Sub stopping at section where formula should be dragged down

Mr2017

Active Member
Joined
Nov 28, 2016
Messages
458
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

Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,967
Office Version
365
Platform
Windows
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

Active Member
Joined
Nov 28, 2016
Messages
458
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,618
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
The rule is that any quotes inside a quoted string (here, the formula is the quoted string) need to be doubled. So:

Rich (BB code):
.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.
 

Forum statistics

Threads
1,086,116
Messages
5,387,923
Members
402,088
Latest member
poppa57

Some videos you may like

This Week's Hot Topics

Top