How to repeat a function on multiple rows in Excel VBA sub macro?

Question

Hi,

The macro works row   #2.   How do I repeat the macro for row # 3 up to row 20  without repeatingthe macro for each row?

Thanks for your help,              Usind Excel 2013 with Windows 10.

Roger

w1	w2	w3	w4	w5	w6	w7	w8	w9	w10	Total	Possible	w11
33	15	27	29	21	24	22	28	18	28	245	330
20	20	20	20	20	10	10	10	10	10	150

Sub Test30()

 'Write B2:J2 into A2:I2

 Range("A2").Resize(, 9).Value = Range("B2").Resize(, 9).Value

 'Write M2 into J2

 Range("J2").Value = Range("M2").Value

 'Clear M2

 Range("M2").ClearContents

End Sub

Answer 1

You just need to create a loop, like this:

Sub Test30()
Dim x As Integer

For x = 2 To 20
'Write B2:J2 into A2:I2
Range("A" & x).Resize(, 9).Value = Range("B" & x).Resize(, 9).Value
'Write M2 into J2
Range("J" & x).Value = Range("M" & x).Value
'Clear M2
Range("M" & x).ClearContents
Next x
End Sub

Answer 2

Hi Jason,

Thankyou very much for your reply.  That is exactly what I was looking for.

Roger,