For each in range if then

[Belleke]

I am looking for a piece of VBA to sum a range if it meets a criteria and populate a userform textbox.
In range F19:F36 are 2 values 6% or 21% (taxrates)
What I want, if the value = 6 % then sum the values in range G19:G36 offset(0,1)
The same for the 21% value.
So that i have 2 sums to populate 2 userform textboxes.
Hope it is clear.

 

[Marc L]

Without an attachment, I can clearly advise the best and easy way
is the Excel way even via VBA : an easy SUMIF worksheet function !

Via Evaluate method or Application.SumIf statement …
​

 

[Belleke]

I have this

T_11.Value = Excel.WorksheetFunction.Sum(Sheets("Factuur").Range("G19:G36"))

But I want to add If Sheets("Factuur").Range("F19:F36") is 6% then
I feel stupid because I did it before.
Little example

 

[Marc L]

If you need the result within a cell, you do not ever need a code …

Cell G39 formula : =SUMIF($F$19:$F$36,F39,$G$19:$G$36)

Just reading Excel inner help …
​

 

[Belleke]

Thanks Marc,
You pointed me in the right direction with the sumif.

Set rng = ws.Range("F19:F36")
Set sum_range = ws.Range("G19:G36")
sumif_criterion = "6%"
T_12.Value = Application.WorksheetFunction.SumIf(rng, sumif_criterion, sum_range)

My boss does not want formulas.
Solved.

 

[Marc L]

Avoid WorksheetFunction as Application is enough (see post #2) …

Respecting TEBV rule, another way
(a must read in VBA inner help : Evaluate !) :​

Sub Demo0()
    MsgBox [SUMIF($F$19:$F$36,F39,$G$19:$G$36)]
End Sub

 

[Belleke]

Thanks Marc, I'll give it a try.

 

[klimbo13]

I have spent a few hours working out how to do this which is why im posting it here now... If you want to return different values in a cell based on which range the value entered in another cell comes under then I have worked out how to do it!! (bear in mind that this is specific to my spreadsheet and was for calculating prices i.e. 0.99 = £0.99)