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]

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?

Try this though I don't have a means to test it.

[COLOR=darkblue]Sub[/COLOR] Additional_Parts_Small_Locations()
    [COLOR=darkblue]Dim[/COLOR] strRng [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i      [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
    Windows("FSL quantity template attempt2.xlsm").Activate
    Sheets("Locations").Select
    Sheets("Locations").Calculate
    [COLOR=darkblue]For[/COLOR] i = 2 [COLOR=darkblue]To[/COLOR] 42
        [COLOR=darkblue]If[/COLOR] Sheets("Locations").Range("C" & i) < 10 [COLOR=darkblue]Then[/COLOR]
            Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).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)"
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next i[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[guilhermelemos]

Thanks, but I still get the same 1004 error.

Does VBA has limitations in terms of functions you can combine or size...

 

[AlphaFrog]

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?

Range("A1:AP10000").Cells(r, c)

r and c are the row-column intersect. There are other VBA methods as well.

Does <acronym title="visual basic for applications">VBA</acronym> has limitations in terms of functions you can combine or size...

Everything has a limit. Not sure what you're asking.

You could do this without a formula, but I don't follow what the current formula does so I cannot suggest the alternative code.


Going to bed now. If you have additional questions, I'll look at them tomorrow if they haven't been resolved.

 

[skywriter]

I have an idea maybe it's off base. I was trying to just paste the formula as a string into A1 on my computer so I could see how it looked compared to the one you sent me copied direct from the cell it was in. So I made a string variable and every time I tried to paste it I got that error and I couldn't figure out why. I finally realized duh it was because the string is basically a formula with the = in front of it and it wouldn't paste, maybe because all the stuff you reference in your formula I don't have. So I put a space before = and then pasted it. All the syntax seemed to work fine quotes and all that good stuff, and it appeared to match your formula, but it was in R1 format and the one you sent me has A1. So I thought maybe you might try what I did or maybe you already did. Put an extra space in front of the = in your code. Run the code to generate the string and paste it into a cell and see how it matches the working formula. You might want to lose the variable and change to A1 referencing and just see if it matches perfectly. If you take the space out once it's in a cell it's a formula and if you have a problem, then I think the problem is in the formula.

 

[guilhermelemos]

It still doesn't work....

Here's what I want to do. For the cases where I have less than 10 SKU's in a given location, I want to copy the 10 parts with the highest ranking for that location.

So far I was trying to do just the top (ranking 1) SKU for the location.

The macro runs thru all locations, then where the number of SKU is less than 11, I go to the last row of the summary tab and copy the SKU on the bottom of the list with the respective location.

Please let me know if you have any further suggestions... thanks for trying to help...