problem with excel worksheets.cells() want more than 257 rows looped! 2

[plip1978]

Hi,
I hope someone can help. The following code breaks at i=257 (assume the condition is true at cell(1, 857) for example), can anyone tell me why? Also is there a way around this?

Worksheets(sheetName).Activate
For i = 1 To 1000
If Worksheets(sheetName).Cells(1, i).Value = checkValue Then
currentRow = i
Exit For
End If
Next i

Thanks in advance.

 

[Andrzejek]

And the i is declared as....?

Dim i As [red]???[/red]

Have fun.

---- Andy

 

[zahead31]

If Worksheets(sheetName).Cells[red](i, 1)[/red].Value Then

Your code appeared to be incrementing the column, not the rows. Your code was getting on error at 257 because there are 256 columns.

HTH,
Matt

 

[SkipVought]

You could perform the objective alot faster, using the Find function.

dim rFound as range
set rfound = Worksheets(sheetName).cells.find(checkValue)
if not rfound is nothing then 
   currentrow = rfound.row
else
   currentrow = 0
end if

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}