CODEWITHSUNDEEP
excelvba
XLmatters
XLmatters

Reputation: 386

Use variables inside a formula

I am attempting to use variables in what should be a simple addition formula. First I search for the column header in row 3 call "Jan Expense Hours" MsgBox ColL comes back with the letter "I" and MsgBox ColL2 comes back with the letter "J", both of which are correct. lRow comes back with row 55 which is also correct. Although when I try to add these variables to Worksheets("Calcs").Range("F4:F" & lRow).Formula = "=SUM('Resource Details'! & [ColL] & 4: & [ColL2] & 4)" I get an Application-defined or object-defined error on this line of code. Does anyone have an Idea what I am doing wrong? Btw, I'm searching for the column header because the columns do shift on various copies.

Full Procedure:

Sub JanTotHrsFind()
Dim lRow As Long
Dim lCol As Long
Dim strSearch As String
Dim aCell As Range
Dim ColL As String
Dim ColL2 As String
Dim ColNo As Long

Sheets("Resource Details").Activate

    'find the column
    strSearch = "*Jan Expense Hours*"

    Set aCell = Sheets("Resource Details").Rows(3).Find(What:=strSearch, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=True, SearchFormat:=False)


'convert column number to letter
ColNo = aCell.Column
    ColL = Split(Cells(, ColNo).Address, "$")(1)

    ColL2 = Split(Cells(, (ColNo + 1)).Address, "$")(1)     'adds one more column to right
MsgBox ColL
MsgBox ColL2 

    lRow = Cells.Find(What:="SUBTOTAL*", _
                    After:=Range(ColL & "4"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row - 1         'minus 1 row to move above

MsgBox "Last Row: " & lRow

'formula for Jan Expense Hours + Jan Capital Hours
'Worksheets("Calcs").Range("F4:F" & lRow).Formula = "=SUM('Resource Details'!I4:J4)"
'Worksheets("Calcs").Range("F4:F" & lRow).Formula = "=SUM('Resource Details'![" & ColL & "]4:[" & ColL2 & "]4)"
Worksheets("Calcs").Range("F4:F" & lRow).Formula = "=SUM('Resource Details'! & [ColL] & 4: & [ColL2] & 4)"

End Sub

Upvotes: 1

Views: 46

Answers (1)

Mertinc
Mertinc

Reputation: 781

You should not write your variables within brackets.

So:

Worksheets("Calcs").Range("F4:F" & lRow).Formula = "=SUM('Resource Details'!" & [ColL] & "4:" & [ColL2] & "4)"

Can you please try your code as I corrected above and see how it goes.

Upvotes: 1

Related Questions