End if without block If error

[jme851]

I am using Excel 2010, I receivean error message "end if without block if" I've been working on this forseveral days and I have been un-successful. Can someone please tell me what Iam missing? The code is to look at what is in combox1 and based off what is selected by the user, it will locate that tab and put in the information from the userform. Thank you inadvance <o></o>

<code> Private SubCommandButton1_Click()<o></o>

"S:\location of file.xlsx"<o></o>

<o> </o>

'UPDATES WORKBOOK<o></o>

<o> </o>

Application.ScreenUpdating = False<o></o>

<o> </o>

Workbooks.Open "S:\locationof file.xlsx"<o></o>

<o> </o>

Set wb2 = ActiveWorkbook<o></o>

<o> </o>

If ComboBox1.Value = "Name 1" Then<o></o>

rowCount = Worksheets("sheet1").Range("A1").CurrentRegion.Rows.Count<o></o>

With Worksheets("sheet").Range("A1")<o></o>

.Offset(rowCount, 0).Value =Me.ComboBox1.Value 'NAME<o></o>

.Offset(rowCount, 1).Value =Me.ComboBox2.Value 'TASK<o></o>

.Offset(rowCount, 2).Value =Format(Now(), "MM/DD/YYYY") 'DATE<o></o>

.Offset(rowCount, 3).Value =Format(Now(), "h:mm AM/PM") 'START<o></o>

<o> </o>

If ComboBox1.Value = "Name 2" Then<o></o>

rowCount =Worksheets("Sheet2").Range("A1").CurrentRegion.Rows.Count<o></o>

WithWorksheets("Sheet2").Range("A1")<o></o>

.Offset(rowCount, 0).Value =Me.ComboBox1.Value 'NAME<o></o>

.Offset(rowCount, 1).Value =Me.ComboBox2.Value 'TASK<o></o>

.Offset(rowCount, 2).Value =Format(Now(), "MM/DD/YYYY") 'DATE<o></o>

.Offset(rowCount, 3).Value =Format(Now(), "h:mm AM/PM") 'START<o></o>

<o> </o>

If ComboBox1.Value = "Name 3" Then<o></o>

rowCount =Worksheets("Sheet3").Range("A1").CurrentRegion.Rows.Count<o></o>

With Worksheets("Sheet3").Range("A1")<o></o>

.Offset(rowCount, 0).Value =Me.ComboBox1.Value 'NAME<o></o>

.Offset(rowCount, 1).Value =Me.ComboBox2.Value 'TASK<o></o>

.Offset(rowCount, 2).Value =Format(Now(), "MM/DD/YYYY") 'DATE<o></o>

.Offset(rowCount, 3).Value =Format(Now(), "h:mm AM/PM") 'START<o></o>

<o> </o>

wb2.Save<o></o>

' MsgBox "message.", vbExclamation<o></o>

Application.ScreenUpdating = True<o></o>

End If<o></o>

End Sub <code> <o></o>

 

[Stiuart_W]

You have 3 If statements and only 1 End If. Each If statement requires it's own End If

 

[Andrew Poulsom]

You are missing some End Withs and End Ifs. You can avoid adding more End Ifs by using:

ElseIf ComboBox1.Value = "Name 2" Then

 

[Stiuart_W]

Select case would be more appropriate in this case though

 

[Norie]

You have 3 If statements but only one End If, each of those statements needs an End If

As well as missing End Ifs you are missing 3 End Withs.

This will compile.

 Application.ScreenUpdating = False



    Workbooks.Open "S:\locationof file.xlsx"



    Set wb2 = ActiveWorkbook



    If ComboBox1.Value = "Name 1" Then

        RowCount = Worksheets("sheet1").Range("A1").CurrentRegion.Rows.Count

        With Worksheets("sheet").Range("A1")

            .Offset(RowCount, 0).Value = Me.ComboBox1.Value    'NAME

            .Offset(RowCount, 1).Value = Me.ComboBox2.Value    'TASK

            .Offset(RowCount, 2).Value = Format(Now(), "MM/DD/YYYY")    'DATE

            .Offset(RowCount, 3).Value = Format(Now(), "h:mm AM/PM")    'START
        End With
    End If


    If ComboBox1.Value = "Name 2" Then

        RowCount = Worksheets("Sheet2").Range("A1").CurrentRegion.Rows.Count

        With Worksheets("Sheet2").Range("A1")

            .Offset(RowCount, 0).Value = Me.ComboBox1.Value    'NAME

            .Offset(RowCount, 1).Value = Me.ComboBox2.Value    'TASK

            .Offset(RowCount, 2).Value = Format(Now(), "MM/DD/YYYY")    'DATE

            .Offset(RowCount, 3).Value = Format(Now(), "h:mm AM/PM")    'START
        End With
    End If


    If ComboBox1.Value = "Name 3" Then

        RowCount = Worksheets("Sheet3").Range("A1").CurrentRegion.Rows.Count

        With Worksheets("Sheet3").Range("A1")

            .Offset(RowCount, 0).Value = Me.ComboBox1.Value    'NAME

            .Offset(RowCount, 1).Value = Me.ComboBox2.Value    'TASK

            .Offset(RowCount, 2).Value = Format(Now(), "MM/DD/YYYY")    'DATE

            .Offset(RowCount, 3).Value = Format(Now(), "h:mm AM/PM")    'START
        End With
    End If

PS You could probably cut the code down a bit, for example.

Option Explicit
Private Sub CommandButton1_Click()
Dim wb2 As Workbook
Dim RowCount As Long
Dim ws As Worksheet

    'UPDATES WORKBOOK

    Application.ScreenUpdating = False

    Set wb2 = Workbooks.Open("S:\locationof file.xlsx")

    Select Case ComboBox1.Value
        Case "Name 1"
            Set ws = wb2.Sheets("Sheet1")
        Case "Name 2"
            Set ws = wb2.Sheets("Sheet2")
        Case "Name 1"
            Set ws = wb2.Sheets("Sheet3")
    End Select

    RowCount = ws.Range("A1").CurrentRegion.Rows.Count

    With ws.Range("A1")

        .Offset(RowCount, 0).Value = Me.ComboBox1.Value    'NAME

        .Offset(RowCount, 1).Value = Me.ComboBox2.Value    'TASK

        .Offset(RowCount, 2).Value = Format(Now(), "MM/DD/YYYY")    'DATE

        .Offset(RowCount, 3).Value = Format(Now(), "h:mm AM/PM")    'START
    End With

End Sub

 

[jme851]

thank you all very much!!! - I added the end with, end if; it works great.