Switch statement or sumif?

[pwillia]

Hi guys,


I have a question regarding the use of VBA switch statements, any help would be very appreciated, I'm still getting to know the language.


Essentially, I have a column of integer values which I would like to iterate through. The integers in the column vary from 12 to -12 with the exception of 0, so 24 possible integer values here.
For each possible value, I would like the activecell.offset(0,7) value added to a variable.
So if I iterate from cell A1 to cell A10, each time a value of "1" appears, I would like the cell 7 spaces to the right of the "1" cell added to a "total" variable for "1" e.g.: "totalOne".
The same applies for every value within my range. So if there are three instances of the value "8" between A1 and A10, then each value 7 cells right of those "8" values will be added to a total value for the value 8.


I started to use:


select case range("u4:u150"). value


case 1
Dim value1 = value1 + ActiveCell.offset(0, 7).Value


However, at this point I realised that a switch statement stores the range I have given it as an array, so my 'case 1' cannot work as it is trying to compare an integer with an array.
Finally, if it stores an array, surely it wouldn't be able to get cell.offset values as it would have no references to those locations given that the array is just a group of integers, not actual cell references? Will I have to use lots of "for each" statements? hope not!

Finally, somebody simply told me to look at 'sumif' statements instead but i'm still no clearer.



Thanks guys for your time. Any questions, please ask.

 

[Norie]

What is Range("U4:U150")?

Is it the list of variables you want to loop through?

If it is why not loop through it?

For Each cell In Range("U4:U150")

        Select Case cell.Value

              Case 1
                   value1 = value1 + cell.Offset(0,7).Value

              Case 2 

                  ' etc

        End Select
Next cell

Mind you it might be easier to use SUMIFs.

If you had a list of the the values 12 to -12 (excluding 0) in A1:A24 you could use a formula like this in B1 which you would copy down to B24.

=SUMIF(U4:U150, A1, AB4:AB150)

 

[pwillia]

Yes that is the list of variables I want to loop through.
Wow, such a simple and stupid mistake by me; I assumed the switch statement would iterate by itself!

Thank you for your explanation of the SUMIF statement, it now makes complete sense.
I think the SUMIF solution will be more graceful for my application as you have pointed out.

Thank you again for your post, very helpful, prompt reply and cool puffin!

 

[Norie]

Thank you.

If you need any further help post back.

 

[pwillia]

OK, I think I am along the right lines but I'm not getting any results yet.
No errors, but no changes either.

        Range("af3") = SumIf("u4:u150", "ae3", "ab4:ab100")

I shouldn't have to wrap this in a 'for each' statement should I?
The range to sum "ab4:ab100" does contain some empty cells, could this be a factor?
I assumed it will skim past them or add a nil value which will not alter the result.

 

[Norie]

Not sure what you are trying to do there but if you want to enter the SUMIF formula in AF3:

Range("af3").Formula =  "=SumIf(u4:u150, ae3, ab4:ab100)"

If you wanted to put the formula in the cells below AF3, assuming you have the values 12 to -12 in AE3-AE26:

Range("af3:af26").Formula =  "=SumIf($U$4:$u$150, ae3, $ab$4:$ab$100)"

That's all you need, you don't need to change anything in the formula, AE3 will automatically increment just as if you copied the formula down manually.

PS I'm assuming you do have a list of the values.

 

[pwillia]

ohhh.... I was just about to reply with what I thought was the best way of doing it, below:

 Range("$af$3") = WorksheetFunction.SumIf(Range("$u$4:$u$150"), Range("$ae$3"), Range("$ab$4:$ab$100"))

except of course this needs repeating 23 more times!

Thank you for your help again!!!

P.S. what difference does the 'formula' word give?

 

[Norie]

It tells VBA that you are putting a formula instead of a value in the cell

 

[pwillia]

Brilliant, thank you for all your help. Clearly I still have a lot to learn!

Thanks.