Link to home
Create AccountLog in
Avatar of morinia
moriniaFlag for United States of America

asked on

Assign multiple values in VB in Excel - Excel 2007

I have a VB module that is currently assigning a value in a case statement :

            Case "NJ"
            lobCol = "H"
            lobProd = "NJ"

Is it possible to assign multiple values for example if I wanted to assign, "NY", "NJ"  and "CT"
to  lobProd>
Avatar of spattewar
spattewar

you can assign only one value to a variable. If you want multiple value then you can define a array and populate it with the values or assign all the values as a comma separated string and then split the values and use it.

for e.g.

dim alobprod() as string
dim lobprod as string
lobprod = "NJ,NY,CT"

alobprod = Split(lobprod,",")

hope this helps.
Not sure what you mean.  You can do something this:

Select Case State
    Case "NJ", "NY, "CT"
        lobCol = "H"
        lobProd = "NJ"
    Case "PA", "MD", "DE", "VA"
        lobCol = "I"
        lobProd = "MD"
    Case "MA"
        lobCol = "J"
        lobProd = "VT"
    Case Else
        lobCol = "K"
        lobProd = "CA"
End Select

Open in new window

What do you wan to perform?
Avatar of morinia

ASKER

I have an in-house function that is doing a sumif.  So far it has worked fine it is only looking for one value.  I now have a request where multiple values need to be added together to come up with the total.

getTot = WorksheetFunction.SumIfs(rngAuths, _
                                             rngEvent, thisEvent, _
                                             rngMonth, colMonth, _
                                             rngLOB, lobProd, _
                                             rngPORG, pOrg)

The problem is lobProd can not be equal 10 different plans.  If when doing the sumif if rngPORG is equal to any of the 10 plans I want the value counted.  I  don't know if this is possible with this function.
If not, perhaps someone can show me how to code another function to do this.
ASKER CERTIFIED SOLUTION
Avatar of spattewar
spattewar

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of morinia

ASKER

spattewar,

I am trying it now!!  Will let you know