Nested If Statements

[hallingh]

I have a bunch of nested If statements (vba, not native excel) that are giving me some trouble. the spot that is giving me a hard time is when I need to add an Else If. Here is my code:

    For y = ActiveSheet.Cells(lngHeaderRow, 2).End(xlToRight).Column To 1 Step -1
        If ActiveSheet.Cells(lngHeaderRow, y).Text = rsp Then
            If y = 2 Then
                ActiveSheet.Cells(13, y).Value = myNum + ActiveSheet.Cells(13, y).Value
                End If
           [B] Else If ActiveSheet.Cells(13, y).Value > ActiveSheet.Cells(13, (y - 1)).Value Then
[/B]                ActiveSheet.Cells(13, y).Value = myNum + ActiveSheet.Cells(13, y).Value
                End If
                Else
                    ActiveSheet.Cells(13, y).Value = myNum + ActiveSheet.Cells(13, (y - 1)).Value
    Next y

The code that is bold, obviously, is the part I'm having trouble with. It doesn't like the way it is written, as it gives me an error before I even run the code and immediately after I enter it.

The error says must be first statement on the line. Does anyone have any ideas how I can write this code so excel will accept it? Thanks a lot for the looks and any help you can give me.

Hank

 

[VoG]

Try ElseIf (all one word).

 

[hallingh]

Haha, that's simpler than I thought it would be. Thank you, that solved that problem. Now, though, I'm getting an "Else without If" error when I run the code. Here is my code again. The first highlighted row is the portion that should have been run when I ran my code. The second highlighted portion (the Else statement) is where it is telling me there is an "else without if" error.

  For y = ActiveSheet.Cells(lngHeaderRow, 2).End(xlToRight).Column To 1 Step -1
        If ActiveSheet.Cells(lngHeaderRow, y).Text = rsp Then
            [B]If y = 2 Then
                ActiveSheet.Cells(13, y).Value = myNum + ActiveSheet.Cells(13, y).Value
[/B]                End If
            ElseIf ActiveSheet.Cells(13, y).Value > ActiveSheet.Cells(13, (y - 1)).Value Then
                ActiveSheet.Cells(13, y).Value = myNum + ActiveSheet.Cells(13, y).Value
                End If
               [B] Else
[/B]                    ActiveSheet.Cells(13, y).Value = myNum + ActiveSheet.Cells(13, (y - 1)).Value
    Next y

Any ideas? Thanks again for the help.

Hank

 

[VoG]

Perhaps it should be

For y = ActiveSheet.Cells(lngHeaderRow, 2).End(xlToRight).Column To 1 Step -1
    If ActiveSheet.Cells(lngHeaderRow, y).Text = rsp Then
        If y = 2 Then
            ActiveSheet.Cells(13, y).Value = myNum + ActiveSheet.Cells(13, y).Value
        ElseIf ActiveSheet.Cells(13, y).Value > ActiveSheet.Cells(13, (y - 1)).Value Then
            ActiveSheet.Cells(13, y).Value = myNum + ActiveSheet.Cells(13, y).Value
        End If
    Else
        ActiveSheet.Cells(13, y).Value = myNum + ActiveSheet.Cells(13, (y - 1)).Value
    End If
Next y

 

[hallingh]

OK, that got rid of the error, but now I have a weird problem. What this code is doing is promptiong a userform that allows the user to select a month and a year. It then puts that into the format "Nov-10," "Jan-11" etc. There are heards in row 12 of my sheet that contain a month and year in the same format. When the user clicks submit, my code looks through the headers, and if it finds the correct header it runs that code. If the header is in column B, it simply adds a figure from a certain cell to the number already under the header in column B. If it is not, then it adds it to the cell directly to its left UNLESS that cell is a smaller number than the cell under the header, when it will add it to the cell under the header.

When I ran this code, I entered Oct-10 into the userform three times in a row. The first time it altered the cell under Oct-10 correctly. However, the second time I ran it it altered the cell under Nov-10, and the third time I entered it it altered the cell under Dec-10. Do you have any clue why this could be happening? Here is my entire code:

Private Sub OKSubmit_Click()
Dim myNum As Long
Dim CellRow As Long
EnterDate.Hide
CellRow = ActiveSheet.Range("S6").Value
myNum = ActiveSheet.Range("E" & CellRow).Value
Dim LCol As Long
Dim rsp As String
Dim y As Long
Const lngHeaderRow = 12
On Error GoTo EndSub
    LCol = Cells(lngHeaderRow, 2).End(xlToRight).Column
    rsp = Me.MonthBox.Value & "-" & Right(Me.YearBox.Value, 2)
    For y = ActiveSheet.Cells(lngHeaderRow, 2).End(xlToRight).Column To 1 Step -1
    If ActiveSheet.Cells(lngHeaderRow, y).Text = rsp Then
        If y = 2 Then
            ActiveSheet.Cells(13, y).Value = myNum + ActiveSheet.Cells(13, y).Value
        ElseIf ActiveSheet.Cells(13, y).Value > ActiveSheet.Cells(13, (y - 1)).Value Then
            ActiveSheet.Cells(13, y).Value = myNum + ActiveSheet.Cells(13, y).Value
        End If
    Else
        ActiveSheet.Cells(13, y).Value = myNum + ActiveSheet.Cells(13, (y - 1)).Value
    End If
Next y
EndSub:
Me.MonthBox.Value = ""
Me.YearBox.Value = ""

Thanks for the look. I really don't know what the issue could be here?

Hank

 

[hallingh]

You might need some more info, I've realized. Nov-10 is in column C. The original cell value is "=B13" with B13 being the directly to its left. I first ran the userform on "Oct-10" to set it equal to 400000. This worked perfectly, and the cell under "Now-10" changed to 400000 as well, since it is set to be equal to that cell. I then ran the userform so it should add 400000 to the cell under "Nov-10" but for some reason that cell remined at 400000 and the cell directly to its right changed to 800000. Not really sure why this would happen, and I am even less sure of how to fix it! Thanks again for the help.

Hank

 

[VoG]

I think that the issue is that you need to exit the loop when you find a match. Perhaps

For y = ActiveSheet.Cells(lngHeaderRow, 2).End(xlToRight).Column To 1 Step -1
    If ActiveSheet.Cells(lngHeaderRow, y).Text = rsp Then
        If y = 2 Then
            ActiveSheet.Cells(13, y).Value = myNum + ActiveSheet.Cells(13, y).Value
            Exit For
        ElseIf ActiveSheet.Cells(13, y).Value > ActiveSheet.Cells(13, (y - 1)).Value Then
            ActiveSheet.Cells(13, y).Value = myNum + ActiveSheet.Cells(13, y).Value
            Exit For
        End If
    Else
        ActiveSheet.Cells(13, y).Value = myNum + ActiveSheet.Cells(13, (y - 1)).Value
        Exit For
    End If
Next y

However, you really don't need a loop at all

y = Rows(12).Find(rsp).Column

 

[hallingh]

So you mean I can replace my entire loop with that and it will still work???

 

[hallingh]

What will happen if the user selects a date that isn't in the headers? This is for a budget calculator, so the userform contains up to the year 2020 but the current headers only go up to 2011. So, it is very possible for the user to accidentally enter a date that isn't there.

 

[VoG]

I think so, if you are only expecting a single match.