Select/Copy/Paste in Macro/VBA

  • Thread starter Thread starter Jim Clark
  • Start date Start date
J

Jim Clark

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?
 
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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA set thick borders around non empty cells in range 0
Excel VBA 1
Special Copy, Paste Special macro 3
Copying a formula to a range 2
Select and copy only cells with values, open an app, paste into 2
Code to do a simple clean of one column by copying and pasting(finding and replacing) phrases in adj 4
Pasting cell contents into a macro 1
paste values without copy-paste values 1

Back
Top