Forum Discussion

hrh_dash's avatar
hrh_dash
Iron Contributor
Aug 05, 2022
Solved

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 test()
    
    Dim ws          As Worksheet
    Dim lastRow     As Long
    Dim str1        As String
    Dim str2        As String
    
    Set ws = Sheet1
    
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).EntireRow.Row
    
    str1 = ws.Range("B2:B" & lastRow).Value
    str2 = ws.Range("C2:C" & lastRow).Value
    
    ws.Range("I2:I" & lastRow).Value = str1 & " " & str2
    
End Sub

 

Appreciate the help in advance..

excel
Formulas and Functions
Macros and VBA
  • OliverScheurich's avatar
    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's avatar
    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).

Resources