в 16:33, 26 мая 2010

2010-05-26T16:33:00Z

Admin: 1 версия

2010-05-26T12:48:31Z

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>

← Предыдущая	Версия 16:33, 26 мая 2010
(нет различий)

VBA/Excel/Access/Word/Forms/Find - История изменений

в 16:33, 26 мая 2010

Admin: 1 версия