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

   <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>