VBA Error 6 Overflow

Question

I am getting run time error 6: Overflow through the below code.

n = (.Cells(Rows.Count, "A").End(xlUp).Row)   
FillP = Array("D", "E", "F", "H", "I", "J", "M", "O", "P", "AR", "AS", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "CB", "CC", "CD")

'fill blanks with na
For i = 2 To n
    For j = LBound(FillP) To UBound(FillP)
        If Trim(.Range(FillP(j) & i)) = "" Then
            .Range(FillP(j) & i) = "na"
        End If
    Next j
Next i

I have Excel 2010, I haven't yet declared any of the above variables yet (will do after testing). Just trying to fill blanks in specific columns with an "na". Nearly identical code appears earlier in my sub and yet it runs fine. No idea where i'm going wrong.

Thanks in advance for any help!

Gave that a try, afraid it made no difference. If it helps n = 2650 — N.J.
– N.J., Commented Oct 27, 2016 at 17:39
Well that would do it. You will need to add another if/then before the current one to test for the error. IF Not IsError(.Range(FillP(j) & i)) Then — Scott Craner
– Scott Craner, Commented Oct 27, 2016 at 18:10
@ScottCraner - A Variant will silently promote itself to a Long instead of overflowing as an Integer. In fact, it will silently promote itself to a Double instead of overflowing as a Long. — Comintern
– Comintern, Commented Oct 27, 2016 at 18:43

Aditya Pansare · Accepted Answer · 2016-10-28 09:44:05Z

1

overflow results when you try to assign value to a variable, that exceeds the limitations of the data type. I am afraid that you might have declared i, j, or n as Interger.

Also you will not need the dot operator before cells & range.

answered Oct 28, 2016 at 9:44

Aditya Pansare

1,1329 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mojo3340 · Accepted Answer · 2016-10-28 09:19:46Z

0

Where does the error specificaally occur? just from inspection, you do not need the dots before Cells and Range. You would only do that in a with statement

answered Oct 28, 2016 at 9:19

mojo3340

5691 gold badge6 silver badges27 bronze badges

Collectives™ on Stack Overflow

VBA Error 6 Overflow

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related