# 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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### 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

Replies
2
Views
88
Replies
1
Views
21
Replies
6
Views
193
Replies
2
Views
73
Replies
1
Views
128

### Forum statistics

1,129,917
Messages
5,638,981
Members
417,061
Latest member
thematulaak ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back