Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Great Board - Can this Else IFstatement be leaner

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    20 Minutes outside Manhattan
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)


  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    Greenwood, SC
    Posts
    677
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Location
    20 Minutes outside Manhattan
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    Board Regular
    Join Date
    Apr 2002
    Location
    20 Minutes outside Manhattan
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •