1

I have a spreadsheet that has 137000+ lines in it which means that I do not want to have lots of excel formulas recalculating at every move. Therefore I created a series of macros to do the job for me. This was working perfectly until recently, and it now stops at row 32767 and hangs until I crash out. I don't understand what has changed. Hopefully one of you clever people can help a novice in need.

Here is an example of one of my subs where this is happening. All I want to do with this is a vlookup as long as it meets a certain requirement which works fine until I hit the row limit:

Sub FGLOH()

Dim FGLOHINT As Integer

FGLOHINT = 3

    Sheets("Calculation of Final LOH").Select
    Sheets("Calculation of Final LOH").Range("A" & FGLOHINT).Activate

Do Until IsEmpty(ActiveCell)

    Sheets("Calculation of Final LOH").Select
    Sheets("Calculation of Final LOH").Range("A" & FGLOHINT).Activate

If ActiveCell.Value = ActiveCell.Offset(-1, 0).Value Then
Sheets("Calculation of Final LOH").Range("H" & FGLOHINT).Value = 0

FGLOHINT = FGLOHINT + 1

Else

Dim FGLOH As String
FGLOH = Sheets("Calculation of Final LOH").Range("A" & FGLOHINT).Value

On Error GoTo 0
On Error Resume Next

Sheets("Calculation of Final LOH").Range("H" & FGLOHINT).Value = Application.WorksheetFunction.VLookup(FGLOH, Sheets("Work Centre LOH Lookup").Range("A2:M200000"), 13, False)

FGLOHINT = FGLOHINT + 1

End If

Loop

End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
LynseyAbbott
  • 35
  • 1
  • 2
  • 5
  • 4
    Since you got this issue fixed, you might want to take this code to [codereview.se] and get it optimized. With that many rows your `.Select` & `.Activate` is going to significantly slow down your code. – K.Dᴀᴠɪs Jan 30 '18 at 14:38
  • 3
    Maybe take a look at https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – K.Dᴀᴠɪs Jan 30 '18 at 14:39
  • Good shout, thank you. It does take a long time to run - I have a lot to learn and no one to teach me except you lovely lot! – LynseyAbbott Jan 30 '18 at 14:44
  • 3
    `Integer` is essentially useless today. Back in the time of 16-bit computers, 16-bit signed integers were all the rage, but today, rule of thumb, use a `Long` (32-bit) integer type whenever you need an integer value, and keep `Integer` (16-bit) in 1997. – Mathieu Guindon Jan 30 '18 at 14:56
  • Thanks - I've just gone through all my code and changed Integer to Long. Integer shall be filed away never to be used again. – LynseyAbbott Jan 30 '18 at 15:21
  • 1
    `Integer` *is* useful when you *must* pass a 16-bit integer type to an API function, for example. But yeah otherwise, a `Long` fits the bill and won't overflow until you try to assign it to 2^31. – Mathieu Guindon Jan 30 '18 at 15:56
  • Note that formulas don't "recalculate at every move" unless they are downstream of another formula that changes, or they are volatile, or they are downstream of a volatile formula. See https://chandoo.org/wp/2014/03/03/handle-volatile-functions-like-they-are-dynamite/ for more on this. You can certianly achieve what you are trying to do with formulas in a way that will outperform VBA. – jeffreyweir Jan 30 '18 at 19:14

1 Answers1

9

32767 is the maximum value for an Integer in VBA. Use a Long instead.

Dim FGLOHINT As Long
braX
  • 11,506
  • 5
  • 20
  • 33