here's something that should get you started. Please be aware that the code is far from being optimal.
It looks for the value "2" in the first column. The address of every cell which contains "2" will be stored in an array (lim). At the end lim is being displayed.
I'd really love someone to shorten this to the minimum so i could learn a bit too :-)
Sub Sample()
Dim oRange As Range, aCell As Range
Dim ws As Worksheet
Dim ExitLoop As Boolean
Dim SearchString As String, FoundAt As String, lim() As Variant, i As Integer, k As Integer
Set ws = Worksheets(1)
Set oRange = ws.Columns(1)
i = 0
k = 0
SearchString = "2"
Set aCell = oRange.Find(What:=SearchString, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
aCell.Activate
ReDim lim(0 To 20)
lim(0) = aCell.Address
i = i + 1
Do While ExitLoop = False
Set aCell = oRange.FindNext(After:=aCell)
If Not aCell Is Nothing Then
aCell.Activate
If aCell.Address = lim(0) Then Exit Do
k = k + 1
ReDim Preserve lim(0 To k)
lim(i) = aCell.Address
i = i + 1
Else
ExitLoop = True
End If
Loop
Else
MsgBox SearchString & " not Found"
End If
For i = 0 To UBound(lim)
MsgBox (lim(i))
Next
End Sub
LinkBack URL
About LinkBacks
robin_108 
Need a Macro to search for text in sheet and copy the matching row to new sheet
Register To Reply
Bookmarks