Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2012
    Posts
    1

    Angry Unanswered: End If... Block If... Why do you pester me so?

    hello all,

    I need some Advice, I have asked at other forums but my posts are left dead for days.
    This is more than likely a simple problem I went around the hard way to do, but it worked until this morning.

    commandbutton3 = Search

    Code:
    Private Sub CommandButton3_Click()
    'nothing entered
    If TextBox1.Value <> "" And TextBox2.Value <> "" And TextBox3.Value <> "" And TextBox5.Value <> "" Then MsgBox "No Criteria Found" Else
    'entered only in box 1
    If TextBox2.Value <> "" And TextBox3.Value <> "" And TextBox5.Value <> "" Then Range("A1").AutoFilter Field:=3, Criteria1:=TextBox1.Value Else
    'entered only in box 2
    If TextBox1.Value <> "" And TextBox3.Value <> "" And TextBox5.Value <> "" Then Range("A1").AutoFilter Field:=3, Criteria1:=TextBox2.Value Else
    'entered only in box 3
    If TextBox1.Value <> "" And TextBox2.Value <> "" And TextBox5.Value <> "" Then Range("A1").AutoFilter Field:=3, Criteria1:=TextBox3.Value Else
    'entered only in box 5
    If TextBox1.Value <> "" And TextBox2.Value <> "" And TextBox3.Value <> "" Then Range("A1").AutoFilter Field:=3, Criteria1:=TextBox5.Value _
    Else _
    Range("A1").AutoFilter Field:=4, Criteria1:=TextBox2.Value
    Range("A1").AutoFilter Field:=6, Criteria1:=TextBox3.Value
    Range("A1").AutoFilter Field:=1, Criteria1:=TextBox5.Value
    End If
    End If
    End If
    End If
    End If
    End Sub
    Above is the code in which I am using for a mere part of the vba project I am doing. basically i have created a user form (see below) to search the data on the spread sheet. this spreadshet is detailed with another userform to enter the data by row and is contained in headings running from A-N.

    i can search the spread sheet but all the fields must contain information and be entered correctly. also I can't seem to get my List box to populate with the filtered information.

    any help would be appreciated, I have other items I am also currently getting my code around but If you would like to see the document it is availible here.

    Userform Layout:

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    You have mis-formatted your If statements - you are trying to combine the single-line and multi-line formats, and the VBA compiler (quite rightly) cannot cope with this.

    The single-line format is
    Code:
    If [Boolean statement/variable] Then [Single instruction for True]
    The multi-line format is
    Code:
    If [Boolean statement/variable] Then
    [One or more instructions for True]
    Else
    [One or more instructions for False]
    End If
    You can also have ElseIf...Then statements between the actions when true and the Else statement.

    This will shape up to be a very cumbersome block of code. Is it possible to validate the contents of the cells at data entry instead?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Tags for this Thread

Posting Permissions

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