Sum If VBA syntax with variable columns?

See more:

VB

MS-Excel

Hi,

I have a string TargetCol that contains the column number of a vlookup result, namely B,C,D,E, etc. I want my SumIf statement to lookup the value contained in the string TargetCol. Below is an extract of the code I'm using. I'm unable to get the right syntax for the "&TargetCol"'":"'"&TargetCol"' part in the SumIF statement.

Any help would be highly appreciated.

If Not rngFound Is Nothing Then
    rngAddress = rngFound.Address
    rngAddress1 = Trim(Left(rngAddress, 2))
    TargetCol = Trim(Right(rngAddress1, 1))
    'MsgBox "Match found at column: " & TargetCol

    For i = 5 To lastRow
     For k = 4 To 11
      If Cells(i, k) = "" Then

       Cells(i, k).FormulaR1C1 = "=SUMIF('Netting SAP - TCD'!C1:C1,'AK090N Trade Receivables'!RC1,'Netting SAP - TCD'!'"&TargetCol"'":"'"&TargetCol"')"

End If
     Next k
    Next i

Thanks,
Vinayak


[edit]Code block changed to VB to correct syntax coloring - OriginalGriff[/edit]