Excell vba getting error after searching 256 columns
 

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.

Excell vba getting error after searching 256 columns
 

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.

Excell vba getting error after searching 256 columns
 

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.

Excell vba getting error after searching 256 columns
 

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.