Gil
11-08-2011, 09:07 PM
I use the following code to search another sheet for data with a loop. It works fine as long as the step is at regular intervals and stops when the next cell is empty.
My problem is the data I am getting is no longer at regular spacing so the last line Loop Until IsEmpty(ActiveCell.Offset(0, 0)) no longer fulfills the task. What do I need to do to ignore empty cells and loop through a range of say 1-100 rows.
Only part is shown
Workbooks("Act Data.XLS").Activate
Dim lngLastRow As Long
lngLastRow = Range("C" & Rows.Count).End(xlUp).Row
Cells(lngLastRow - 0, 3).Select
Dim Sh As Worksheet
Dim Fnd As Range
Dim c As Range
Do
Set Sh = Sheets("Act BP")
Set c = ActiveCell
Set Fnd = Sh.Cells.Find((Split(c)(0)), LookAt:=xlWhole)
If Not Fnd Is Nothing Then
c.Offset(, 1) = Sh.Cells(3, Fnd.Column) & "-" & Sh.Cells(Fnd.Row, 2) & "-" & Sh.Cells(Fnd.Row, 1)
Else
c.Offset(, 1) = "Not found"
End If
ActiveCell.Offset(-1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 0))
I have tried some alternatives but find myself going round in a loop.Any help will be appreciated
My problem is the data I am getting is no longer at regular spacing so the last line Loop Until IsEmpty(ActiveCell.Offset(0, 0)) no longer fulfills the task. What do I need to do to ignore empty cells and loop through a range of say 1-100 rows.
Only part is shown
Workbooks("Act Data.XLS").Activate
Dim lngLastRow As Long
lngLastRow = Range("C" & Rows.Count).End(xlUp).Row
Cells(lngLastRow - 0, 3).Select
Dim Sh As Worksheet
Dim Fnd As Range
Dim c As Range
Do
Set Sh = Sheets("Act BP")
Set c = ActiveCell
Set Fnd = Sh.Cells.Find((Split(c)(0)), LookAt:=xlWhole)
If Not Fnd Is Nothing Then
c.Offset(, 1) = Sh.Cells(3, Fnd.Column) & "-" & Sh.Cells(Fnd.Row, 2) & "-" & Sh.Cells(Fnd.Row, 1)
Else
c.Offset(, 1) = "Not found"
End If
ActiveCell.Offset(-1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 0))
I have tried some alternatives but find myself going round in a loop.Any help will be appreciated