none
VBA CODE TO INSERT COMMA BETWEEN 2 CONCATENATED CELLS RRS feed

  • Question

  • Hello VBA developers, 

    Just wanted to check in how to edit my code below to include a comma between 2 cells that I concatenated. Basically, I want to concatenate Col F and K and put the values in Col G. For example, when Col F2=Brussells and K2=RAIL, the output in G2 will be Brussells,RAIL. Below is my code and thanks in advance for your help!

    Dim LastRow As Long
         
        With ActiveSheet
            LastRow = .Cells(.Rows.count, "F").End(xlUp).row
            With .Range("G2:G" & LastRow)
                .Formula = "=F2 & K2"
                .Value = .Value
            End With
        End With
    
    

    Monday, November 6, 2017 10:16 PM

Answers

  • To include a double quote in the string that is already enclosed in double quotes, insert 2 double quotes.

    Sub test()
        Dim LastRow As Long
        With ActiveSheet
            LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
            With .Range("G2:G" & LastRow)
                .Formula = "=F2 & "","" & K2"
                .Value = .Value
            End With
        End With

    End Sub


    Regards, OssieMac

    • Marked as answer by IamJackie Monday, November 6, 2017 10:43 PM
    Monday, November 6, 2017 10:33 PM

All replies

  • To include a double quote in the string that is already enclosed in double quotes, insert 2 double quotes.

    Sub test()
        Dim LastRow As Long
        With ActiveSheet
            LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
            With .Range("G2:G" & LastRow)
                .Formula = "=F2 & "","" & K2"
                .Value = .Value
            End With
        End With

    End Sub


    Regards, OssieMac

    • Marked as answer by IamJackie Monday, November 6, 2017 10:43 PM
    Monday, November 6, 2017 10:33 PM
  • Thank you very much! 
    Monday, November 6, 2017 10:43 PM