# convert excel formula to vba

#### Scott13

##### New Member
SO I have the following formula:

Code:
``=IFERROR(IF(AND(B\$8>=(VLOOKUP(\$A9,Leave_Dates,2,FALSE)), (B\$8<=(VLOOKUP(\$A9,Leave_Dates,3,FALSE))),WEEKDAY(B\$8,1)<6), "L", ""), "")``
I need to somehow convert this to VBA to perform the same thing.

"Leave_Dates" is a named table
B8:AF8 contains dates of the month ( 1 jan thru 31 jan)
A9:A16 contains names of employees

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### sergioMabres

##### Well-known Member
HI Scott,
Well this is a real pickle, the way I see your problem is whether you:
1. Use the "WorksheetFunction" object like in X = WorksheetFunction.VLookup("A9", "Leave_Dates", 2, False)
2. You rewrite VLOOKUP, IFERROR, IF, and WEEKDAY in VBA, what would be an amazing job to do, it will take you about 4 month of work to do that.
Cheers
Sergio

#### Marcelo Branco

##### MrExcel MVP
In Excel

Turn-on the Macro Recorder
Put the formula in the proper cell (if isn't there)
press Enter
Turn-off the Macro Recorder

Take a look at the code

M.

• sergioMabres

#### Judoman

##### New Member
I have a formula in excel spreadsheet that adds an X when specific criteria are met (Xs and/or Model names in certain other cells). Problem is if X is manually deleted it also deletes formula. So I would like to move formula to VB. But I have no idea how to write it in VBA. Here is current formula that is in cell E27:
=IF(AND(A7="X",E19="X"),"X",IF(AND(E7="X",E6= "F9EX Ey II", E19="X"),"X",IF(AND(E7="X",E6="F9DX EY II", E19="X"),"X","")))

#### sergioMabres

##### Well-known Member
Hi Judo try this
Code:
``````    ActiveCell.FormulaR1C1 = _
"=IF(AND(R[-20]C[-4]=""X"",R[-8]C=""X""),""X"",IF(AND(R[-20]C=""X"",R[-21]C= ""F9EX Ey II"", R[-8]C=""X""),""X"",IF(AND(R[-20]C=""X"",R[-21]C=""F9DX EY II"", R[-8]C=""X""),""X"","""")))"``````
Cheers
Sergio

#### Judoman

##### New Member
Sergio,

I put the formula in a module, also tried putting it directly in sheet1. Not sure at all if heading of Private Sub.... is correct. Get a Compile error:Ambiguous name detected:Worksheet_SelectionChange

I'm assuming Active Cell means where I want X to automatically be added. Here is what i got, it's all in red in the module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

ActiveCell.FormulaR1C1 = E27 _
"=IF(AND(R[-20]C[-4]=""X"",R[-8]C=""X""),""X"",
IF(AND(R[-20]C=""X"",R[-21]C= ""F9EX Ey II"",R[-8]C=""X""),""X"",
IF(AND(R[-20]C=""X"",R[-21]C=""F9DX EY II"", R[-8]C=""X""),""X"","""")))"
End If
End Sub

#### sergioMabres

##### Well-known Member
Use like this in a module, run the macro by hand NOT on change
Code:
``````Sub Macro1()
'
' Macro1 Macro
'

'
ActiveCell.FormulaR1C1 = _
"=IF(AND(R[-20]C[-4]=""X"",R[-8]C=""X""),""X"",IF(AND(R[-20]C=""X"",R[-21]C= ""F9EX Ey II"", R[-8]C=""X""),""X"",IF(AND(R[-20]C=""X"",R[-21]C=""F9DX EY II"", R[-8]C=""X""),""X"","""")))"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub``````
Cheers
Sergio

#### Judoman

##### New Member
Put formula in module and did not get any error message or red text. Sadly it still does not work. If I run Macro1 from Excel Macro selection I get Run-time error '1004' Application -defined or object-defined error. Selecting debug highlights formula in yellow

Use like this in a module, run the macro by hand NOT on change
Code:
``````Sub Macro1()
'
' Macro1 Macro
'

'
ActiveCell.FormulaR1C1 = _
"=IF(AND(R[-20]C[-4]=""X"",R[-8]C=""X""),""X"",IF(AND(R[-20]C=""X"",R[-21]C= ""F9EX Ey II"", R[-8]C=""X""),""X"",IF(AND(R[-20]C=""X"",R[-21]C=""F9DX EY II"", R[-8]C=""X""),""X"","""")))"
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub``````
Cheers
Sergio

#### sergioMabres

##### Well-known Member
I don't know what are you doing to get a ERROR 1004, it works fine when I run with cell G10 selected in an empty Sheet, I cannot know what is going on in your environment, sorry, this is the transcription of your formula into vba.
Cheers
Sergio

#### Judoman

##### New Member
Sergio,

What do you mean by G10 selected? In my formula and yours the cell to get the X to autofill is E27