PDA

View Full Version : Integer Overflow



jkloucek
09-13-2016, 12:56 AM
Hi guys,

I'm getting an Overflow error (Error 6) and I just don't understand why.
I have two workbooks - one source WB and one target WS. The Source file has a significantly more items than the Target file.
What I wan't to do is to compare first columns of both WBs with IDs and if they match then other columns should copy from Source to Target WB.

The Overflow error is on line rowSource = rowSource + 1


Sub Copy()

Dim WB As Workbook
Dim WS As Worksheet
Set WB = Workbooks("HPD_product_export_EN_NEW.xlsx")
Set WS = WB.Sheets("Product Database")

Dim rowTarget As Integer
Dim rowSource As Integer
rowTarget = 1
rowSource = 1


Do
again:
If Cells(rowTarget, 1) = WS.Cells(rowSource, 1) Then

Cells(rowTarget, 10) = WS.Cells(rowSource, 10)

Else
rowSource = rowSource + 1
GoTo again
End If

rowTarget = rowTarget + 1
rowSource = 1

Loop Until rowTarget = 132

End Sub



Thanks for any advice in advance.


EDIT:// Ok after I posted this thread I found the solution. I did not realize, that a situation could happen when there is no match at all and the rowSource will be counting to infinity :) Just had to take care of that and that's it.

Kenneth Hobs
09-13-2016, 05:04 AM
Variables that hold column numbers can be integers. Variables that hold row numbers are best dimmed as LONG.

jkloucek
09-13-2016, 05:23 AM
Thanks for your reply, that's good point.
But in my case it would result in overflow anyway as there was no match and the loop would be "infinite".
In case of Excel workbook/worksheet, there is no need of LONG data type for rows I think since maximum rows there could be is 1048576 and integer can get up to 2147483647. (If wikipedia is correct :) )

mana
09-13-2016, 05:35 AM
integer
-32767 to 32767

mana
09-13-2016, 05:38 AM
for...next



Sub test()
Dim WB As Workbook
Dim WS As Worksheet
Dim i As Long
Dim j As Long

Set WB = Workbooks("HPD_product_export_EN_NEW.xlsx")
Set WS = WB.Sheets("Product Database")




For i = 1 To WS.Cells(WS.Rows.Count, 1).End(xlUp).Row
For j = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If Cells(j, 1).Value = WS.Cells(i, 1).Value Then
Cells(j, 10) = WS.Cells(i, 10)
Exit For
End If
Next
Next

End Sub

snb
09-13-2016, 05:47 AM
Why not simply using vlookup ?

jkloucek
09-13-2016, 05:47 AM
Thank you for your both replies.
I guess I gotta start using For-Next as it makes it much easier.

jkloucek
09-13-2016, 05:50 AM
I don't know this. So I'm gonna take a look at VLOOKUP what it does.
I'm just using what I know so far so sometimes the code can be pretty confusing as I am substituting bult-in stuff with loops and 'ifs'.

Why not simply using vlookup ?

snb
09-13-2016, 06:36 AM
Be smart: upload a sample file.

mana
09-13-2016, 06:36 AM
vlookup



Sub test2()
Dim WB As Workbook
Dim WS As Worksheet
Dim tbl As String

Set WB = Workbooks("HPD_product_export_EN_NEW.xlsx")
Set WS = WB.Sheets("Product Database")

tbl = WS.Range("A1").CurrentRegion.Columns(1).Resize(, 10).Address(external:=True)

With Range("A1").CurrentRegion.Columns(10)
.Formula = "=iferror(vlookup(a1," & tbl & ",10,0),"""")"
.Value = .Value
End With

End Sub

jkloucek
09-13-2016, 06:46 AM
Sorry, I can't do that as I must follow some internal rules. I know it's rather uncomfortable trying to write a macro/code when one does not see the file.

Be smart: upload a sample file.

SamT
09-13-2016, 08:22 AM
Sub VBAX_SamT_Copy()

Dim Cel As Range

For Each Cel In Workbooks("HPD_product_export_EN_NEW.xlsx"). _
Sheets("Product Database"). _
Range("A1").CurrentRegion.Columns(1)

If Cel = Cells(Cel.Row, 1) Then Cells(Cel.Row, 10) = Cel.Offset(, 10)
Next Cel
End Sub