Assign Formula with Macro

[graiggoriz]

Hello,

I have a formula which does a Vlookup in few cells. Some of those cells have a data validation based on a range from another worksheet.

When The datavalidation is used, the cell does not have the formula anymore.

I have created a macro to blank the cells but I would need those cells to contain the formula again when pushing on that button.

So to cut long story short, How do I assign a entire formula to a cell, knowing that that formula will be deleted?

Many Thanks,
Graig

[fredlo2010]

Using this line you will write "B1+1" in cell "A1" For instance

Please Login or Register  to view this content.

[graiggoriz]

What about that type of formula:
=IF(ISERROR(VLOOKUP($C$5;Sheet2!B:BU;3;FALSE));"";VLOOKUP($C$5;Sheet2!B:BU;3;FALSE))"

I first should select the variable and then assign the formula?
<sub>Sub TRYOU_Click()

Dim C6 As String

Range("C6").FormulaR1C1 = "=IF(ISERROR(VLOOKUP($C$5;BD_PROVEEDORES!B:BU;3;FALSE));"";VLOOKUP($C$5;BD_PROVEEDORES!B:BU;3;FALSE))"

End Sub</sub>

The above do not work, maybe I miss something then.

[fredlo2010]

Hi,

The best way to get a formula R1C1 is to turn on the recorder click the cell and press enter. Then your formula will be in the macro.

Thanks

[graiggoriz]

Correct I often forget that the record can really be helpful! thanks a lot.

I that formula I am take the lookup value as the C5 cell but when that cell is empty and the cell F5 is filled in I need to use that vlookup with the lookup value F5. I thought I should use the "OR" command but I don't manage to work that out on my own..

Could you please help me with that??

Here the command I am using only for the C5 lookup value:
=IF(ISERROR(VLOOKUP($C$5;BD_PROVEEDORES!B:BU;3;FALSE));"";VLOOKUP($C$5;BD_PROVEEDORES!B:BU;3;FALSE) )

Many thanks again.
Graig