VBA/Excel/Access/Word/Forms/Find

Материал из VB Эксперт
Версия от 15:48, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

Consider only cells that contain data in the form of numbers, text, and formulas

 
     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



Continuing a Search with the FindNext Method and FindPrevious Method

 
Sub find()
    Cells.FindNext
End Sub



Finding the Last Used Cell in a Column or Row

 
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



Find the First Non-Zero-Length Cell in a Range

 
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



Ride the Range from the Bottom to Find Last Row

 
Sub count()
    cells(Rows.count, 1).End(xlUp).Select
End Sub



Searching for and Replacing Formatting

 
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



Uses the Find method to reduce the number of cycles spent in VBA loops:

 
     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



Working with Find and Replace

 
Sub cell()
    Cells.Find(What:="2008", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        True, SearchFormat:=False).Activate
End Sub