Macro moving down the sheet cell by cell

[berlead]

I have successfully created a macro that copies and pastes data fro
three columns of a row into a single entry in a single cell in tha
same row. I am trying to get this macro to perform the same functio
in multiple cells directly beneath the row where I recorded it. Fo
example, I have it working in row 3. I want it to perform the sam
function in rows 4 through 200.

How do I get it to move down one row, execute, move down one row
execute, move down one row, execute etc and stay row relative...?

When I run the macro now, it always returns to row 3 and executes.

I am not a programmer and have very limited experience with macros (a
you can probably tell).

Thanks

 

[Stephen Rasey]

You will need to post your code because you must change some of the recorded
code to change the copy reference.

The secret will be to create a parent subrouting that moved to each new
selection, then
executes your copy-paste subroutine.

How do you want to tell it the range of cells to operate on? With a named
range.
Have the user highlight some cells and cycle through the cells?

You have a block of cells you select to start and it will loop until it
finds a row with a blank cell in the first column.

Sub CycleCells()
Dim rngTarget As Range
Set rngTarget = Selection

Set rngTarget = rngTarget.Resize(1, 3) 'change the selection to 1 row
by 3 cells.

Do Until (rngTarget.Cells(1, 1) = "" Or IsEmpty(rngTarget.Cells(1, 1)))
rngTarget.Select 'optional, only if you want to see it
change the selection
'MyCopyPaste rngTarget
'move the selection down one row
Set rngTarget = rngTarget.Offset(1, 0)
DoEvents '(always put this in a loop to make the computer list to
the keyboard and mouse)
Loop

End Sub

Stephen Rasey
Houston
http://wiserways.com
http://excelsig.org

 

[berlead]

Thanks for your reply. I have to create an email distribution list fo
a number of users in my division, and I am starting with a list o
first names, last names and then I know the our domain (@xxx.com) S
what I did is below: (I x'ed out parts of the names and domain fo
privacy reasons.)


Sub name()
'
' name Macro
' Macro recorded 8/26/2004 by rice-b
'

'
Range("K4").Select
ActiveCell.FormulaR1C1 = "AnXXXXX"
Range("N4").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = "AnXXXXX."
Range("L4").Select
ActiveCell.FormulaR1C1 = "DeXXXX"
Range("N4").Select
ActiveCell.FormulaR1C1 = "AnXXXXX.DeXXXX@"
Range("M4").Select
ActiveCell.FormulaR1C1 = "XXX.com"
Range("N4").Select
ActiveCell.FormulaR1C1 = "(e-mail address removed)"
Range("N5").Select
End Su

 

[Stephen Rasey]

You do not need a macro to do this.

Columns K, L, and M are three parts to an internet mail address.
give the N5 cell the formula
=K5 & "." & L5 & "@" & M5
Copy it down.

Stephen Rasey
Houston

 

[berlead]

That worked! Thanks for your reply! :-