Excel Formulas in VBA Editor

[harrisont9]

Hello,

I'm trying to automatize a report. I have several huge formulas that I would like to embed into the VBA editor. Can anyone help?

This is the formula I want the Macro to enter:

IFERROR(IF(IF(ISNA(VLOOKUP(A:A,'Q1186 Warehouse Order Roundup'!A:S,MATCH("SHIP_STATUS",'Q1186 Warehouse Order Roundup'!$A$1:$S$1,0),0)),VLOOKUP(A:A,'Q1188 Staged not shipped'!A:S,MATCH("SHIPPING_STATUS",'Q1188 Staged not shipped'!$A$1:$S$1,0),0),VLOOKUP(A:A,'Q1186 Warehouse Order Roundup'!A:S,MATCH("SHIP_STATUS",'Q1186 Warehouse Order Roundup'!$A$1:$S$1,0),0))=0,"",IF(ISNA(VLOOKUP(A:A,'Q1186 Warehouse Order Roundup'!A:S,MATCH("SHIP_STATUS",'Q1186 Warehouse Order Roundup'!$A$1:$S$1,0),0)),VLOOKUP(A:A,'Q1188 Staged not shipped'!A:S,MATCH("SHIPPING_STATUS",'Q1188 Staged not shipped'!$A$1:$S$1,0),0),VLOOKUP(A:A,'Q1186 Warehouse Order Roundup'!A:S,MATCH("SHIP_STATUS",'Q1186 Warehouse Order Roundup'!$A$1:$S$1,0),0))),"")

<tbody>
</tbody>

This is how it looks in the VBA editor window. Everything works fine, except the section in bold.

Sheets("Formulas").Select
Range("C2").Select
Selection.Copy
Sheets("DataConsolidation").Select
Range("D2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(IF(ISNA(VLOOKUP(C[-3],'Q1186 Warehouse Order Roundup'!C[-3]:C[15],MATCH(""SHIP_STATUS"",'Q1186 Warehouse Order Roundup'!R1C1:R1C19,0),0)),VLOOKUP(C[-3],'Q1188 Staged not shipped'!C[-3]:C[15],MATCH(""SHIPPING_STATUS"",'Q1188 Staged not shipped'!R1C1:R1C19,0),0),VLOOKUP(C[-3],'Q1186 Warehouse Order Roundup'!C[-3]:C[15],MATCH(""SHIP_STATUS"",'Q1186 Warehous" & _
"Roundup'!R1C1:R1C19,0),0))=0,"""",IF(ISNA(VLOOKUP(C[-3],'Q1186 Warehouse Order Roundup'!C[-3]:C[15],MATCH(""SHIP_STATUS"",'Q1186 Warehouse Order Roundup'!R1C1:R1C19,0),0)),VLOOKUP(C[-3],'Q1188 Staged not shipped'!C[-3]:C[15],MATCH(""SHIPPING_STATUS"",'Q1188 Staged not shipped'!R1C1:R1C19,0),0),VLOOKUP(C[-3],'Q1186 Warehouse Order Roundup'!C[-3]:C[15],MATCH(""SHIP_ST" & _
"1186 Warehouse Order Roundup'!R1C1:R1C19,0),0))),"""")"

 

[wigi]

Hello

You can do far better (IMHO) by not doing this in VBA.
Put the formula in a (hidden) cell and use VBA to just grab the formula:

 Range("D2").Formula = Range("H1").Formula

For example.

 

[harrisont9]

Hey Wigi,

The sheet where the formulas need to go is first created by a macro. What would the code be to tell VBA to grab the formula from a hidden cell in a different worksheet?

Thanks,

 

[wigi]

You can add the reference to the sheet in front:

Sheets("nameofthesheet").Range("H1").Formula

 

[harrisont9]

Wigi,

What if the formulas I need come from an entire separate workbook? For example, the formula from a cell in sheet X in workbook X needs to go into a cell in sheet Y from workbook Y.

 

[wigi]

What I showed you before, was adding the sheet name in front of the Range.
By the same token, add the workbook reference in front, again, so that you can refer to a workbook other than the Active workbook.