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.

 

[robospike]

It was a real help. Trust me.

I had one other issue following this on another topic, so I posted it seperatly: http://www.mrexcel.com/forum/excel-...-based-currently-relying-3rd-party-addon.html

Thanks again.

 

[robospike]

Hi lrobbo314,

Following on from this I have tried to run this against a larger dataset now, and get an error. I suspect this is doe to something within the data, however I am not sure how to prevent the error or locate the cell causing the issue.

The error is runtime error 13 - type mismatch.

Any ideas?

 

[robospike]

I have identified some cells that start = even though there is no formula, so I have used a find and replace to remote these, that has fixed it.

 

[lrobbo314]

I would do whatever I could in your case to avoid using the Activecell. It's generally considered poor practice in coding, unless absolutely necessary. I would try assigning a variable to whatever the activecell is. So, if your active cell is D4, you can have variables for the row/column and work from there with your code.

Having said all of that, I imagine that your error is coming from using the activecell with the offset. For example, if you are in cell A1 and you try to offset that cell by activecell.offset(-1,0), or activecell.offset(0,-1), you will run into an error because those cells don't exist.

Try seeing what cell your code errors out on and see if that might be what is causing the error.

 

[robospike]

Thanks for the advice. The issue was cells starting with =- however I will review the macro with your advice re activecell in mind. Thanks.