# Thread: convert excel formula to vba Thanks:  1 Post #5229924 (1) Likes:  1 Post #5229924 (1)

1. ## convert excel formula to vba

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

2. ## Re: convert excel formula to vba

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

3. ## Re: convert excel formula to vba

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.

4. ## Re: convert excel formula to vba

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","")))

5. ## Re: convert excel formula to vba

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

6. ## Re: convert excel formula to vba

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

7. ## Re: convert excel formula to vba

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

8. ## Re: convert excel formula to vba

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

Originally Posted by sergioMabres
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

9. ## Re: convert excel formula to vba

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

10. ## Re: convert excel formula to vba

Sergio,

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•