VBA/Macro help needed with 'Do While' A:A has data - combine B&C into D

[robospike]

Hi, This is my first post, I hope someone can help.

I have three columns of data and need to concat column 2 and 3 into column 4. Data Example:

_ A B C D E F G
1 a a a
2 a a a
3 a a a
4 a a
5 a a
6 a a
7 a
8 a

Where B and C are both present, I need them both to appear in D.
Where B is present but C is not, I need B to appear in D (it could attempt to add C, but I need B).

The current issue I have is I run this macro when C1 is selected and it will complete till C4 and stop as C4 is blank, D4-6 should get a value but don't.

I also have some data is A that is always present, right through to the last row, so If the loop worked on A:A until it reached a blank row (but combined B and C into D) that would be fine.

Current macro:

Sub ConcatColumns()

Do While ActiveCell <> "" 'Loops until the active cell is blank.

ActiveCell.Offset(0, 1).FormulaR1C1 = _
ActiveCell.Offset(0, -1) & "" & Chr(10) & "" & ActiveCell.Offset(0, 0)

ActiveCell.Offset(1, 0).Select
Loop

End Sub

I did try setting this line to contain a value that is not present, however the macro runs forever and a day down the sheet.
eg: Do While ActiveCell <> "XXXXXX"

Any help would be very much apreciated.

 

[lrobbo314]

How about something like this?

Sub xconcat()
Dim LR  As Long
Dim i   As Long

LR = Range("A" & Rows.Count).End(xlUp).Row()

For i = 1 To LR
    If Cells(LR, 2) <> vbNullString And Cells(LR, 3) <> vbNullString Then
        Cells(LR, 4) = Cells(LR, 2) & " " & Cells(LR, 3)
    ElseIf Cells(LR, 3) = vbNullString And Cells(LR, 2) <> vbNullString Then
        Cells(LR, 4) = Cells(LR, 2)
    Else
        'Another condition
    End If
Next i

End Sub

 

[robospike]

Thanks for the reply, I don't seem to be able to get this to run. Any thoughts? I get no output at all.

 

[lrobbo314]

I used the wrong variable...

Sub xconcat()
Dim LR  As Long
Dim i   As Long

LR = Range("A" & Rows.Count).End(xlUp).Row()

For i = 1 To LR
    If Cells(i, 2) <> vbNullString And Cells(i, 3) <> vbNullString Then
        Cells(i, 4) = Cells(i, 2) & Cells(i, 3)
    ElseIf Cells(i, 3) = vbNullString And Cells(i, 2) <> vbNullString Then
        Cells(i, 4) = Cells(i, 2)
    Else
        'Another condition
    End If
Next i

End Sub

 

[robospike]

This is great, it has almost solved my problem completely! One last issue I have with this, is that I have to run it against 4 different column pairs on the sheet. eg: B+C into D then M+N into O etc.

I currently call this from another macro by selecting a cell then calling the macro, eg in the above example C1. Is there a way I can link this to always assume A has data and loop through it but effect changes based on the active cell and the cell next to it?

Thanks you so much for your help with this, I have been banging my head on the table.

 

[lrobbo314]

I don't really know what you are asking for.

I can modify it to do the same thing to columns M, N, and O.

As for the activecell portion of your question... Do you mean that you want to be able to have say, C3, selected and the macro will run from there down ignoring the rows above it?

Is there a way I can link this to always assume A has data

The code is using column A to figure out how many rows to deal with, so it is assuming that there is always data in A.

 

[robospike]

Re: column A this is perfect.

This code is called at three different times, by a seperate macro. When it is called the data is always presented as 'data column' 'data column' 'result column', and the first cell below the heading in the second data column is selected (i missed the heading in my initial example, so this would be C2 (not C1) as it would have a heading. When the macro is called for M+N into O, then N2 would be selected. eg: it is called like this:

Range("N2").Select
Application.Run "'Test.xlsm'!ConcatColumns"

Therefore I was wondering if I can keep the column A login and adjust your referances to: Cells(i, 2) to something like: ActiveCell.Offset(0, -1)

Hope this helps, appologies for my unclear description. I am very greatful for your help.

 

[robospike]

Ok, so with a little adjustment, I think I have this working, by selecting C1 or N1 this will work on BCD or MNO or any other three columns:

Sub testing2()

Dim LR As Long
Dim i As Long

LR = Range("A" & Rows.Count).End(xlUp).Row()

For i = 1 To LR
If ActiveCell.Offset(i, -1) <> vbNullString And ActiveCell.Offset(i, 0) <> vbNullString Then
ActiveCell.Offset(i, 1) = ActiveCell.Offset(i, -1) & "" & Chr(10) & "" & ActiveCell.Offset(i, 0)
ElseIf ActiveCell.Offset(i, 0) = vbNullString And ActiveCell.Offset(i, -1) <> vbNullString Then
ActiveCell.Offset(i, 1) = ActiveCell.Offset(i, -1)
ElseIf ActiveCell.Offset(i, -1) = vbNullString And ActiveCell.Offset(i, 0) <> vbNullString Then
ActiveCell.Offset(i, 1) = ActiveCell.Offset(i, 0)
Else
'Another condition
End If
Next i

End Sub

 

[robospike]

Now posted in Code Tags:

Sub testing2()

Dim LR  As Long
Dim i   As Long

LR = Range("A" & Rows.Count).End(xlUp).Row()

For i = 1 To LR
    If ActiveCell.Offset(i, -1) <> vbNullString And ActiveCell.Offset(i, 0) <> vbNullString Then
        ActiveCell.Offset(i, 1) = ActiveCell.Offset(i, -1) & "" & Chr(10) & "" & ActiveCell.Offset(i, 0)
    ElseIf ActiveCell.Offset(i, 0) = vbNullString And ActiveCell.Offset(i, -1) <> vbNullString Then
        ActiveCell.Offset(i, 1) = ActiveCell.Offset(i, -1)
    ElseIf ActiveCell.Offset(i, -1) = vbNullString And ActiveCell.Offset(i, 0) <> vbNullString Then
        ActiveCell.Offset(i, 1) = ActiveCell.Offset(i, 0)
    Else
        'Another condition
    End If
Next i

End Sub

Thank you so much for your help with this, it seems to be working a treat. I have several code blocks to look through and attempt to adjust now. I am so greatful.

 

[lrobbo314]

Great. Sorry I was away from the forum for a while, but it's awesome that you got it worked out. Glad to help in the small way that I could.