Macro for copying a value to a variable no. of rows

[Ian Grega]

Hello, I would really appreciate it if anyone can help with the following, my
$70 book has only confused me further.

I am trying to write a macro that will enter a value from a cell into a
variable number of rows below it in the same column. eg copy the value from
cell A1 into the cells commencing at A5 and on for the number of rows
specified by an integer entered in cell A2, and then do the same for B2 etc

In the following example the Number 2 is entered in 4 rows, the number 3 is
entered in 2 rows

2 4
3 5

2 4
2 4
2 4
4
4

Sincerely appreciate someones help.
Ian Grega

 

[Ian Grega]

Apologies, in the example given the number 2 is entered in 3 rows below it
and the number 4 is entered in 5 rows. I changed the numbers to avoid
confusion and only made it more so.

Ian Grega

 

[Per Jessen]

Hi Ian

Here's a way to do it.
The macro will continue until it meets a empty cell in row 1.

Sub CopyValues()
Dim cOff As Integer
Dim CopyRange As String
Dim iRange As String
Dim StartCell As String

cOff = 0
CopyRange = "A1"
iRange = "A2"
StartCell = "A5"
Do Until Range(CopyRange).Offset(0, cOff).Value = ""
ValueToCopy = Range(CopyRange).Offset(0, cOff).Value
Iteration = Range("A2").Offset(0, cOff).Value
For i = 0 To Iteration - 1
Range(StartCell).Offset(i, cOff) = ValueToCopy
Next
cOff = cOff + 1
Loop
End Sub

Regards,
Per

 

[Ian Grega]

Hello Per,

Greetings from Perth Australia, early tuesday morning here.
Thanks very much for your assistance. I will try it now and report back on
how I go.

Thanks again
Ian Grega

 

[Ian Grega]

Hi Per

Perfect.
Strangely a lot slower than I thought it would be.

Many thanks
Ian

 

[Per Jessen]

Hi Ian

Thanks for your reply from Copenhagen, Denmark

To speed up the macro, put this line in the start of the macro (after Sub
CopyValues()) :

Application.ScreenUpdating=False

And at the end goes this line (before End sub)

Application.ScreenUpdating=True

Best regards,
Per

 

[Ian Grega]

Cheers Per,

For whatever reason it became 10 times quicker after the file had been
closed and reopened later in the day, I will input your extra code just in
case it decides to go slow again.

Many thanks
Ian