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.
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.