I am searching a column of data for a string of users input and am
receiving an error after searching 256 rows. Is there a limit on the
worksheet object? If not does anyone have any advice? I can not figure
out why it will not search past 256. Thank you.

Below is my code:

Set rngCasNumberRange = Range("A1:B13242")

intIfCounter = 1

'for loop to loop through column A and search for the first number
entered.
'fill Column D with the number and column E with the code if found
For rwIndex = 1 To 1
For colIndex = 2 To 13242
With Worksheets("SortedByCasNumberAndPhrase").Cells(rwI ndex,
colIndex)
If rngCasNumberRange.Cells(colIndex, 1) = strCASNumberOne
Then
'fill variable with phase code corresponding to the cas
number found
strPhaseCodeOne = rngCasNumberRange.Cells(colIndex, 2)
'fill cells with found phrase code numbers
rngCasNumberRange.Cells((intIfCounter + 14), 4).Value =
strCASNumberOne
rngCasNumberRange.Cells((intIfCounter + 14), 5).Value =
strPhaseCodeOne
intIfCounter = intIfCounter + 1
End If
End With
Next colIndex
Next rwIndex

Thanks.

There are 6,5536 rows and 256 columns per worksheet. Less rows if you're
using Excel version 4 or less. So the there won't be a column number beyond
the max of 256
--
Kevin Backmann


" wrote:

I am searching a column of data for a string of users input and am
receiving an error after searching 256 rows. Is there a limit on the
worksheet object? If not does anyone have any advice? I can not figure
out why it will not search past 256. Thank you.

Below is my code:

Set rngCasNumberRange = Range("A1:B13242")

intIfCounter = 1

'for loop to loop through column A and search for the first number
entered.
'fill Column D with the number and column E with the code if found
For rwIndex = 1 To 1
For colIndex = 2 To 13242
With Worksheets("SortedByCasNumberAndPhrase").Cells(rwI ndex,
colIndex)
If rngCasNumberRange.Cells(colIndex, 1) = strCASNumberOne
Then
'fill variable with phase code corresponding to the cas
number found
strPhaseCodeOne = rngCasNumberRange.Cells(colIndex, 2)
'fill cells with found phrase code numbers
rngCasNumberRange.Cells((intIfCounter + 14), 4).Value =
strCASNumberOne
rngCasNumberRange.Cells((intIfCounter + 14), 5).Value =
strPhaseCodeOne
intIfCounter = intIfCounter + 1
End If
End With
Next colIndex
Next rwIndex

Thanks.

Perhaps ...

With Worksheets("SortedByCasNumberAndPhrase").Cells(rwI ndex,colIndex)

should be ....

With Worksheets("SortedByCasNumberAndPhrase").Cells(col Index,rwIndex)

You appear to have rows & columns "mixed up" i.e rwIndex should be colINdex
and vice-versa.

" wrote:

I am searching a column of data for a string of users input and am
receiving an error after searching 256 rows. Is there a limit on the
worksheet object? If not does anyone have any advice? I can not figure
out why it will not search past 256. Thank you.

Below is my code:

Set rngCasNumberRange = Range("A1:B13242")

intIfCounter = 1

'for loop to loop through column A and search for the first number
entered.
'fill Column D with the number and column E with the code if found
For rwIndex = 1 To 1
For colIndex = 2 To 13242
With Worksheets("SortedByCasNumberAndPhrase").Cells(rwI ndex,
colIndex)
If rngCasNumberRange.Cells(colIndex, 1) = strCASNumberOne
Then
'fill variable with phase code corresponding to the cas
number found
strPhaseCodeOne = rngCasNumberRange.Cells(colIndex, 2)
'fill cells with found phrase code numbers
rngCasNumberRange.Cells((intIfCounter + 14), 4).Value =
strCASNumberOne
rngCasNumberRange.Cells((intIfCounter + 14), 5).Value =
strPhaseCodeOne
intIfCounter = intIfCounter + 1
End If
End With
Next colIndex
Next rwIndex

Thanks.

Thank you Toppers.....they were switched. A simple thing like that and
I have spent hours scratching my head. Again, thank you :o)
Toppers wrote:
Perhaps ...

With Worksheets("SortedByCasNumberAndPhrase").Cells(rwI ndex,colIndex)

should be ....

With Worksheets("SortedByCasNumberAndPhrase").Cells(col Index,rwIndex)

You appear to have rows & columns "mixed up" i.e rwIndex should be colINdex
and vice-versa.

" wrote:

I am searching a column of data for a string of users input and am
receiving an error after searching 256 rows. Is there a limit on the
worksheet object? If not does anyone have any advice? I can not figure
out why it will not search past 256. Thank you.

Below is my code:

Set rngCasNumberRange = Range("A1:B13242")

intIfCounter = 1

'for loop to loop through column A and search for the first number
entered.
'fill Column D with the number and column E with the code if found
For rwIndex = 1 To 1
For colIndex = 2 To 13242
With Worksheets("SortedByCasNumberAndPhrase").Cells(rwI ndex,
colIndex)
If rngCasNumberRange.Cells(colIndex, 1) = strCASNumberOne
Then
'fill variable with phase code corresponding to the cas
number found
strPhaseCodeOne = rngCasNumberRange.Cells(colIndex, 2)
'fill cells with found phrase code numbers
rngCasNumberRange.Cells((intIfCounter + 14), 4).Value =
strCASNumberOne
rngCasNumberRange.Cells((intIfCounter + 14), 5).Value =
strPhaseCodeOne
intIfCounter = intIfCounter + 1
End If
End With
Next colIndex
Next rwIndex

Thanks.