CODEWITHSUNDEEP
excelvba
T. Dunar
T. Dunar

Reputation: 1

VBA FormulaR1C1 adds parentheses when HLOOKUP referances another workbook

I'm using the following code to look up a variable workbook and retrieve that data. It enters a formula into the cell range that grabs the data out of the other workbook. My problem is that if I use R1C1 style in the formula to indicate what range in the other workbook I want to search, VBA enters the formula as if it is A1 style. Then the formula will not return the correct info.

But if I leave that portion of the code in A1 style, VBA adds parentheses to the range when it enters it into the cell and the formula fails.

Here is the line of code:

Range("R8:R" & FinalRow).FormulaR1C1 = "=IFERROR(HLOOKUP(IF(RIGHT(RC[-13],2)=""B4"",INDEX('[" & MyNM & " " & MyCC & " " & MyUnit & " EVMS.xlsm]" & myUA _
                & "'!D:E,MATCH(RC4,'[" & MyNM & " " & MyCC & " " & MyUnit & " EVMS.xlsm]" & myUA & "'!E:E,0),1)," _
                & "INDEX(INDIRECT(""'[" & MyNM & " " & MyCC & " " & MyUnit & " EVMS.xlsm]""&RC5&""'!D:E"",TRUE)," _
                & "MATCH(RC4,INDIRECT(""'[" & MyNM & " " & MyCC & " " & MyUnit & " EVMS.xlsm]""&RC5&""'!E:E"",TRUE),0),1))" _
                & ",'[" & MyNM & " " & MyCC & " " & MyUnit & " EVMS.xlsm]SOP'!R1C3:R4C41,4,FALSE),""NP"")"

Here is the result:

=IFERROR(HLOOKUP(IF(RIGHT(E13,2)="B4",INDEX('[5P1B 4520 5015 EVMS.xlsm]UA1B'!D:(E),MATCH($D13,'[5P1B 4520 5015 EVMS.xlsm]UA1B'!E:(E),0),1),INDEX(INDIRECT("'[5P1B 4520 5015 EVMS.xlsm]"&$E13&"'!D:E",TRUE),MATCH($D13,INDIRECT("'[5P1B 4520 5015 EVMS.xlsm]"&$E13&"'!E:E",TRUE),0),1)),'[5P1B 4520 5015 EVMS.xlsm]SOP'!$C$1:$AO$4,4,FALSE),"NP")

Upvotes: 0

Views: 238

Answers (1)

Josh Fierro
Josh Fierro

Reputation: 140

I see that you're using Indirect in this formula, and that would only work if the referenced workbook is open. If it is open, then you can make your whole formula indirect and get rid of all the hassles that way. You can do this by referencing Address(Row()-x,Column()-y) within your formulas so that they work like an R1C1 style formula, and they reference different rows/columns depending on their own position even though the formula is identical in every cell. An example of a simple formula that works this way is this:
=INDIRECT("'[My Workbook.xlsx]Sheet1'!"&ADDRESS(ROW()-3,COLUMN()-1))

I believe this will do what you need:

Range("R8:R" & FinalRow).Formula = "=IFERROR(HLOOKUP(IF(RIGHT(INDIRECT(Address(Row()-13,Column()+2)))=""B4"",INDEX('[" & MyNM & " " & MyCC & " " & MyUnit & " EVMS.xlsm]" & myUA _
            & "'!D:E,MATCH(INDIRECT(Address(Row(),Column()+4)),'[" & MyNM & " " & MyCC & " " & MyUnit & " EVMS.xlsm]" & myUA & "'!E:E,0),1)," _
            & "INDEX(INDIRECT(""'[" & MyNM & " " & MyCC & " " & MyUnit & " EVMS.xlsm]""&RC5&""'!D:E"",TRUE)," _
            & "MATCH(INDIRECT(Address(Row(),Column()+4)),INDIRECT(""'[" & MyNM & " " & MyCC & " " & MyUnit & " EVMS.xlsm]""&RC5&""'!E:E"",TRUE),0),1))" _
            & ",'[" & MyNM & " " & MyCC & " " & MyUnit & " EVMS.xlsm]SOP'!INDIRECT(ADDRESS(ROW()+1,COLUMN()+3 &":"& ADDRESS(ROW()+4, COLUMN()+41)),4,FALSE),""NP"")"

Upvotes: 0

Related Questions