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
 

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
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,524
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

ADVERTISEMENT

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

ADVERTISEMENT

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,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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top