How to create VBA from formula

[zhead]

I have been trying to a VBA code to place this formula in column BC. =RIGHT(CELL("filename",D2),LEN(CELL("filename",D2))-FIND("]",CELL("filename",D2))).
Here is what i am trying to use.

Please Login or Register  to view this content.

[jomili]

I think this may do what you're needing:

Please Login or Register  to view this content.

Selecting is frowned on in coding, as there's usually not a need to select.

For translating a formula to a VBA formula it helps to turn on the Macro recording, then type the formula on your sheet. When you turn off the macro recorder and look at the recorded code the formusa will be in VBA format.

[CAntosh]

Try this?

Please Login or Register  to view this content.

[jolivanes]

To expand on jomili's answer.
For an existing formula:
Select the cell that has the formula in it
Tools - Macro - Record or Developer - Record Macro
OK for Macro Name
F2
Enter
Stop Recording
If you check your code in the Macro Name you just OK'd, it'll have the formula in code.
Or
Select the cell with the formula and in the immediate window type: ?ActiveCell.FormulaR1C1

[zhead]

Jomili and Cantosh.
Thanks. Both codes work but only if i run it twice. the first run enters the formula in column BC and the second run enters the sheet name.
this is not a big problem for me/

jolivanes
Thanks for the advice

[jomili]

Not sure what you're meaning. Is your formula supposed to deliver the sheet name to the BC column? If that's what you're trying to do there are easier ways to do that than the formula method.

What behavior are you seeing? When you say "the first run enters the formula in column BC" are you saying that at the end of the procedure you're seeing formulas, but no formula results, or are you seeing results of the formulas, and if so is that the result you expected to see, or if not how does it differ from what you expect to see?

When you run a second time you say "the second run enters the sheet name." where does the sheet name get entered?

[zhead]

Yes the formula is supposed to deliver the sheet name in column BC.
When i run the macro it enters the formula in BC. If i run the macro again it replaces the formula with the sheet name in column BC

[jomili]

Ah, that makes it a lot easier. See if this does the trick:

Please Login or Register  to view this content.

If you still need to do that formatting as you like, add the line

Please Login or Register  to view this content.

before the "End Sub"

[zhead]

That worked great.
you are awesome

[zhead]

Thanks Jomili.
Your help is very much appreciated.

[jomili]

You're very welcome. Glad I could help.