if condition on row numbers

[juliet_jia]

Hi all, in the VBA macro below I'm trying to run a if...then conditional on the row numbers, but it always masses up (sometimes it works, and sometimes not..... seems random). Any help is very appreciated!

It uses findNext to identify the row numbers. To avoid findNext goes back to the beginning of the data, I thought to use If condition, i.e. If FindNext returns lastCell < FirstCell, I set the lastCell to the last row of the file.

But when lastCell is clearly larger than FirstCell, it still enters into the If loop and returns the last cell of the file as appose to should-be Findnext's lastCell.

Dim c_end As String
Dim FirstCell As String, lastCell As String
Dim Status As Variant

 Range("b1048576").End(xlUp).Select
 c_end = Selection.Row

Set Status =Range("B:B").Find("Ship", lookat:=xlWhole)                   
 j = Application.CountIf(Range("B:B"), "Ship")
            
If Not Status Is Nothing Then
                            
  For i = 1 To j

   FirstCell = Status.Row
   Set Status = Range("B:B").FindNext(Status)
   lastCell = Status.Row
 
     If [COLOR=DarkRed]lastCell < FirstCell[/COLOR] Then
      lastCell = c_end
     End If
 
  Next

End If

 

[rdw72777]

I won't lie, I don't really understand what you are trying to do exactly. But I'm just curious, whi did you set "FirstCell" and "LastCell" to string values if they are going to be row numbers? I'm not sure string values evaluate perfectly in numerical operations (i.e. lastcell < firstcell) so maybe that's the cause of the problem. Try setting them to Long instead of String and see what happens.