Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Lounger
    Join Date
    Sep 2013
    Posts
    34
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Convert Excel formulas in VBA code

    Hello,
    I`ve tried unnseccessfully in last week getting help and elsewhere in writing a Vba code for a situation which I have to complet monthly. I have two kind of income ,taxable and net and I have to fiind an Minimum; Maximum and and Average of those like in attached workbook for each type of function,rank,coeff1 and coeff2.
    I have some Excel formulas for those ”case” but i would liked an vba code for this keeping in mind that i have over 1500 employers and I am using Excel 2003.
    If someones cant help me to convert those excel formulas in an Vba code i would be thankfully.
    Thanks in advance!
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I'm not sure why you need VB code.

    Your calcs seem to work, although I did the following array formula in column F, for example:

    =IF($E3>1,MIN(IF(0<(Foaie1!$F$2:$F$384=$A3)*(Foaie 1!$C$2:$C$384=$B3)*(Foaie1!$D$2:$D$384=$C3)*(Foaie 1!$E$2:$E$384=$D3),Foaie1!$G$2:$G$384)),"")

    In column G:

    =IF($E3>1,MAX((Foaie1!$F$2:$F$384=$A3)*(Foaie1!$C$ 2:$C$384=$B3)*(Foaie1!$D$2:$D$384=$C3)*(Foaie1!$E$ 2:$E$384=$D3)*Foaie1!$G$2:$G$384),"")

    and the average column H:

    =IF($E3>1,AVERAGE(IF(0<(Foaie1!$F$2:$F$384=$A3)*(F oaie1!$C$2:$C$384=$B3)*(Foaie1!$D$2:$D$384=$C3)*(F oaie1!$E$2:$E$384=$D3),Foaie1!$G$2:$G$384)),"")

    Filling these down seems to work for all of the rows.

  3. #3
    Lounger
    Join Date
    Sep 2013
    Posts
    34
    Thanks
    16
    Thanked 0 Times in 0 Posts
    I want to do one kind of "stadardization" and I have a lot of situaation likr this and the Vba code I want to adapted it on my future situations!!! My boss vision. I hope he`s not reading this kind of forums! :P. So if anyone cant helpme whit this piece of code i would be thankfully.
    Have a nice day!!

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    It sounds like you want a pivot table, which requires not formulas or VBA. I have attached the pivot table for the dataset, you can format as desired and modify as needed.

    Steve
    Attached Files Attached Files

  5. #5
    Lounger
    Join Date
    Sep 2013
    Posts
    34
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Thanks u very much sdckapr it`s really usefull and it`s a great ideea. I don`t want to look ungratefully and displeased but I search all over the internet an VBA code for Min ,Max and Average based on multiple 4 criteria,(conditoins ) and I didn`t found it any "ideea". From my point of view this "kind" of VBA code,based on multiples criterias it`s easily handled and adaptable on my futures situation. Again, I don`t want to look impolite!! So if someone can help me with this VBA code for being a "source of inspiration" for my future projects.
    King regards!

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    afm,

    Try looking at the builtin database functions {DAVERAGE, DMAX, DMIN, etc.}. You can set as many criteria as necessary. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Lounger
    Join Date
    Sep 2013
    Posts
    34
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Mr. RetiredGeek I`m not such and "expert" , I know to adapt an VBA cod on different range, columns... but not building them!! King regards!

  8. #8
    Lounger
    Join Date
    Sep 2013
    Posts
    34
    Thanks
    16
    Thanked 0 Times in 0 Posts
    I do not want to be pushy but still hope in that VBA code

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    What exactly do you want the VBA code to do?
    Should it create the output?
    Do you want to create a function which calculates Avg, min, max with 4 criteria? If you want the function, are the criteria are the ranges for lookup to be hard coded are are they going to be entered as parameters in the function? Should the function include the looking for >1 that the formula has? Do you want 1 function with a parameter to choose min, max, average or 3 separate functions?

    Note: the reason you don't generally see these type of functions in VB, is that the formulas are so much easier to use as they don't require coding (they are already coded) and they are much more efficient especially if you are going to do thousands of calculations.

    Steve
    PS also if you want the 5 ranges as parameters to enter, do you want the 5 ranges as parameters or do you want 1 range and list the columns of the range as a parameter?
    Last edited by sdckapr; 2013-09-18 at 19:40.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Here is a simple method, let me know if it does what you need. In VB add the function:
    Code:
    Option Explicit
    Function myFunc(sType As String, sCol As String)
      Dim lRow As Long
      Dim sComp As String
    
      lRow = Application.Caller.Row
      sComp = ">"
      If LCase(sType) = "average" Then sComp = ">="
    
      myFunc = Evaluate("=IF($E" & lRow & sComp & "1," & sType & "(IF(Foaie1!$F$2:$F$384=$A" _
        & lRow & ",IF(Foaie1!$C$2:$C$384=$B" & lRow & ",IF(Foaie1!$D$2:$D$384=$C" & lRow & _
        ",IF(Foaie1!$E$2:$E$384=$D" & lRow & ",Foaie1!$" & sCol & "$2:$" & sCol & "$384))))),"""")")
    End Function
    Then in the 'This is what i want!"F3 enter:
    =myfunc(F$2, "g")
    copy F3 to G3 and H3

    Then in the 'This is what i want!"I3 enter:
    =myfunc(I$2, "h")
    copy i3 to j3 and k3

    Then copy f3:K3 down the rows ...

    Steve

  11. #11
    Lounger
    Join Date
    Sep 2013
    Posts
    34
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Thanks very much sdckapr for this code...it`s a begining for me....
    But I would like the function include the looking for>1 ( If ActiveCell.Value > 1 Then , Select Case ActiveCell Case MaxIf Case MinIf Case AverageIf , End Select , ActiveCell.Offset(x, y).Select ,Loop ; If ActiveCell.Value = 1 Select Case Average something like this from my Knowleage) I hope I was enough clear and you undestood me.
    Thanks Again very very much!!!!!!!!!!!

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Some comments:
    The function does include looking for the >1.
    The case structure is not needed in the example function, nor is a loop neccessary. The code does the minifs, maxifs, averageifs directly. Excel is doing the "heavy lifting". A function in XL would not be based on the activecell anyway (the Appplication.Caller determines what cell called the function).

    I still am at a loss to understand what you want the code to accomplish. Do you want to go from what you seem to have (the Foaie1 sheet) to get to the "This is what i want" sheet. As I demonstrated earlier, the pivot table is 95%+ of what you want. I would not use that structure of pseudocode, I would have the code create the pivot table then modify that to get the remaining 5%.

    Perhaps if you detailed exactly what you want the code to accomplish. Do you want a function or a routine to do a task? Let me know the goals.

    Steve

  13. #13
    Lounger
    Join Date
    Sep 2013
    Posts
    34
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Routine.xls Is about a routine to do the task.
    Basically I had a part of code from a previous situation and asked your help to complete the code. I attached it now indicating that base sheet is Rap0. There are practical 2 VBA. "Your part of vba code" I would have wanted to adapt it to Rap0 to run and complete the situation automatically. Excuse "garbage" from code I did not stay to translate them. Steve I said where I got stuck in code and I basically I wanted to write a sequel to code to automate it as under GenRap2 () (but whit that 4 criteria)

  14. #14
    Lounger
    Join Date
    Sep 2013
    Posts
    34
    Thanks
    16
    Thanked 0 Times in 0 Posts
    And the atached!
    Attached Files Attached Files

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    What Module, what procedure, and what lines of code are you having problems with?

    [Again I point out that it seems to me you are trying to create your own routine which does what the excel built-in Pivot table already does, Why not start with the pivot table? You may be able to modfy that data with no code, or just create the code to modify the pivot table. Why recreate the wheel?]

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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