VBA formula not working

[texasalynn]

I've been staring at this for an hour and can't find the problem.

Set rngFound = Cells.Find(What:=MyData, After:=ActiveCell, _
    LookAt:=xlPart, MatchCase:=True)
    f = rngFound.Address
    df = "'" & ShName & "'!" & f
Worksheets(ShName2).Activate
    Range("A2").FormulaR1C1 = _
        "=RIGHT(" & df & ")"

The line Range("A2")... gets an error application-defined or object-defined error. What am I missing here?

 

[Von Pookie]

You're not entering a R1C1 formula, yet you are using the FormulaR1C1 property.

Change FormulaR1C1 to simply Formula.

Edit: You also should not need to select the sheet. This should enter the formula just the same:

Worksheets(ShName2).Range("A2").Formula = "=RIGHT(" & df & ")"

 

[texasalynn]

I knew that! Duh - knew it had to be staring at me. Thanks

 

[Norie]

Aren't you missing the 2nd argument for RIGHT?

 

[texasalynn]

could be, but the real problem was what Von Pookie suggested. I didn't post my full code, only a portion so I may have left it off. But all is working correctly now. Thanks for the pointer

 

[Von Pookie]

Aren't you missing the 2nd argument for RIGHT?

Technically not, at least if the object is to return only one character. Anymore than that, then yes, the 2nd argument is missing.

 

[Norie]

Von Pookie



I just realised that when I checked back.