Referencing a floating column in an excel formula (VBA)

Question

I am trying to loop though a worksheet to get some data. With that data I am trying to do a calculation and output to another worksheet directly (I want the formula to be on the sheet).

I loop through each column (first row) to see if has data. If it has, the calculation is performe. Problem is I am using concatenate and a variable to count the rows, but that does not work with the columns.

The code is:

Sub OutputManager()

Dim x As Long
Dim lRow As Long, lColumn As Long
Dim LastRow As Long, LastColumn As Long
Dim ws As Worksheet


'find date limits
LastRow = Worksheets("TIME").Cells(Rows.Count, "A").End(xlUp).Row
LastColumn = Worksheets("TIME").Cells(1, Columns.Count).End(xlToLeft).Column


'enter an if loop to find an index

For lColumn = 2 To LastColumn
    If Worksheets("TIME").Cells(1, lColumn) <> "" Then
        For lRow = 3 To LastRow
            Worksheets("Ret").Cells(lRow, lColumn) = "='TIME'!" & lColumn & lRow & "/'TIME'!" & lColumn & lRow - 1 & "-1" 
                  '***** problem with the lcolumn, it gives a number and not a "A" or "B"

        Next lRow
    End If
Next lColumn

Any ideas on how to do that?

Easiest workaround: convert number to letter. See: http://stackoverflow.com/questions/12796973/function-to-convert-column-number-to-letter . — Limak, Jan 20 '17 at 09:20

score 2 · Accepted Answer · answered Jan 20 '17 at 09:25

2

try this:

With Worksheets("TIME")
    For lColumn = 2 To LastColumn
        If .Cells(1, lColumn) <> "" Then
            For lRow = 3 To LastRow
                Worksheets("Ret").Cells(lRow, lColumn) = "='TIME'!" & .Cells(lRow, lColumn).Address & "/'TIME'!" & .Cells(lRow - 1, lColumn).Address & "-1"
            Next lRow
        End If
    Next lColumn
End With

answered Jan 20 '17 at 09:25

user3598756

28,893
4
18
28

Thanks for the answer.´The .Cells(lRow, lColumn).Address´ is giving me a "Invalid or unqualified reference" error. – DGMS89 Jan 20 '17 at 09:29
1

have you copied _exactly_ the code in my answer? In the error message box click "Debug" and in Immediate Window type `?"='TIME'!" & .Cells(lRow, lColumn).Address & "/'TIME'!" & .Cells(lRow - 1, lColumn).Address & "-1"`, press return and see the returned value – user3598756 Jan 20 '17 at 10:16
There was a space after the first = sign, I was giving the error for some reason. Now it works perfectly. – DGMS89 Jan 20 '17 at 10:18

Referencing a floating column in an excel formula (VBA)

1 Answers1