VBA/Excel/Access/Word/Forms/Find
Содержание
- 1 Consider only cells that contain data in the form of numbers, text, and formulas
- 2 Continuing a Search with the FindNext Method and FindPrevious Method
- 3 Finding the Last Used Cell in a Column or Row
- 4 Find the First Non-Zero-Length Cell in a Range
- 5 Ride the Range from the Bottom to Find Last Row
- 6 Searching for and Replacing Formatting
- 7 Uses the Find method to reduce the number of cycles spent in VBA loops:
- 8 Working with Find and Replace
Consider only cells that contain data in the form of numbers, text, and formulas
<source lang="vb"> Sub GetRealLastCell() Dim lRealLastRow As Long Dim lRealLastColumn As Long Range("A1").Select On Error Resume Next lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas,, xlByRows, xlPrevious).Row lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas,, xlByColumns, xlPrevious).Column Cells(lRealLastRow, lRealLastColumn).Select End Sub </source>
Continuing a Search with the FindNext Method and FindPrevious Method
<source lang="vb">
Sub find()
Cells.FindNext
End Sub
</source>
Finding the Last Used Cell in a Column or Row
<source lang="vb">
Function GetLastCellInColumn(rg As Range) As Range
Dim lMaxRows As Long lMaxRows = ThisWorkbook.Worksheets(1).Rows.Count If IsEmpty(rg.Parent.Cells(lMaxRows, rg.Column)) Then Set GetLastCellInColumn = _ rg.Parent.Cells(lMaxRows, rg.Column).End(xlUp) Else Set GetLastCellInColumn = rg.Parent.Cells(lMaxRows, rg.Column) End If
End Function
</source>
Find the First Non-Zero-Length Cell in a Range
<source lang="vb">
Sub FirstNonZeroLength(Rng As Range)
Dim myCell As Range myCell = Range("A1:E3") FirstNonZeroLength = 0# For Each myCell In Rng If Not IsNull(myCell) And myCell <> "" Then Debug.Print myCell.Value Exit Function End If Next myCell Debug.Print myCell.Value
End Function
</source>
Ride the Range from the Bottom to Find Last Row
<source lang="vb">
Sub count()
cells(Rows.count, 1).End(xlUp).Select
End Sub
</source>
Searching for and Replacing Formatting
<source lang="vb">
Sub font()
With Application.FindFormat.Font .Name = "Arial" .Size = "12" .Bold = True End With With Application.ReplaceFormat.Font .Name = "Arial Black" .Bold = False End With Cells.Replace What:="5", Replacement:="5", LookAt:=xlPart, SearchOrder _ :=xlByColumns, MatchCase:=False, SearchFormat:=True, ReplaceFormat:=True
End Sub
</source>
Uses the Find method to reduce the number of cycles spent in VBA loops:
<source lang="vb"> Sub DeleteRows2() Dim rngFoundCell As range Application.ScreenUpdating = False Set rngFoundCell = range("C:C").Find(What:="Mangoes") Do Until rngFoundCell Is Nothing rngFoundCell.EntireRow.delete Set rngFoundCell = range("C:C").FindNext Loop End Sub </source>
Working with Find and Replace
<source lang="vb">
Sub cell()
Cells.Find(What:="2008", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ True, SearchFormat:=False).Activate
End Sub
</source>