# Thread: Convert Excel formulas in VBA code

1. ## 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.

2. 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. 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. 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

5. 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. afm,

Try looking at the builtin database functions {DAVERAGE, DMAX, DMIN, etc.}. You can set as many criteria as necessary. HTH

7. 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. I do not want to be pushy but still hope in that VBA code

9. 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?

10. 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. 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!!!!!!!!!!!

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. 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. And the atached!

15. 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 Last

#### Posting Permissions

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