Overflow Error

[kayrrah07]

Hi Guys,

I'm currently having a problem with this overflow error thing. I don't know how to resolve this. Please help me find a solution. Here are my codes:

Dim counter As Integer
Dim row As Integer
Dim message, mInitial As String
message = "Populate SAPHR first."
counter = 3
row = 4
If IsEmpty(Sheets("MASTER DATA").Range("A" & counter)) Then
MsgBox message
Else

Do Until IsEmpty(Sheets("CSV DATA FIELDS").Range("B" & row))
Do Until Sheets("MASTER DATA").Range("A" & counter) = Sheets("CSV DATA FIELDS").Range("C" & row)
counter = counter + 1 (when i click debug, the program points to this one)
Loop

Sheets("MASTER DATA").Rows(counter).Columns("I").Value = Sheets("CSV DATA FIELDS").Rows(row).Columns("C").Value
Sheets("MASTER DATA").Rows(counter).Columns("K").Value = Sheets("CSV DATA FIELDS").Rows(row).Columns("L").Value
Sheets("MASTER DATA").Rows(counter).Columns("L").Value = Sheets("CSV DATA FIELDS").Rows(row).Columns("S").Value
Sheets("MASTER DATA").Rows(counter).Columns("W").Value = Sheets("CSV DATA FIELDS").Rows(row).Columns("J").Value
Sheets("MASTER DATA").Rows(counter).Columns("X").Value = Sheets("CSV DATA FIELDS").Rows(row).Columns("AD").Value
If IsEmpty(Sheets("CSV DATA FIELDS").Range("G" & row)) Then
Sheets("MASTER DATA").Rows(counter).Columns("J").Value = Sheets("CSV DATA FIELDS").Rows(row).Columns("F").Value & ", " & Sheets("CSV DATA FIELDS").Rows(row).Columns("E").Value
Else
mInitial = Sheets("CSV DATA FIELDS").Rows(row).Columns("G").Value
mInitial = Left(mInitial, 1)
Sheets("MASTER DATA").Rows(counter).Columns("J").Value = Sheets("CSV DATA FIELDS").Rows(row).Columns("F").Value & ", " & Sheets("CSV DATA FIELDS").Rows(row).Columns("E").Value & " " & mInitial & "."
End If

counter = 3
row = row + 1

Loop
End If

 

[Jonmo1]

Try dimming your variables as LONG instead of INTEGER.

Interger can only go up to 32767

 

[kayrrah07]

Try dimming your variables as LONG instead of INTEGER.

Interger can only go up to 32767

I've tried that already and another error occured. "Application Defined or Object-defined error" it points to this code.

Do Until Sheets("MASTER DATA").Range("A" & counter) = Sheets("CSV DATA FIELDS").Range("C" & row)

Any idea what happen sir?

 

[Jonmo1]

What are the values of counter and row at the time of the error?
When you debug, you can just hover your mouse over the variables to find thier current values.

 

[kayrrah07]

What are the values of counter and row at the time of the error?
When you debug, you can just hover your mouse over the variables to find thier current values.

The value for counter = 65537 and row = 19. Those were the values written when i hovered on it.

 

[Blade Hunter]

The value for counter = 65537 and row = 19. Those were the values written when i hovered on it.

Range("A" & counter)

I assume you are on Excel <2007 which means 65537 is one more than the allowed rows. In other words, it is not finding what you are looking for and running out of rows to check. You need to have a failsafe in there to say if counter > rows.count then exit loop

 

[kayrrah07]

Range("A" & counter)

I assume you are on Excel <2007 which means 65537 is one more than the allowed rows.

Yep. You assumed correctly. I'm using excel 2003.

 

[kayrrah07]

Range("A" & counter)

I assume you are on Excel <2007 which means 65537 is one more than the allowed rows. In other words, it is not finding what you are looking for and running out of rows to check. You need to have a failsafe in there to say if counter > rows.count then exit loop

Where do i put the code "if counter > rows.count then exit loop<!-- / message --><!-- sig -->" Please see my codes above. I dont have idea where to put it

 

[Jonmo1]

Right between the lines that say

counter = counter + 1
put the If here
Loop


But, perhaps a better question should be...

in this line

Do Until Sheets("MASTER DATA").Range("A" & counter) = Sheets("CSV DATA FIELDS").Range("C" & row)

Why is the condition not being met allowing that counter to get to such a high number?

 

[Blade Hunter]

Jonmo is correct, it is always better to look for the reason of an error rather than the error itself.