convert excel formula to vba

[Scott13]

SO I have the following formula:

=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

 

[sergioMabres]

E27 works too the formula is relative, but, you have to be in E27 it works on the selected cell

 

[Judoman]

E27 works too the formula is relative, but, you have to be in E27 it works on the selected cell

Having to be in a specific cell is not what I was after. What I would like is when A7 has X and E19 has X then E27 would automatically have an X. Second part is if E7 has X and E19 has X then E27 would again have autofill X.

For me, simply adding the Xs to A7 and E19 does nothing, even if I have E27 selected. If I run macro1 from Macros list I get a Compile error: Syntax error

 

[sergioMabres]

If you want to be in E27 change the macro to

Sub Macro1()
'
' Macro4 Macro
'

'
    Range("E27").Select
    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"","""")))"
    Range("E28").Select
End Sub

This works only for E27
Cheers
Sergio

 

[Judoman]

OK, this one works perfectly for what I need. I really appreciate all your help Sergio. I never would have figured this out myself.