Detecting last column in sheet

Question

I am trying to use the Lastcolumn from Sheet D in a formula in Sheet M. This is the code I have: D!R[8]C " & LastColumn1 & .

But it looks like the code is using the last column number in sheet M.

Can you please check what is wrong with this code?

Sub misc()
  Dim LastColumn1 As Long
  LastColumn1 = ActiveWorkbook.Worksheets("D").Range("a12").End(xlToRight).Column
End Sub
Sub try()
    Sheet1.Select        
    Range("A4").Select
    ActiveCell.End(xlToRight).Offset(-2, 1).Value = counter & "Qtr"
    Do While ActiveCell.Value <> ""
        ActiveCell.End(xlToRight).Offset(0, 1).Formula = "=IFERROR(D!R[8]C " & LastColumn1 & " *(1+INDEX(A!R3C4:R418C27,MATCH(M!R2C,A!R2C4:R2C27,0))),0)"
        ActiveCell.Offset(1, 0).Select
    Loop
End Sub

The variable `LastColumn1` is set at module or global scope? When running the `Try` sub, what is the value of `LastColumn1` variable? Hover over it in break mode (step into the code), or debug it in another way, please. — kolcinx, Nov 26 '15 at 14:22
Even when I put Option Explicit it is not working. Option Explicit Sub try() Dim LastColumn1 As Long LastColumn1 = ActiveWorkbook.Worksheets("D").Range("A12").End(xlToRight).Value Sheet1.Select Range("A4").Select ActiveCell.End(xlToRight).Offset(-2, 1).Value = "Qtr" Do While ActiveCell.Value <> "" ActiveCell.End(xlToRight).Offset(0, 1).Formula = "=IFERROR(D!R[8]C "LastColumn1" *(1+INDEX(A!R3C4:R418C27,MATCH(M!R2C,A!R2C4:R2C27,0))),0)" ActiveCell.Offset(1, 0).Select Loop End Sub — TseD, Nov 26 '15 at 15:50

score 0 · Answer 1 · edited Nov 26 '15 at 14:39

You need a function for this.

Function LastColumn() as long
  LastColumn = ThisWorkbook.Worksheets("D").Range("a12").End(xlToRight).Column
end function

And then you call that function directly from your code:

Sub try()
    Sheet1.Select        
    Range("A4").Select
    lastCol = LastColumn '## Calling the function from above
    ActiveCell.End(xlToRight).Offset(-2, 1).Value = counter & "Qtr"
    Do While ActiveCell.Value <> ""
        ActiveCell.End(xlToRight).Offset(0, 1).Formula = "=IFERROR(D!R[8]C " & lastCol & " *(1+INDEX(A!R3C4:R418C27,MATCH(M!R2C,A!R2C4:R2C27,0))),0)"
        ActiveCell.Offset(1, 0).Select
    Loop
End Sub

score 0 · Answer 2 · edited May 23 '17 at 11:51

Can you please check what is wrong with this code?

You have an undeclared/uninitialized variable called LastColumn1 which you are using in a formula string. This variable has no value at runtime, which causes the error.

How to resolve it:

If you had simply used Option Explicit, you would be informed of a compilation error. LastColumn1 variable is undeclared within the scope of the try() procedure.

Since you are not using Option Explicit (which requires that all variables be declared), the compiler initializes undeclared variables as type Variant, with an empty/0/null-string value.

So, what you have essentially is an obvious error with:

ActiveCell.End(xlToRight).Offset(0, 1).Formula = _
    "=IFERROR(D!R[8]C " & EMPTY & " * _
    (1+INDEX(A!R3C4:R418C27,MATCH(M!R2C,A!R2C4:R2C27,0))),0)"

Because the result of that concatenation is: D!R[8]C and that is an invalid Range (without a column index), it is undefined/cannot exist as such.

Using @iDevelop's function above will probably suffice for most ordinary, simple cases, however you may want to review the more robust methods described here:

Error in finding last used cell in VBA

Detecting last column in sheet

2 Answers2