convert excel formula to vba
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: convert excel formula to vba

  1. #1
    New Member
    Join Date
    Dec 2015
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular sergioMabres's Avatar
    Join Date
    Feb 2013
    Location
    Córdoba, Argentina
    Posts
    920
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,228
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default 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. #4
    New Member
    Join Date
    Nov 2014
    Location
    Arkansas
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #5
    Board Regular sergioMabres's Avatar
    Join Date
    Feb 2013
    Location
    Córdoba, Argentina
    Posts
    920
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #6
    New Member
    Join Date
    Nov 2014
    Location
    Arkansas
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #7
    Board Regular sergioMabres's Avatar
    Join Date
    Feb 2013
    Location
    Córdoba, Argentina
    Posts
    920
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #8
    New Member
    Join Date
    Nov 2014
    Location
    Arkansas
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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



    Quote Originally Posted by sergioMabres View Post
    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. #9
    Board Regular sergioMabres's Avatar
    Join Date
    Feb 2013
    Location
    Córdoba, Argentina
    Posts
    920
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #10
    New Member
    Join Date
    Nov 2014
    Location
    Arkansas
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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