I am putting together a list to loop through in Excel.

I want to loop down column A starting at A2, and copy the value from the active cell to D2 (always D2)

This is what I have so far:

Sub Test2() Range("A2").Select Do Until IsEmpty(ActiveCell) 'Take value from active Cell and copy to D2 MsgBox ("Continue") ActiveCell.Offset(1, 0).Select Loop
End Sub

I tried replacing 'Take value from active Cell and copy to D2 with Range("D2).value = ActiveCell but this did not work.

Any suggestions.

So to recap. D2 will equal A2, I will get message box, press continue, then D2 will equal A3, and so on...

I have also tried:

Sub TestLoop() Dim Selected As Variant Selected = Selection.Value Range("A2").Select Do Until IsEmpty(ActiveCell) Range("D2").Value = Selected MsgBox ("Continue") ActiveCell.Offset(1, 0).Select Loop
End Sub

1

2 Answers

Just figured I'd add a simple version of your answer without the .select

Sub TestLoop()
Dim r As Range
Set r = Range("A2")
Do Until IsEmpty(r) Range("D2").Value = r '.Value MsgBox ("Continue") Set r = r.Offset(1)
Loop
End Sub

1

I have solved this.

I was placing the variable outside of the loop, I think.

Sub TestLoop() Range("A2").Select Do Until IsEmpty(ActiveCell) Dim Selected As Variant Selected = ActiveCell.Value Range("D2").Value = Selected MsgBox ("Continue") ActiveCell.Offset(1, 0).Select Loop
End Sub

2

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy