Syntax Error Not R1C1 Problem. Manual formula work fine

[zuki01]

Hi all,

I cannot fathom why this returns syntax error. I've done all the fiddles and bits I know and it still gives it me. The formula works fine when i type it in but when i do it in a macro. Nope. Ive typed the macro, I've recorded the macro. Nothing. Its driving me up the wall !!!

Sub Macro19_Lookup_Pallets()
    Sheets("Rcom").Select
    Range("Q1").Select
    ActiveCell.FormulaR1C1 = "Pallets"
    Range("Q2").Select
    ActiveCell.FormulaR1C1 =
    "=IF(RC[-16]="""","""",TRIM(CONCATENATE(RC[-3],"" "",IFERROR(IFERROR(IFERROR(IFERROR(VLOOKUP(RC[-1],Hydra!C[5]:C[6],2,FALSE),VLOOKUP(RC[-2],Hydra!C[5]:C[6],2,FALSE)),VLOOKUP(CONCATENATE(RC[-15],"" "",TEXT(RC[-13],""DD/MM/YYYY""),"" "",RC[-15],"" "",""missing"","" "",TEXT(RC[-13],""DD/MM/YYYY"")),Hydra!C[5]:C[6],2,FALSE)),VLOOKUP(CONCATENATE(RC[-15],"" "",TEXT(RC[-10]" & _
        "/YYYY""),"" "",RC[-15],"" "",""missing"","" "",TEXT(RC[-10],""DD/MM/YYYY"")),Hydra!C[5]:C[6],2,FALSE)),""""))))"
    Range("Q2").Select
    Selection.AutoFill Destination:=Range("Q2:Q10131")
    Range("Q2:Q10131").Select
    Range("Q6").Select
    Sheets("Home").Select
End Sub

 

[Michael M]

What's the manual equivalent of the formula ??

 

[zuki01]

Hi Michael,

It's

=IF(A2="","",TRIM(CONCATENATE(N2," ",IFERROR(IFERROR(IFERROR(IFERROR(VLOOKUP(P2,Hydra!V:W,2,FALSE),VLOOKUP(O2,Hydra!V:W,2,FALSE)),VLOOKUP(CONCATENATE(B2," ",TEXT(D2,"DD/MM/YYYY")," ",B2," ","missing"," ",TEXT(D2,"DD/MM/YYYY")),Hydra!V:W,2,FALSE)),VLOOKUP(CONCATENATE(B2," ",TEXT(G2,"DD/MM/YYYY")," ",B2," ","missing"," ",TEXT(G2,"DD/MM/YYYY")),Hydra!V:W,2,FALSE)),""))))

 

[Michael M]

Try
Is the formula always going to row 10131, or is it the last row in "Q" ?

Sub Macro19_Lookup_Pallets()
Sheets("Rcom").Range("Q1").Value = "Pallets"
Sheets("Rcom").Range("Q2:Q10131").Formula = "=IF(A2="""","""",TRIM(CONCATENATE(N2,"" "",IFERROR(IFERROR(IFERROR(IFERROR(VLOOKUP(P2,Hydra!V:W,2,FALSE),VLOOKUP(O2,Hydra!V:W,2,FALSE)),VLOOKUP(CONCATENATE(B2,"" "",TEXT(D2,""DD/MM/YYYY""),"" "",B2,"" "",""missing"","" "",TEXT(D2,""DD/MM/YYYY"")),Hydra!V:W,2,FALSE)),VLOOKUP(CONCATENATE(B2,"" "",TEXT(G2,""DD/MM/YYYY""),"" "",B2,"" "",""missing"","" "",TEXT(G2,""DD/MM/YYYY"")),Hydra!V:W,2,FALSE)),""""))))"
Sheets("Home").Range("A1").Select
End Sub

 

[zuki01]

Hi Michael, sorry for the slow reply.

Its the last row in Q it goes down to. Which can be anything upto about 7000 - 9500 at the most.

 

[RoryA]

Based on how you've posted the code, you simply need a line continuation character:

Sub Macro19_Lookup_Pallets()
    Sheets("Rcom").Select
    Range("Q1").Select
    ActiveCell.FormulaR1C1 = "Pallets"
    Range("Q2").Select
    ActiveCell.FormulaR1C1 = _
    "=IF(RC[-16]="""","""",TRIM(CONCATENATE(RC[-3],"" "",IFERROR(IFERROR(IFERROR(IFERROR(VLOOKUP(RC[-1],Hydra!C[5]:C[6],2,FALSE),VLOOKUP(RC[-2],Hydra!C[5]:C[6],2,FALSE)),VLOOKUP(CONCATENATE(RC[-15],"" "",TEXT(RC[-13],""DD/MM/YYYY""),"" "",RC[-15],"" "",""missing"","" "",TEXT(RC[-13],""DD/MM/YYYY"")),Hydra!C[5]:C[6],2,FALSE)),VLOOKUP(CONCATENATE(RC[-15],"" "",TEXT(RC[-10]" & _
        "/YYYY""),"" "",RC[-15],"" "",""missing"","" "",TEXT(RC[-10],""DD/MM/YYYY"")),Hydra!C[5]:C[6],2,FALSE)),""""))))"
    Range("Q2").Select
    Selection.AutoFill Destination:=Range("Q2:Q10131")
    Range("Q2:Q10131").Select
    Range("Q6").Select
    Sheets("Home").Select
End Sub

Note - it's generally not advisable to return a space instead of "" in a formula.

 

[steve the fish]

I think you have been given the solution but for future reference the macro recorder will not work on long formulas. At each line break it misses out part of the formula. From memory about 8 or 9 characters. Note this part of your formula:

VLOOKUP(CONCATENATE(RC[-15],"" "",TEXT(RC[-10]" & _
"/YYYY"")

This effectively becomes:

VLOOKUP(CONCATENATE(RC[-15],"" "",TEXT(RC[-10]/YYYY"")

This isn't valid as hopefully you can see.