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

 

[hallingh]

Yes, there will only be one possible match. I've changed it to use your code, but it is not working. Maybe I've done something wrong??? Here is what I have now:

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)
    y = Rows(12).Find(rsp).Column
    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

EndSub:
Me.MonthBox.Value = ""
Me.YearBox.Value = ""
 

End Sub

Did I omit something that still needs to be present?

 

[VoG]

Perhaps it should just be

    y = Rows(12).Find(rsp).Column
        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

 

[hallingh]

I thought about this, but won't this change a cell I don't want it to if row 12 doesn't contain a matching header?

 

[VoG]

Then maybe like this

Dim Found As Range
Set Found = Rows(12).Find(rsp)
If Not Found Is Nothing Then
    y = Found.Column
    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
'No match - do something else
End If

 

[hallingh]

I had it display a msgbox if there was no match, but with this code it tells me there was no match found regardless of what I enter. Any idea why it wouldn't be recognizing the match?

Also, with my original For loop code, I realized that when I entered Oct-10 into the userform to have it add 400,000 to the cell below that header, it was actually changing every cell that was under a header to be equal to 400,000. Any idea why that would be happening?

 

[VoG]

Maybe try

Set Found = Rows(12).Find(what:=rsp, LookIn:=xlValues, lookat:=xlWhole)

 

[hallingh]

Aha! You are all that is man. Thanks a ton for the help; I am very appreciative.

Thanks!

Hank