why does code stop execution at row 32768?

[c1802362]

I have been trying to understand why my code works, but only up to a
point. Here is the code and details:

I have a .csv file that has 50,000 rows and 8 columns which I read
into excel. The first 7 columns contain generic information such as
customer number, name, etc. Column H contains a string of anywhere
from 25 to 10,000 characters of the form "make1 \ model1-option1;
make2 \ model1-option1; make1\ model2-option2; etc". For example, the
make can be "Ford, Chevy, Dodge, etc", the model can be "Camaro,
Firebird, Charger, etc", and the option is a letter suffix, e.g., A,
B, C, etc.

The Make can be listed in any number of ways, such as "Chevy,
Chevrolet, The Chevrolet, etc". Model and options are of the form
123A, where the model is always a 3 digit code with an alpha suffix
between A and Z. Only one model is searched for in a given run and
there are no repeats the option letter, so there can only be a max of
26 elements found in any string.

The code a) searches through the string to find every instance of
'make', b) finds the slash character which separates the make from the
model, and c) determines if the correct model follows, then writes the
model and option code out to adjoining columns.

As written, the code works as required.

My problem: when the row reaches number 32768, the code keeps running
but fails to evaluate the string in column H. Originally, my counter
variables were all set as integers, as no string position would be
greater than 10,000. I've run traps on all of my pointers and I never
come close to reaching the integer declaration limit. As I understand
VBA, each time I reset the row pointer to the next line with
'Offset.Select', I reset the range. So, even with everything set to
Long, the code executes without any errors, but doesn't do what I want
past row 32768.

My workaround was to first filter the 50,000 rows of data to only
those that have the make of interest, as not every string contains
every Make. No subset appears to be greater than 12,000 rows, so the
code works perfectly if first filtered.

So, I'm good with my output, but I'd like to understand why the code
stops working at row 32768?

Art

**********************************

Option Explicit
Option Compare Text
Sub filter()

Dim i As Long, x As Long, y As Long
Dim Column As Long, CurrentAddress As String

Const Make = "Chev"
Const slash = "\"
Const Model = "123"

i = 1 ' string index for make
y = 0 ' string index for slash mark
x = 1 ' index for model
Column = 1 ' index for output

Range("H2").Select
Do Until ActiveCell = ""
Do
' find instance of make within string and return position
x = InStr(i, ActiveCell, Make, vbTextCompare)
If x <> 0 Then ' if make is found within string, continue
i = x + 1 ' search for slash mark after make
y = InStr(i, ActiveCell, slash, vbTextCompare) ' return string
position
' if desired model is next element, write to column
If Mid(ActiveCell, y + 1, 3) = Model Then
ActiveCell.Offset(0, Column) = Mid(ActiveCell, y + 1, 4)
Column = Column + 1 ' index column pointer
End If
End If
Loop Until x = 0 ' continue until all instances of make within
string are found

CurrentAddress = ActiveCell.Address
Range(CurrentAddress).Offset(1, 0).Select ' move to next string
Column = 1 ' reset output column pointer
Loop

End Sub

 

[joel]

You bare probably finding a blank cell and stopping. Try this code. I
changed the code to look for : Slash & Make . this simplifies the code a
lot.

Option Explicit
Option Compare Text
Sub filter()

Dim RowCount As Long
Dim LastRow As Long
Dim Coloffset As Long
Dim StartPos As Long
Dim SlashPos As Long
Dim CellData As String

Const Make = "Chev"
Const slash = "\"
Const Model = "123"


LastRow = Range("H" & Rows.Count).End(xlUp).Row
For RowCount = 2 To LastRow
Coloffset = 1
' find instance of make within string and return position
CellData = Range("H" & RowCount)
StartPos = 1
SlashPos = InStr(StartPos, CellData, slash & Make, vbTextCompare)
' continue until all instances of make within
' string are found
Do While SlashPos > 0
Range("H" & RowCount).Offset(0, Coloffset) = Mid(CellData, SlashPos +
1, 4)
Coloffset = Coloffset + 1 ' index column pointer
StartPos = SlashPos + 4
SlashPos = InStr(StartPos, CellData, slash & Make, vbTextCompare)
Loop

RowCount = RowCount + 1 ' move to next string
Next RowCount

End Sub

 

[Chip Pearson]

32767 is the maximum signed value that can be stored in a 16 bit
integer. Somewhere, an integer is being used and causing overflow
problems.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)