Macro to concatenate for row 2 to the last row

I would like to create a simple dynamic concatenate macro to merge both strings in column B and C. However, it popping out an error "run time error 13 type mismatch". Below is my code so far: Sub ...

excel

Formulas and Functions

Macros and VBA

OliverScheurich

Aug 05, 2022

hrh_dash

Sub test()
    
    Dim ws          As Worksheet
    Dim lastRow     As Long
    Dim str1        As Variant
    Dim str2        As Variant
    
    Set ws = Tabelle1
    
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).EntireRow.Row
    
    str1 = ws.Range("B2:B" & lastRow).Value
    str2 = ws.Range("C2:C" & lastRow).Value
    
    ws.Range("I2:I" & lastRow).FormulaR1C1 = "=RC[-7]&"" ""& RC[-6]"
    
End Sub

You can try this code which seems to work in my sheet (sheetname is Tabelle1).

OliverScheurich

Gold Contributor

Aug 05, 2022

hrh_dash

Sub test()
    
    Dim ws          As Worksheet
    Dim lastRow     As Long
    Dim str1        As Variant
    Dim str2        As Variant
    
    Set ws = Tabelle1
    
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).EntireRow.Row
    
    str1 = ws.Range("B2:B" & lastRow).Value
    str2 = ws.Range("C2:C" & lastRow).Value
    
    ws.Range("I2:I" & lastRow).FormulaR1C1 = "=RC[-7]&"" ""& RC[-6]"
    
End Sub

You can try this code which seems to work in my sheet (sheetname is Tabelle1).

hrh_dash
Iron Contributor
Aug 06, 2022
thanks for the assistance!

Forum Discussion

Macro to concatenate for row 2 to the last row

Resources