Solved: Modify Loop Until IsEmpty

[Gil]

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

[mdmackillop]

Sub Test()

Dim lngLastRow As Long
Dim Sh As Worksheet
Dim Fnd As Range
Dim c As Range
Dim cel As Range

Workbooks("Act Data.XLS").Activate
lngLastRow = Range("C" & Rows.Count).End(xlUp).Row
Set cel = Cells(lngLastRow, 3)

Do
Set Sh = Sheets("Act BP")
Set Fnd = Sh.Cells.Find((Split(cel)(0)), LookAt:=xlWhole)
If Not Fnd Is Nothing Then
cel.Offset(, 1) = Sh.Cells(3, Fnd.Column) & "-" & Sh.Cells(Fnd.Row, 2) & "-" & Sh.Cells(Fnd.Row, 1)
Else
cel.Offset(, 1) = "Not found"
End If
If cel.Row = 1 Then Exit Do
If cel.Offset(-1) <> "" Then
Set cel = cel.Offset(-1)
Else
Set cel = cel.End(xlUp)
End If
Loop
End Sub

[Dave]

Gil perhaps you could have mentioned the Mr. Excel posting. Please review the forum rules. Dave

[Gil]

Dave

I do not understand your reference to a Mr Excel posting. The only forum I subscribe to is VBA Express and the code I have posted I developed with the help of contributors from VBA Express and possibly bits from the internet in general, as far as I understand no other forums.

Gil

[Gil]

Hello mdmackillop
Thank you for your code. It works perfectly to suit my needs.
With that as a base to work from I am able to add my minor tweeks i.e a message box etc.
I would not have got there otherwise.
Many thanks
Gil