PDA

View Full Version : Do until loop in Excel



tjakatjs
11-29-2016, 05:45 AM
I need help creating a loop for this fairly simple code. I haven't included the actual spreadsheets as they are rather cumbersome but I think this is simple enough that someone should be able to help. Here's the code with the notes of what needs to be added/changed. Thank you in advance for your help.


Sheets("SA V.1").Select
Range("B22").Select
ActiveCell.FormulaR1C1 = "='MACD Table'!R[-21]C"
Do
'until column A is blank
Sheets("SA V.1").Select
Range("B21").Select
ActiveCell.FormulaR1C1 = "='MACD Table'!R[-19]C[-1]"
'I want this to move down a row each time the routine repeats

Range("B23").Select
Selection.Copy
Sheets("MACD Table").Select

Range("B2").Select
'This also needs to move down a row each time
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Loop Until IsEmpty(Cells(iRow, 1))

End Sub

Bob Phillips
11-30-2016, 02:39 AM
Dim lastrow As Long

With Worksheets("SA V.1")

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("B22").Resize(lastrow - 21).FormulaR1C1 = "='MACD Table'!R[-21]C"
.Range("B22").Resize(lastrow - 21).Copy
Worksheets("MACD Table").Range("B2").PasteSpecial Paste:=xlPasteValues
End With

tjakatjs
12-01-2016, 12:26 PM
I don't think I explained this very well and I suppose it is harder without the spreadsheet. I reposted the question with more information and the spreadsheet. Thank you for your help.

SamT
12-01-2016, 03:40 PM
I reposted the question with more information and the spreadsheet.

Where did you repost it?