0

I am getting an OVERFLOW error when running a For statement with VBA. What can I do in order to avoid this issue?

Dim lr As Long
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
For i = 2 To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next

I tried to add Clng(lr) but that did not work. trying to get this to work and bypass the Overflow error

Aaron Gold
  • 1
  • 1
  • 4
    What did you `Dim i` as ? – BigBen May 30 '23 at 16:48
  • Looks like "i' is Dim as integer – Aaron Gold May 30 '23 at 16:50
  • 3
    `Dim i As Long` – BigBen May 30 '23 at 16:51
  • 2
    In VBA, `Integer` has a range of -32,768 to 32,767. And `Long` has a range of -2,147,483,648 to 2,147,483,647. So if you use an `Integer` to try and iterate to a limit defined by a `Long`, you'll hit Overflow Error when the loop element exceeds the limit of an `Integer`. Eg. If you loop `For i = 1 to 50000`, you'll hit an Overflow error at i = 32768, midway though the loop. – Toddleson May 30 '23 at 16:55
  • As general advice, there's almost no reason to ever use `Integer` in VBA. The size difference in memory is negligible on a modern computer. And I've heard that `Long` even calculates faster because of some weirdness in how the application creates variables. – Toddleson May 30 '23 at 17:01
  • 2
    FYI in VBA `Match()` either returns a number>0 if there's a match, or an error value if there's no match. When used with `WorksheetFunction` it triggers a run-time error when no match is found. – Tim Williams May 30 '23 at 17:15
  • The problem here is at....... ws.Rows.Count use....... ws.Rows.CountLarge ....... instead, also try to use Option Explicit in every module – ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ May 30 '23 at 18:30
  • @ΑΓΡΙΑΠΕΣΤΡΟΦΑ, the problem is `i` though, not `.Count`, and `.CountLarge` is unnecessary here. `Rows.Count` is only going to return 1048576 at the maximum. – BigBen May 30 '23 at 19:42
  • I put values at row 1048576 and 1048575 and with lr = Me.Cells(Me.rows.CountLarge, vcol).End(xlUp).row get in lr a value of 1048575 Then set For i = lr To 2 Step -1 and the i takes the 1048575 without error. (i remove Option Explicit) – ΑΓΡΙΑ ΠΕΣΤΡΟΦΑ May 30 '23 at 19:58

0 Answers0