R/T 1004 - syntax error in code line

[jim may]

Can someone assist -- This code line has Got me -- Can somone point out a missing ", or 2?
Been trying now for 20 minutes, without success.

Range("T" & nxtbr & ":T" & Currlr).Formula = "=IF(S" & nxtbr & "=""N"",""000SI"",LEFT(A" & nxtbr & ",Application.Worksheetfunction.find("","",A & nxtbr))"

 

[dave3009]

Hi Jim

I think it needs to end

Range("T" & nxtbr & ":T" & Currlr).Formula = "=IF(S" & nxtbr & "=""N"",""000SI"",LEFT(A" & nxtbr & ",Application.Worksheetfunction.find("","",A" & nxtbr & "))"

All the best


Dave

 

[Andrew Poulsom]

Hello Jim

Why Application.WorksheetFunction.Find, not just FIND? What would your formula be if you entered it in a cell? Turning on the macro recorder and pressing F2 Enter on an existing formula will give you the syntax, albeit in R1C1 reference style.

 

[jim may]

Andrew,
This is another AMAZING (hidden) Function of EXCEL - I didn't know one could do this,
WOW - Thanks.

Your right -- the code comes off in R1C1 syntax (which I rarely use).

My before code line began with
Range("T" & nxtbr & ":T" & Currlr).Formula =

If I paste the R1C1 code produced to the right of the above - Do I need to edit my .Formula = to .FormulaR1C1 = ?

I forgot to do that, but it seems to work without making this edit change.

Thanks again,

Jim

When I append it into my existing code do I at

 

[Andrew Poulsom]

ADVERTISEMENT

You should use Formula for A1 references and FormulaR1C1 references.

 

[jim may]

You should use .Formula for A1 references and .FormulaR1C1 FOR R1C1 references?

Can you confirm!

 

[Andrew Poulsom]

You should use .Formula for A1 references and .FormulaR1C1 FOR R1C1 references?

Can you confirm!

Yes, sorry for the omission.

 

[jim may]

No Problem, Thanks very much again