Sub stopping at section where formula should be dragged down

[Mr2017]

Hi

I've got a Sub which runs successfully until the point where a formula needs to be dragged down to the rest of the column.

In short, data is copied from column H (cell 15 downwards) in the "Main Sheet" of a file called "Main Sheet" and pasted into cell DC15 of the same sheet.

The code then goes to another file called "Formula Wbk" and then transfers that formula to cell DD16 of the "Main Sheet" in the "Main Sheet" file.

However, the code then stops out at the last line of this code, which is supposed to fill the formula from cell DD16 to the rest of column DD (please note the two D's). And I get a debugging error.

Does anyone know why this is? Have I mistyped something?

TIA

Sub 
Workbooks(Mainsheet).Activate
Worksheets("Main sheet").Select
Range("H15", Range("H15").End(xlDown)).Copy
Range("DC15").PasteSpecial xlPasteValues
 
Workbooks(Mainsheet).Sheets("Main sheet").Range("DD16").Formula = Workbooks(Formula_Wbk).Sheets("New Data").Range("P2").Formula

Range("DD16:D" & Range("DC" & Rows.Count).End(xlUp).Row).Formula = "=IFERROR((AY16/K16)-AF16,"")"
End Sub

 

[Fluff]

You have forgotten to double-up on the quotes inside the formula.

What is the point in copying the formula from another workbook, only to over-write it with another formula?

 

[Mr2017]

Ok, thanks for clarifying that.

Good question re the formula - it's actually the same formula, but the second last line was added as a way of copying the text of the formula, so the cell references don't change when it's added to the new sheet. Then the last line of code is intended to drag the formula down to the other rows. Is there a better way of doing it?

Also, what is the general rule with quotes in the VB editor? Does everything in a formula that has quotes have to be surrounded by double quotes? Or is it just the instances where the quotes are intended as a blank? So if there was a formula that read =if(A7=1,"That cell has the number 1","""") would the text surrounded by the quotes have to have additional quotes, as well?

TIA

 

[RoryA]

The rule is that any quotes inside a quoted string (here, the formula is the quoted string) need to be doubled. So:

.Formula = "=IFERROR((AY16/K16)-AF16,"""")"

The red quotes mark the beginning and end of the quoted string. In order to prevent the compiler from treating any quotes in between as terminating the string, you have to escape them by doubling them - the blue and green ones.

 

[Mr2017]

Ok, thanks Rory.