Dynamic lookup value requires correct syntax

[guilhermelemos]

Hi,

I am trying to create a lookup that will get the parts with the highest chance of being used depending on the location.

I figured out the formula in Excel, but I needed to convert into VBA. I used the recorder, but I need to add a variable to the formula, so the lookup value of my formula is correct.

I am getting the following error.

Run-time error '1004'
Application-defined or object-defined error


I think it's because the syntax of my formula is not correct when I reference the variable i.

Can anyone help?

Thanks in advance.



Sub Additional_Parts_Small_Locations()
Dim strRng As String



Dim i As Integer
i = 2
Windows("FSL quantity template attempt2.xlsm").Activate
Sheets("Locations").Select
Sheets("Locations").Calculate
Range("A2").Select
Do While i < 43
If ActiveCell.Offset(0, 2) < 10 Then
Sheets("Summary").Select
Range("A600000").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = _
"=INDEX(INDIRECT(ADDRESS(1,1,,,""[Printers vs parts consumption_roundup.xlsx]Parts ranking per location"")&"":""&ADDRESS(20000,MATCH(Locations!RiC1 ,'[Printers vs parts consumption_roundup.xlsx]Parts ranking per location'!R1C1:R1C42,0))),MATCH(1,INDIRECT(ADDRESS(1,MATCH(Locations!RiC1,'[Printers vs parts consumption_roundup.xlsx]Parts ranking per location'!R" & _
"2,0),,,""[Printers vs parts consumption_roundup.xlsx]Parts ranking per location"")&"":""&ADDRESS(20000,Locations!RiC1,'[Printers vs parts consumption_roundup.xlsx]Parts ranking per location'!R1C1:R1C42,0))),0),1)"
End If




i = i + 1
ActiveCell.Offset(1, 0).Select
Loop


End Sub

 

[AlphaFrog]

Concatenate the variable into the formula string with something like this...

Locations!R" & i & "C1

 

[guilhermelemos]

Concatenate the variable into the formula string with something like this...

Locations!R" & i & "C1

Thanks, but I am still getting the same error...

 

[AlphaFrog]

Thanks, but I am still getting the same error...

Well the method works.
So there may be something wrong in the formula or show your modified code to see how you implemented the change.

 

[guilhermelemos]

Sub Additional_Parts_Small_Locations()
Dim strRng As String
Dim i As Integer
i = 2
Windows("FSL quantity template attempt2.xlsm").Activate
Sheets("Locations").Select
Sheets("Locations").Calculate
Range("A2").Select
Do While i < 43
If ActiveCell.Offset(0, 2) < 10 Then
Sheets("Summary").Select
Range("A600000").End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = _
"=INDEX(INDIRECT(ADDRESS(1,1,,,""[Printers vs parts consumption_roundup.xlsx]Parts ranking per location"")&"":""&ADDRESS(20000,MATCH(Locations!R" & i & "C1 ,'[Printers vs parts consumption_roundup.xlsx]Parts ranking per location'!R1C1:R1C42,0))),MATCH(1,INDIRECT(ADDRESS(1,MATCH(Locations!R" & i & "C1,'[Printers vs parts consumption_roundup.xlsx]Parts ranking per location'!R" & _
"2,0),,,""[Printers vs parts consumption_roundup.xlsx]Parts ranking per location"")&"":""&ADDRESS(20000,Locations!R" & i & "C1,'[Printers vs parts consumption_roundup.xlsx]Parts ranking per location'!R1C1:R1C42,0))),0),1)"
End If




i = i + 1
ActiveCell.Offset(1, 0).Select
Loop


End Sub

 

[skywriter]

Can you show me what this formula would look like in a cell, I'm sure it has something to do with where you have quotes and ampersands etc., but with all the [ ' : I don't know what it should look like in a cell.
I need to see where the quotes are in the actual formula as it would appear in a cell without extra quotes and ampersands.

I had a problem like this years ago with code in Access and it took me a while of moving quotes and ampersands and what not before I figured it out. I'd be glad to mess with it for you.

 

[AlphaFrog]

I think this...
""[Printers vs parts consumption_roundup.xlsx]Parts ranking per location"")

Should be this...
'[Printers vs parts consumption_roundup.xlsx]Parts ranking per location'!)

Check that syntax for each instance.

 

[guilhermelemos]

It gets a syntax / compile error.

The formula that works on a cell looks like this:

=INDEX(INDIRECT(ADDRESS(1,1,,,"[Printers vs parts consumption_roundup.xlsx]Parts ranking per location")&":"&ADDRESS(20000,MATCH(Locations!A35,'[Printers vs parts consumption_roundup.xlsx]Parts ranking per location'!$A$1:$AP$1,0))),MATCH(1,INDIRECT(ADDRESS(1,MATCH(Locations!A35,'[Printers vs parts consumption_roundup.xlsx]Parts ranking per location'!$1:$1,0),,,"[Printers vs parts consumption_roundup.xlsx]Parts ranking per location")&":"&ADDRESS(20000,MATCH(Locations!A35,'[Printers vs parts consumption_roundup.xlsx]Parts ranking per location'!$A$1:$AP$1,0))),0),1)


Thanks

 

[AlphaFrog]

It looks like the formula in the VBA code is missing a MATCH and referencing row 2 instead of row 1 as in the reference formula.


ActiveCell.FormulaR1C1 = _
"=INDEX(INDIRECT(ADDRESS(1,1,,,""[Printers vs parts consumption_roundup.xlsx]Parts ranking per location"")&"":""&ADDRESS(20000,MATCH(Locations!R" & i & "C1,'[Printers vs parts consumption_roundup.xlsx]Parts ranking per location'!$A$1:$AP$1,0))),MATCH(1,INDIRECT(ADDRESS(1,MATCH(Locations!R" & i & "C1,'[Printers vs parts consumption_roundup.xlsx]Parts ranking per location'!R1,0),,,""[Printers vs parts consumption_roundup.xlsx]Parts ranking per location"")&"":""&ADDRESS(20000,MATCH(Locations!R" & i & "C1,'[Printers vs parts consumption_roundup.xlsx]Parts ranking per location'!$A$1:$AP$1,0))),0),1)"

 

[guilhermelemos]

It brings me back to error 1004.

Maybe I should simplify my formula.

Is there a way I have range, say A1:AP10000 and then I just pick the cell in the intersection of a column and a row? Is that the offset function?