convert excel formula to vba

Scott13

New Member
Joined
Dec 29, 2015
Messages
31
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
 

Some videos you may like

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
Joined
Feb 24, 2013
Messages
946
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
Joined
Aug 23, 2010
Messages
16,371
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.
 

Judoman

New Member
Joined
Nov 7, 2014
Messages
28
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
Joined
Feb 24, 2013
Messages
946
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
Joined
Nov 7, 2014
Messages
28
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
Joined
Feb 24, 2013
Messages
946
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
Joined
Nov 7, 2014
Messages
28
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
Joined
Feb 24, 2013
Messages
946
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
Joined
Nov 7, 2014
Messages
28
Sergio,

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

Watch MrExcel Video

Forum statistics

Threads
1,099,533
Messages
5,469,241
Members
406,644
Latest member
SavingsJoe

This Week's Hot Topics

Top