loop...until ..

[Yorson]

Please help me with the following code:

Sub looprange()

x = ActiveCell.Row

y = ActiveCell.Column

'Have y start at column 2

y = 2

'Loop until a blank row is found

Do While Cells(y, 2).Value <> ""

Column(y, 2).Value = Cells(x, 14).Value

'increase the value of x by 1 to act on the next row

x = x + 1

Loop


End Sub



Sources data as follows:

Column L Column M Column N Column O
Account Number Account Number Contract Date Contract Date
Account Number xxx-xxx-xxxx Contract Date 2001-02-19 12:00AM
Account Number xxx-xxx-xxxx Contract Date 2000-12-22 12:00AM
Account Number xxx-xxx-xxxx Contract Date 2001-05-28 12:00AM
Account Number xxx-xxx-xxxx Contract Date 2002-03-21 12:00AM
Account Number xxx-xxx-xxxx Contract Date 2002-05-22 12:00AM


What I need is that the heading in Column M " Account Number " will be
automatically pasted into the whole column L when I run Macro, and
column O heading " contact date " will does the same thing -- paste
into whole column N until it reach the blank cells, and so on.

Could somebody help me to fix the code which I did--it seems that it
doesn't work.

I would appreciate your help.

Yorson

 

[Guest]

Maybe this will help. Select cell in column M containing your header and run
the macro, repeat for column O.

Sub LoopRange()
With ActiveCell
Range(.Offset(0, -1), _
Cells(.End(xlDown).Row, .Column - 1)).Value = .Value
End With
End Sub

 

[Yorson]

Perfect, I test it --it works!

Thanks JMB!

 

[Yorson]

Hi, JMB

I have a new problem here, can you tell me how to insert the new column
when I perform the macro, for example, the column already filled with
the raw data, and I llike to ask macro to insert new column"L" ,
well... maybe you can modify the code for me.

Thanks again!

Yorson