Select/Copy/Paste in Macro/VBA
 

I'm trying to create a macro to preformat an imported text
file. One of the tasks is to concatenate lastname,
firstname, middleinit, suffix that is spread into 4
columns into one column. The number of rows varies with
the imported text file.

I've tried inserting a blank column and building the
concat formula in the top cell, but I'm having a problem
copying this formula down to all the cells below while
STOPPING at the last cell that has data to the right.

Suggestions for this newbie, please?

Select/Copy/Paste in Macro/VBA
 

Jim,

Assuming you have just entered the formula in A1, and A1 (or A2, or
Awhatever) is the active cell, the following will do it:

i = 1
Do Until IsEmpty(ActiveCell.Offset(i,1))
ActiveCell.Offset(i,0).FormulaR1C1 = ActiveCell.FormulaR1C1
i = i + 1
Loop

HTH,
Nikos

"Jim Clark" wrote in message
...
I'm trying to create a macro to preformat an imported text
file. One of the tasks is to concatenate lastname,
firstname, middleinit, suffix that is spread into 4
columns into one column. The number of rows varies with
the imported text file.

I've tried inserting a blank column and building the
concat formula in the top cell, but I'm having a problem
copying this formula down to all the cells below while
STOPPING at the last cell that has data to the right.

Suggestions for this newbie, please?