Total on last column and after some vlookup

[Nervatos]

Hello there

I have found this one, but can't make my vlookup formula workring. Hope some can fix it up.

Public Sub CreateSumFormulas()
    Dim lngLastCol As Long, lngLastRow As Long
    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Ark45")
    Dim StartCell As String: StartCell = "C2"
    
    lngLastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
    lngLastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column + 1

    ws.Cells(lngLastRow, 1).Value = "Total"
    ws.Cells(1, lngLastCol).Value = "Enheder Total"

    ws.Range(Cells(2, lngLastCol), Cells(lngLastRow - 1, lngLastCol)).Formula = "=SUM(" & StartCell & ":" & Cells(2, lngLastCol - 1).Address(0, 0) & ")"
    ws.Range(Cells(lngLastRow, 3), Cells(lngLastRow, lngLastCol)).Formula = "=SUM(" & StartCell & ":" & Cells(lngLastRow - 1, 3).Address(0, 0) & ")"

    lngLastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
    lngLastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column + 1
    
    ws.Cells(1, lngLastCol).Value = "Værdi" 'Kolonne
    ws.Range(Cells(2, lngLastCol), Cells(lngLastRow - 1, lngLastCol)).Formula = "=IFERROR(SUM(" & Cells(2, lngLastCol - 1).Address(0, 0) & "*VLOOKUP(" & Cells(2, lngLastCol - 1).Address(0, 0) & "," & MasterSheet & "!A:G,3,FALSE)),"")"
 End Sub

I got an error on this line: ws.Range(Cells(2, lngLastCol), Cells(lngLastRow - 1, lngLastCol)).Formula = "=IFERROR(SUM(" & Cells(2, lngLastCol - 1).Address(0, 0) & "*VLOOKUP(" & Cells(2, lngLastCol - 1).Address(0, 0) & "," & MasterSheet & "!A:G,3,FALSE)),"")"

The error is: Run-time error '1004': Application-defined or object-defined error.

MasterSheet is the sheet where we lookup for, and I want it to search for numbers in A column and should be multiply with the column before this vlookup. Hope some can fix my problem. Thanks!

 

[DanteAmor]

"=IFERROR(SUM(" & Cells(2, lngLastCol - 1).Address(0, 0) & "*VLOOKUP(" & Cells(2, lngLastCol - 1).Address(0, 0) & "," & MasterSheet & "!A:G,3,FALSE)),"")"

SUM(" & Cells(2, lngLastCol - 1).Address(0, 0) & "*

Do you want to add or multiply?

Do you have the formula done manually in the cell? you can put that formula here to understand what you want and help you with the code.

Or maybe:

Public Sub CreateSumFormulas()
    Dim lngLastCol As Long, lngLastRow As Long
    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Ark45")
    Dim StartCell As String: StartCell = "C2"
    
    lngLastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
    lngLastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column + 1

    ws.Cells(lngLastRow, 1).Value = "Total"
    ws.Cells(1, lngLastCol).Value = "Enheder Total"
    ws.Cells(1, lngLastCol + 1).Value = "Værdi" 'Kolonne

    ws.Range(ws.Cells(2, lngLastCol), ws.Cells(lngLastRow - 1, lngLastCol)).Formula = _
      "=SUM(" & StartCell & ":" & ws.Cells(2, lngLastCol - 1).Address(0, 0) & ")"
    ws.Range(ws.Cells(lngLastRow, 3), ws.Cells(lngLastRow, lngLastCol)).Formula = _
      "=SUM(" & StartCell & ":" & ws.Cells(lngLastRow - 1, 3).Address(0, 0) & ")"

    lngLastRow = lngLastRow + 1
    lngLastCol = lngLastCol + 1
    
    ws.Range(ws.Cells(2, lngLastCol), ws.Cells(lngLastRow - 1, lngLastCol)).Formula = _
      "=IFERROR(" & ws.Cells(2, lngLastCol - 1).Address(0, 0) & _
      "* VLOOKUP(" & ws.Cells(2, lngLastCol - 1).Address(0, 0) & ",MasterSheet!A:G,3,FALSE), """")"
 End Sub

 

[Nervatos]

Thanks DanteAmor for your help, it's working well.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.