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

 

[kayrrah07]

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?

Yep i keep questioning my self about that, i just cant get the answer my self that's why a seek help from experts

 

[Jonmo1]

Well, it basically means...

There is no value in Master Data column A that equals the value in CSV Data Fields C19.

Check for exact spelling
Check for extra spaces "Hello" vs " Hello" vs "Hello "
If the values are numbers, make sure they actually are numbers, and not "Numbers stored as text"
That is a very common and annoying problem in Excel.
Use ISNUMBER to test

=ISNUMBER(A1)
Where A1 is one of the values in column A (Fill the formuila down to test the whole column of data)
AND do the same for the value in C19

They should return TRUE

 

[kayrrah07]

Well, it basically means...

There is no value in Master Data column A that equals the value in CSV Data Fields C19.

Check for exact spelling
Check for extra spaces "Hello" vs " Hello" vs "Hello "
If the values are numbers, make sure they actually are numbers, and not "Numbers stored as text"
That is a very common and annoying problem in Excel.
Use ISNUMBER to test

=ISNUMBER(A1)
Where A1 is one of the values in column A (Fill the formuila down to test the whole column of data)
AND do the same for the value in C19

They should return TRUE

Thanks, things are getting clearer. I'll try to double check first my codes then try to see the sheet values.

 

[Blade Hunter]

Another tip, I am not sure if others do this but I certainly do. When I test values for equality, I always Upper case it and trim it.

The data I work with comes from multiple external partners and you can just never trust them to do it exactly how you expect.

Another thing I have started doing is instead of looping the whole range I either use find to find the value or if it is not singular, I create a dummy column to the right with the first letter of the search cell in it.

Find the first letter and loop until it changes, this way you are searching a subset.

 

[kayrrah07]

Another tip, I am not sure if others do this but I certainly do. When I test values for equality, I always Upper case it and trim it.

The data I work with comes from multiple external partners and you can just never trust them to do it exactly how you expect.

Another thing I have started doing is instead of looping the whole range I either use find to find the value or if it is not singular, I create a dummy column to the right with the first letter of the search cell in it.

Find the first letter and loop until it changes, this way you are searching a subset.

I still can't figure it out guys. Can someone of you see my work and check what's the matter with it? anyone?