Multiple If-Then statements - a better way?

[TheShaunMichael]

Hello,

I have 9 separate macros setup, each designed to create a document in MS Word. What happens now, is I have a userform open with 9 buttons and I press the button for the type of document I need in MS Word.

What I'd like to do is create a series of If-Then statements to have Excel just automatically choose the correct Macro/Document. There are several variables in my Excel spreadsheet which dictate the document I need and I am thinking there is an easier way to write this code than by creating essentially several layers of If-Then statements (maybe select case???). I'm thinking about testing for the presence of each of these variables separately and assigning it a number. Then based on the total sum I can assign the MS Word template accordingly.

Here is the information i'm working with:

Dim EI As Worksheet
Set EI = Worksheets("Estimate Info")
Z = ActiveCell.Row

NewTotal = EI.Cells(Z, 13) - testing for a value greater than 0
FormTotal = EI.Cells(Z, 16) - testing for a value greater than 0
DTPTotal = EI.Cells(Z, 25) - testing for a value greater than 0
FuzzyTotal = EI.Cells(Z, 28) - testing for a value greater than 0
GoldTotal = EI.Cells(Z, 31) - testing for a value greater than 0
SourceLang = EI.Cells(Z, 18) - testing for the word "English"
TargetLang = EI.Cells(Z, 19) - testing for the word "English"

 

[shg]

One way:

    Dim myCase      As Long
 
    With Worksheets("Estimate Info").Rows(ActiveCell.Row)
        myCase = WorksheetFunction.SumProduct(Array(-(.Cells(13) > 0), _
                                                    -(.Cells(16) > 0), _
                                                    -(.Cells(18) = "English"), _
                                                    -(.Cells(19) = "English"), _
                                                    -(.Cells(25) > 0), _
                                                    -(.Cells(28) > 0), _
                                                    -(.Cells(31) > 0)), _
                                                    Array(64, 32, 16, 8, 4, 2, 1))
    End With

    Select Case myCase
        Case 127    ' all true
            '...
        Case 0  ' none true
    End Select