Great Board - Can this Else IFstatement be leaner

[Q]

I have been skulking anonymously around this board(s) for a few months now. I found it such an incredibly useful and helpful site that I decided to join a few weeks ago. Every member should take pride in this board; there are very few sites that are this "friendly" and useful. BRAVO MR. Excel and Juan.
Alright then all *** kissing aside here’s my question.
I have written an Else If statement for a command button on a userform I created. This form basically has 2 Input fields; one combo box allows the user to choose a variable, the variable being 1 to 90 and S1 to S44. The other field is a date, which is chosen off a calendar control.
Each variable from the combobox field is what is keyed on. For example if S33 is chosen the date field is copied to a sheet called "S33", a row is inserted on this sheet, and then the date is also copied to a sheet called "Premium". So each respective variable copies a date to its own personal sheet and also to one of three other sheets. (One of three sheets meaning that the numbers are broken up into different groups on different sheets. 1 through 55 on one sheet and S1 through S44 on another sheet and so on.) I'm sure there is a better way to write this but I can't seem to do it and have it work consistently. It seems I have become the King of IF statements. I have included a sample below.

If cboTankNumber.Value = "" Then
MsgBox "You must enter a Tank Number"
ElseIf cboTankNumber.Value = 1 Then
Worksheets("Green Fass").Range("D7") = calInput.Value
Worksheets("Tank1").Range("A22") = calInput.Value
Worksheets("Tank1").Rows("22:22").Insert Shift:=xlDown
frmInput.Hide
ElseIf cboTankNumber.Value = 2 Then
Worksheets("Green Fass").Range("F7") = calInput.Value
Worksheets("Tank2").Range("A22") = calInput.Value
Worksheets("Tank2").Rows("22:22").Insert Shift:=xlDown
frmInput.Hide
ElseIf cboTankNumber.Value = 3 Then
Worksheets("Green Fass").Range("H7") = calInput.Value
Worksheets("Tank3").Range("A22") = calInput.Value
Worksheets("Tank3").Rows("22:22").Insert Shift:=xlDown
frmInput.Hide

////////////

ElseIf cboTankNumber.Value = "S43" Then
Worksheets("Schoene").Range("V40") = calInput.Value
Worksheets("TankS43").Range("A22") = calInput.Value
Worksheets("TankS43").Rows("22:22").Insert Shift:=xlDown
frmInput.Hide
Else
cboTankNumber.Value = "S44"
Worksheets("Schoene").Range("X40") = calInput.Value
Worksheets("TankS44").Range("A22") = calInput.Value
Worksheets("TankS44").Rows("22:22").Insert Shift:=xlDown
frmInput.Hide
End If
.........and so on

Any help or suggestions will greatly be appreciated.

[Mark O'Brien]

I had a quick glance at this one.

1. Have a look at becoming the King of "Select Case". (examples on this board and in the Help file)

2. These lines of code are in every Else If section:

Worksheets("Tank1").Range("A22") = calInput.Value 

Worksheets("Tank1").Rows("22:22").Insert Shift:=xlDown 

If you do this for every condition, put this after your "End If" (when you finally hit it). This will clean up your code because you only need to use this code once.

HTH

[Mark O'Brien]

Also, with this part of the code in the first section of Else If's

ElseIf cboTankNumber.Value = 1 Then 

Worksheets("Green Fass").Range("D7") = calInput.Value 

you can probably replace every single Else If with this:

Worksheets("Green Fass").Range("C7").OffSet(0,cboTankNumber.Value) = calInput.Value

Because the column the "calInput.Value" goes in depends on the number in the combobox cboTankNumber. I'm assuming that this is a pattern.

(PS, glad you've stopped lurking. It took me a while before I started posting here as well)

[kkknie]

I see there have been some other posts, but since I spent a few minutes on mine, I'll add it for your enjoyment.

I assume sheet "Green Fass" has columns of data
beginning with D7 through some final value with
data every other column. You could do it all
in one statement if you get it right. My example
may not be exactly correct since I cannot see your
workbook, but it should be a good starting point.


dim strTemp as string
dim intTemp as integer

If cboTankNumber.Value = "" Then
MsgBox "You must enter a Tank Number"
Else

strTemp = cboTankNumber.Value
intTemp = cboTankNumber.ListIndex

Worksheets("Green Fass").Cells(7,4 + intTemp*2).value = calInput.Value
Worksheets("Tank" & strTemp).Range("A22").value = calInput.Value
Worksheets("Tank" & strTemp).Rows("22:22").Insert Shift:=xlDown
frmInput.Hide

End If

Note that the listindex of your combobox begins
at 0, so the cell you want to enter the new data
in is Cells(7, 4 + ListIndex*2). This corresponds
to:

Cells(7, 4+0*2) (D7)
Cells(7, 4+1*2) (F7)
Cells(7, 4+2*2) (G7)

etc.

Hope this helps,

K

P.S. Try it out on a practice sheet first!!!

[Q]

Thanks to you both for the quick responses. Mark I will look into Select Case suggestion. In your first response, point 2 I beleive you implied that the lines:
Worksheets("Tank1").Range("A22") = calInput.Value
Worksheets("Tank1").Rows("22:22").Insert Shift:=xlDown

are used through out the staetment. There is a slight variation in each of the Else IF statements. Where "Tank1" is replaced with "Tank2" and so on. So unfortunatly I have to use those lines in each Else If.

kkknie, Thanks and I will try it on a practice sheet. I always do it saves on the heartache.

[ This Message was edited by: Q on 2002-05-09 08:37 ]

[Mark O'Brien]

I did not see that.

How about using something like:

Worksheets("Tank" & cboTankNumber.Value).Range("A22") = calInput.Value 

to reference the worksheet.

EDIT:: Oops, sort of similar to what kkknie posted. Apologies kkknie.
_________________
[b] Mark O'Brien

[ This Message was edited by: Mark O'Brien on 2002-05-09 08:38 ]

[Q]

Yes. Both you and kkknie are on the same path. I'll take that as an omen, it must be a good one to follow.
Thanks to you both for helping cut down the fat.