VBA: copy + paste text of adjacent cell

[lesternope]

Hello,

I have a large table, in which I need to append to every line of column B (except the first) a comma + space key, and then add the contents of adjacent cell A right next to it.

so basically I want to go from this kind of table:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]Mr.[/TD]
[TD]Smith, John[/TD]
[/TR]
[TR]
[TD]Mrs. [/TD]
[TD]Miller, Betty[/TD]
[/TR]
</tbody>[/TABLE]

to this kind of table

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]Mr.[/TD]
[TD]Smith, John, Mr.[/TD]
[/TR]
[TR]
[TD]Mrs.[/TD]
[TD]Miller, Betty, Mrs.[/TD]
[/TR]
</tbody>[/TABLE]

anyone an idea on it? Thanks in advance!

 

[JackDanIce]

Welcome to the board. Try:

Sub Append()

    Dim x       As Long
    Dim arr()   As Variant
    
    x = Cells(Rows.Count, 1).End(xlUp).row
    arr = Cells(2, 1).Resize(x - 1, 2).Value
    
    For x = LBound(arr, 1) To UBound(arr, 2)
        arr(x, 2) = arr(x, 2) & ", " & arr(x, 1)
    Next x
        
    Cells(2, 1).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
    Erase arr
    
End Sub

 

[lesternope]

Welcome to the board. Try:

Sub Append()

    Dim x       As Long
    Dim arr()   As Variant
    
    x = Cells(Rows.Count, 1).End(xlUp).row
    arr = Cells(2, 1).Resize(x - 1, 2).Value
    
    For x = LBound(arr, 1) To UBound(arr, 2)
        arr(x, 2) = arr(x, 2) & ", " & arr(x, 1)
    Next x
        
    Cells(2, 1).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
    Erase arr
    
End Sub

Thanks jack, this basically does as it is supposed to do, except, the original file has more than 2 rows and this code only works on those first 2. I'm not sure which numbers in the code to amend in order to get it to work on all rows of col B. Any pointers appreciated!

 

[JackDanIce]

This line in the code finds the row number of the last filled cell in column A:

x = Cells(Rows.Count, 1).End(xlUp).row

Your reply suggests there is no more data after A3 in the csv sheet... The blue 1 is the column number it tries to find the last row on, i.e. 1 = column A, 2 = column B etc.

 

[footoo]

For x = LBound(arr, 1) To UBound(arr, 1)

 

[JackDanIce]

Good spot, thanks for correcting

 

[lesternope]

This line in the code finds the row number of the last filled cell in column A:

x = Cells(Rows.Count, 1).End(xlUp).row

Your reply suggests there is no more data after A3 in the csv sheet... The blue 1 is the column number it tries to find the last row on, i.e. 1 = column A, 2 = column B etc.

that's weird, since actually each row in A has a value (just as with B). The debugger also gives out x=2, which doesn't really make sense looking at the spreadsheet, as the current one I'm working at has 460 rows with text inside. you don't happen to know a snippet I could use in order to just manually enter the number of lines I want the macro to look at (unless that messes with the other parts of the code)?

 

[lesternope]

For x = LBound(arr, 1) To UBound(arr, 1)

this actually fixed it, thanks so much to both of you!

 

[JackDanIce]

Glad it worked, after footoo's correction!