none
Help with VBA for Excel - find row containing value / select row / delete row

    Question

  • I'm another Excel newbie working on a school project using VBA for Excel. Currently I have a UserForm for users to enter their first name, last name, date and time. I have a table that records the data. Now what I would like to do is:

    1. For each row in range of the sheet "Student Information", find the row that contains TextBoxFirstName.Value in column A AND TextBoxLastName.Value in column C AND TextBoxDate.Value in column G AND ListBoxTime.Value in column I.

    2. Select column E in that row that fulfils the conditions above and save the value of that cell.

    ^ if value of that cell is "ADULT", convert that into "1" and save "1".

    ^ if value of that cell is "STUDENT", convert that into "2" and save "2".

    ^ if value of that cell is "SENIOR", convert that into "3" and save "3".

    3. Delete that row.

    4. Go to sheets("March") and find the row that contains TextBoxDate.Value in column C. Select that row.

    5. Within 2 rows below the row, find the row that contains ListBoxTime.Value in column A. Select that row.

    6. Within that row, find the first cell that contains the saved value - either 1, 2 or 3.

    7. Select that cell and enter "." into that cell.

    I am aware that it is a lengthy set of instructions, but I have experimented for 3 weeks without any progress, searched all online tutorials, but I couldn't figure it out. 

    Any help would be hugely appreciated !

    Friday, February 15, 2013 10:21 AM

All replies

  • Do it in steps and post back when you get this first step working. I have assumed that your table on Student Information starts in Cell A1 and has a header row:

    Sub Step1()

        Dim myF As Range
        Set myF = Worksheets("Student Information").Range("A1").CurrentRegion
        myF.AutoFilter Field:=1, Criteria1:=TextBoxFirstName.Value
        myF.AutoFilter Field:=3, Criteria1:=TextBoxLastName.Value
        myF.AutoFilter Field:=7, Criteria1:=TextBoxDate.Value
        myF.AutoFilter Field:=9, Criteria1:=ListBoxTime.Value

    End Sub

    Since G and I are dates and times, you need to match the date and time formatting of your fields, which may mean using code like

        myF.AutoFilter Field:=7, Criteria1:=Format(DateValue(TextBoxDate.Value),"mm/dd/yy")
        myF.AutoFilter Field:=9, Criteria1:=Format(DateValue(ListBoxTime.Value),"hh:mm")

    Friday, February 15, 2013 2:19 PM
  • Many thanks: this did find the row but - as the code says itself - applied an auto-filter. Is it possible to remove the auto-filter after the entire process?

    But otherwise - the first step is successful.

    - -

    Apologies for the first paragraph - I've found a way to remove the auto-filter, so the first step is complete. Many thanks for your help! Troubled with the next steps - used Google and landed me with offset but the code did not work and debugging was merely useful to a newbie like me.

    • Edited by ansonmiu14 Friday, February 15, 2013 3:03 PM
    Friday, February 15, 2013 2:56 PM
  • OK.  First,  you don't want to remove the autofilter, since that is how we identified the rows that have the data we want.

    So, we add code to do the next steps:

    Sub Macro1()
        Dim myF As Range
        Set myF = Worksheets("Student Information").Cells
        myF.AutoFilter Field:=1, Criteria1:=TextBoxFirstName.Value
        myF.AutoFilter Field:=3, Criteria1:=TextBoxLastName.Value
        myF.AutoFilter Field:=7, Criteria1:=TextBoxDate.Value
        myF.AutoFilter Field:=9, Criteria1:=ListBoxTime.Value

        Dim myC As Range
        Dim myD As Range
        Dim myT As Range
        Dim myN As Range
        Dim myV As Integer

        For Each myC In Intersect(myF.Resize(myF.Rows.Count - 1).Offset(1), _
                                  Worksheets("Student Information").Range("E:E")) _
                                  .SpecialCells(xlCellTypeVisible)
            myV = 0
            If myC.Value = "ADULT" Then myV = 1
            If myC.Value = "STUDENT" Then myV = 2
            If myC.Value = "SENIOR" Then myV = 3
            If myV <> 0 Then
                Set myD = Worksheets("March").Range("C:C").Find(DateValue(TextBoxDate.Value))
                If myD Is Nothing Then
                    MsgBox TextBoxDate.Value & " was not found on sheet ""March"""
                Else
                    Set myT = myD.Offset(0, -2).Resize(3).Find(TimeValue(ListBoxTime.Value))
                    If myT Is Nothing Then
                        MsgBox ListBoxTime.Value & " was not found on sheet ""March"""
                    Else
                        Set myN = myT.EntireRow.Find(myV)
                        If myN Is Nothing Then
                            MsgBox myV & " was not found on ""March"" in " & myT.EntireRow.Address
                        Else
                            myN.Value = "."
                            MsgBox myN.Address & " was changed"
                            myC.EntireRow.Clear
                        End If
                    End If
                End If
            End If
        Next myC

        myF.AutoFilter

    End Sub


    Friday, February 15, 2013 4:18 PM
  • Thank you - entered the code and ran the macro, but nothing happened. I think I may have not articulated the instructions clearly; I'll try to re-word it.

    1. For each row in the range of the sheet "Student Information", find the row that contains TextBoxFirstName.Value in column A AND TextBoxLastName.Value in column C AND TextBoxDate.Value in column G AND ListBoxTime.Value in column I.

    2. Select the cell in column E in that row that fulfils the conditions above.

    3. The cell will either have ADULT, STUDENT or SENIOR as its value.

    4. If the value is "ADULT", convert that into "1" and save the value "1".

    4. If the value of that cell is "STUDENT", convert that into "2" and save the value "2".

    4. If the value of that cell is "SENIOR", convert that into "3" and save the value "3.

    5. Delete that row found in step 1.

    6. Go to sheets("March")

    7. Find the row that contains TextBoxDate.Value in column C. (start finding from row 8).

    8. Select that row.

    9. Find the row that contains ListBoTime.Value in column A within 2 rows below the row found in step 7.

    10. Select that row.

    11. Find the first cell that contains the saved value - either 1, 2 or 3 - within the row selected in step 10.

    12. Enter the value "." in that cell.

    Saturday, February 16, 2013 2:08 AM
  • That is what my code does, except for starting in row 8 for step 7.

    Note that I put Message boxes into my code that communicate when things are done, but I didn't put any handling for no results being found on the filtering. So, change this line

     For Each myC In Intersect(myF.Resize(myF.Rows.Count - 1).Offset(1), _
                                  Worksheets("Student Information").Range("E:E")) _
                                  .SpecialCells(xlCellTypeVisible)

    to this

     For Each myC In Intersect(Intersect(myF.Resize(myF.Rows.Count - 1).Offset(1), _
                            Worksheets("Student Information").Range("E:E")), _
                            Worksheets("Student Information").UsedRange) _
                            .SpecialCells(xlCellTypeVisible)

    so that it will result in an error if no cells were found meeting your filtering criteria. Put a break on that line and see what is filtered at that point.

    Saturday, February 16, 2013 1:23 PM
  • I renamed the message box to error 1 and error 2 - your altered code functioned properly and selected the cell of STUDENT. However, the messagebox ERROR 1 came up - the code functioned until:
      Set myD = Worksheets("March").Range("C:C").Find(DateValue(TextBoxDate.Value))
                If myD Is Nothing Then
                    MsgBox TextBoxDate.Value & " was not found on sheet ""March"""
                Else

    In this case, the date was 01 March 2013 in Sheets("March") with "3/1/2013" in the formula box. The date from the UserForm and the Sheets("Student Information") was "3/1/2013". Will the problem be the difference between the worded date and the numerical date?

    Many thanks again.

    - -

    Edited the code and the first two message boxes did not pop up. But in the end, nothing happened and there were no messageboxes

    - -

    Somehow a debug again showed that your altered code from your post above had runtime error 1004 : no cells were found

    - -

    Fixed the code and went back to what I started with - first two messageboxes did not pop up so the values have been found. I am suspecting that the last section is not working:

    Else 
    myN.Value = "."
    MsgBox myN.Address & "was changed"
    myC.EntireRow.Clear
    

    • Edited by ansonmiu14 Saturday, February 16, 2013 4:47 PM
    Saturday, February 16, 2013 4:17 PM
  • Finding dates can be a pain because formatting can be an issue.

    Try changing

      Set myD = Worksheets("March").Range("C:C").Find(DateValue(TextBoxDate.Value))

    to

      Set myD = Worksheets("March").Range("C:C").Find(DateValue(TextBoxDate.Value), LookAt:=xlValue)

    If myD is Nothing Them Msgbox "Try different code!"

    Saturday, February 16, 2013 5:31 PM
  • Now the special cells has a compile error: invalid or unqualified reference highlighting the special cells in the For..... (xlCellTypeVisible)

    I got it to work through adding "Stop" in the code but removing "stop" makes it not function again - why is this the case?

    • Edited by ansonmiu14 Sunday, February 17, 2013 2:19 AM
    Sunday, February 17, 2013 2:07 AM
  • Private Sub CmdButtonUnbook_Click()
    
    If TextBoxFirstName.Value = "" Then
    MsgBox ("You must enter a first name!")
    Exit Sub
    ElseIf TextBoxLastName.Value = "" Then
    MsgBox ("You must enter a last name!")
    Exit Sub
    ElseIf ListBoxTime.Value = "" Then
    MsgBox ("You must select a time!")
    Exit Sub
    Else
    
    Dim myF As Range
    
    Set myF = Worksheets("Student Information").Cells
    myF.AutoFilter Field:=1, Criteria1:=TextBoxFirstName.Value
    myF.AutoFilter Field:=3, Criteria1:=TextBoxLastName.Value
    myF.AutoFilter Field:=7, Criteria1:=TextBoxDate.Value
    myF.AutoFilter Field:=9, Criteria1:=ListBoxTime.Value
    
    Dim MyMonth As Integer
    Dim MonthName As String
    
    MyMonth = Month(TextBoxDate.Value)
    
    Select Case MyMonth
    Case 1
    MonthName = "January"
    Case 2
    MonthName = "February"
    Case 3
    MonthName = "March"
    Case 4
    MonthName = "April"
    Case 5
    MonthName = "May"
    Case 6
    MonthName = "June"
    Case 7
    MonthName = "July"
    Case 8
    MonthName = "August"
    Case 9
    MonthName = "September"
    Case 10
    MonthName = "October"
    Case 11
    MonthName = "November"
    Case 12
    MonthName = "December"
    
    End Select
    
    Dim myA As Range
    Dim myC As Range
    Dim myD As Range
    Dim myT As Range
    Dim myN As Range
    Dim myV As Integer
    
    'For Each myC In Intersect(Intersect(myF.Resize(myF.Rows.Count - 1).Offset(1), _
    '            Worksheets("Student Information").Range("E:E")), _
    '            Worksheets("Student Information").UsedRange)
    '    myV = 0
    '    If myC.Value = "Adult" Then myV = 1
    '    If myC.Value = "Student" Then myV = 2
    '    If myC.Value = "Senior" Then myV = 3
    '    If myV <> 0 Then
    '        Set myD = Worksheets(MonthName).Range("C:L").Find(DateValue(TextBoxDate.Value))
    '        If myD Is Nothing Then
    '            MsgBox TextBoxDate.Value & " was not fond on sheet " & MonthName
    '        Else
    '            Set myT = myD.Offset(1, -2).Find(ListBoxTime.Value)
    '            If myT Is Nothing Then
    '                MsgBox ListBoxTime.Value & " was not found on sheet " & MonthName
    '            Else
    '            Stop
    '            Stop
    '            Stop
    '            Resume
    '
    '                Set myN = myT.EntireRow.Find(myV)
    '                If myN Is Nothing Then
    '                    MsgBox myV & " was not found on " & MonthName & " in " & myT.EntireRow.Address
    '                Else
    '                    myN.Value = "."
    '                    MsgBox myN.Address & " was changed "
    '                    myC.EntireRow.Delete (xlShiftUp)
    '
    '                End If
    '            End If
    '        End If
    '    End If
    'Next myC
    '
    End If
    '
    'myF.AutoFilter
    
    Unload Me
    
    End Sub

    I think it would be more helpful if I just show you the code  because I am no coding expert to see where the problem lies - many thanks!

    Ignoring the comment blocks, the only part where it doesn't work is the for statement. Otherwise, having the stops there and manually resuming them will eventually work for only the first entry in the student info table.

    Sunday, February 17, 2013 2:54 PM
  • Delete your textboxes, and insert a single listbox, named Listbox. Then try code like this, to force the selection of values and combinations of data that actually exist, by adding allowed values to the listbox and sequentially filtering as items are selected.

    Option Explicit
    Dim myF As Range
    Dim shtSI As Worksheet
    Dim varPossible As Variant
    Dim varEach As Variant
    Dim varList As Variant
    Dim MyMonth As Integer
    Dim MonthName As String
    Dim strLastName As String
    Dim strFirstName As String
    Dim dteDate As Date
    Dim dteTime As Date
    Dim myA As Range
    Dim myC As Range
    Dim myD As Range
    Dim myT As Range
    Dim myN As Range
    Dim myV As Integer

    Private Sub CmdButtonUnbook_Click()
    If Me.CmdButtonUnbook.Caption = "Choose Last Name" Then
    strLastName = Me.ListBox.Value
    myF.AutoFilter Field:=3, Criteria1:=strLastName
    Me.CmdButtonUnbook.Caption = "Choose First Name"
    MakeList 1
    Exit Sub
    End If

    If Me.CmdButtonUnbook.Caption = "Choose First Name" Then
    strFirstName = Me.ListBox.Value
    myF.AutoFilter Field:=1, Criteria1:=strFirstName
    Me.CmdButtonUnbook.Caption = "Choose Date"
    MakeList 7
    Exit Sub
    End If

    If Me.CmdButtonUnbook.Caption = "Choose Date" Then
    dteDate = Me.ListBox.Value
    myF.AutoFilter Field:=7, Criteria1:=Format(dteDate, myF.Cells(2, 7).NumberFormat)
    MyMonth = Month(dteDate)
    MonthName = Format(dteDate, "MMMM")
    Me.CmdButtonUnbook.Caption = "Choose Time"
    MakeList 9
    Exit Sub
    End If

    If Me.CmdButtonUnbook.Caption = "Choose Time" Then
    dteTime = Me.ListBox.Value
    myF.AutoFilter Field:=9, Criteria1:=Format(dteTime, myF.Cells(2, 9).NumberFormat)
    Me.CmdButtonUnbook.Caption = "Proceed"
    Exit Sub
    End If

    If Me.CmdButtonUnbook.Caption = "Proceed" Then

    For Each myC In myF.Columns(5).SpecialCells(xlCellTypeVisible)
        myV = 0
        If myC.Value = "Adult" Then myV = 1
        If myC.Value = "Student" Then myV = 2
        If myC.Value = "Senior" Then myV = 3
        If myV <> 0 Then
            Set myD = Worksheets(MonthName).Range("C:L").Find(DateValue(dteDate))
            If myD Is Nothing Then
                MsgBox dteDate & " was not found on sheet " & MonthName
            Else
                Set myT = myD.Offset(1, -2).Find(dteTime)
                If myT Is Nothing Then
                    MsgBox dteTime & " was not found on sheet " & MonthName
                Else
                    Set myN = myT.EntireRow.Find(myV)
                    If myN Is Nothing Then
                        MsgBox myV & " was not found on " & MonthName & " in " & myT.EntireRow.Address
                    Else
                        myN.Value = "."
                        MsgBox myN.Address & " was changed "
                    End If
                End If
            End If
        End If
    Next myC

    myF.Columns(5).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    myF.AutoFilter

    End If

    Unload Me

    End Sub


    Private Sub UserForm_Initialize()
        Set shtSI = Worksheets("Student Information")
        If shtSI.AutoFilterMode Then shtSI.ShowAllData
        Set myF = shtSI.Range("A1", shtSI.Cells(shtSI.Rows.Count, 9).End(xlUp))
        MakeList 3
        Me.CmdButtonUnbook.Caption = "Choose Last Name"
    End Sub
    Private Sub MakeList(c As Integer)
        Set varPossible = myF.Columns(c).Offset(1).Resize(myF.Rows.Count - 1).Cells
        With CreateObject("Scripting.Dictionary")
            For Each varEach In varPossible
                If varEach.Value <> "" And (Not varEach.EntireRow.Hidden) And Not .exists(varEach.Value) Then
                    .Add IIf(c = 9, Format(varEach.Value, "h:mm"), varEach.Value), Nothing
                End If
            Next
            varList = .keys
        End With
        With Me.ListBox
            .Clear ' clear the listbox content
            .List = varList
            .ListIndex = 0 ' select the first item
        End With
    End Sub



    Sunday, February 17, 2013 4:53 PM
  • Thank you - is it possible not to use list boxes because listing every possible first name and every possible last name will be troublesome.

    Basically I have a UserForm for booking which allows the user to input their details and their details will be transferred on the Sheets("Student Information"). My purpose of the UserForm for un-booking is to allow users to delete their information from the Sheets("Student Information") and to delete their "space" on the WorkSheet corresponding to the month of the date of their lesson. On the said worksheet, there is a row of 20 cells for each date and time (i.e 1/3/2013 10 AM - 12 PM) and the value "1" suggests an adult, value "2" suggests a student, value "3" suggests a senior and value "." suggests a vacant spot. I would like the UserForm for un-booking to find and delete the information in the Sheets("Student Information") and find the corresponding row of the date and time and delete the correct "space" according to their identity.

    - -

    I tried to substitute your previous FOR statement with "Set myC = myF.Columns(E).Cells" but there was an "application-defined or object-defined error". Is there a way to fix this? I think this is the main reason why the code is not working, as well as the unexplainable "mystery" of adding stops and manually resuming them to enable the code to work.

    • Edited by ansonmiu14 Monday, February 18, 2013 3:23 PM
    Monday, February 18, 2013 2:28 PM
  • Try it this way - keep the last name text box, and add a listbox. Add a label that says "Enter you last name here"  Then after the user enters their last name, everything else to be entered is done using available values in the listbox - I'm never sure how my name is entered (Bernard or Bernie), but I am sure of my last name - forget dates and times, too....

    Option Explicit
    Dim myF As Range
    Dim shtSI As Worksheet
    Dim varPossible As Variant
    Dim varEach As Variant
    Dim varList As Variant
    Dim MyMonth As Integer
    Dim MonthName As String
    Dim strLastName As String
    Dim strFirstName As String
    Dim dteDate As Date
    Dim dteTime As Date
    Dim myA As Range
    Dim myC As Range
    Dim myD As Range
    Dim myT As Range
    Dim myN As Range
    Dim myV As Integer

    Private Sub CmdButtonUnbook_Click()
    If Me.CmdButtonUnbook.Caption = "Choose Last Name" Then
    strLastName = Me.TextBoxLastName.Value
    Set myA = myF.Columns(3).Find(strLastName)
    If myA Is Nothing Then
        MsgBox "That last name was not found"
        Exit Sub
    End If
    Me.TextBoxLastName.Enabled = False
    Me.ListBox.Enabled = True
    myF.AutoFilter Field:=3, Criteria1:=strLastName
    Me.CmdButtonUnbook.Caption = "Choose First Name"
    MakeList 1
    Exit Sub
    End If

    If Me.CmdButtonUnbook.Caption = "Choose First Name" Then
    strFirstName = Me.ListBox.Value
    myF.AutoFilter Field:=1, Criteria1:=strFirstName
    Me.CmdButtonUnbook.Caption = "Choose Date"
    MakeList 7
    Exit Sub
    End If

    If Me.CmdButtonUnbook.Caption = "Choose Date" Then
    dteDate = Me.ListBox.Value
    myF.AutoFilter Field:=7, Criteria1:=Format(dteDate, myF.Cells(2, 7).NumberFormat)
    MyMonth = Month(dteDate)
    MonthName = Format(dteDate, "MMMM")
    Me.CmdButtonUnbook.Caption = "Choose Time"
    MakeList 9
    Exit Sub
    End If

    If Me.CmdButtonUnbook.Caption = "Choose Time" Then
    dteTime = Me.ListBox.Value
    myF.AutoFilter Field:=9, Criteria1:=Format(dteTime, myF.Cells(2, 9).NumberFormat)
    Me.CmdButtonUnbook.Caption = "Proceed"
    Exit Sub
    End If

    If Me.CmdButtonUnbook.Caption = "Proceed" Then

    For Each myC In myF.Columns(5).SpecialCells(xlCellTypeVisible)
        myV = 0
        If myC.Value = "Adult" Then myV = 1
        If myC.Value = "Student" Then myV = 2
        If myC.Value = "Senior" Then myV = 3
        If myV <> 0 Then
            Set myD = Worksheets(MonthName).Range("C:L").Find(DateValue(dteDate))
            If myD Is Nothing Then
                MsgBox dteDate & " was not found on sheet " & MonthName
            Else
                Set myT = myD.Offset(1, -2).Find(dteTime)
                If myT Is Nothing Then
                    MsgBox dteTime & " was not found on sheet " & MonthName
                Else
                    Set myN = myT.EntireRow.Find(myV)
                    If myN Is Nothing Then
                        MsgBox myV & " was not found on " & MonthName & " in " & myT.EntireRow.Address
                    Else
                        myN.Value = "."
                        MsgBox myN.Address & " was changed "
                    End If
                End If
            End If
        End If
    Next myC

    myF.Columns(5).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    myF.AutoFilter
    myF.AutoFilter

    End If

    Unload Me

    End Sub

    Private Sub UserForm_Initialize()
        Set shtSI = Worksheets("Student Information")
        If shtSI.AutoFilterMode Then shtSI.ShowAllData
        Set myF = shtSI.Range("A1", shtSI.Cells(shtSI.Rows.Count, 9).End(xlUp))
        Me.CmdButtonUnbook.Caption = "Choose Last Name"
        Me.ListBox.Enabled = False
        Me.TextBoxLastName.Enabled = True
    End Sub

    Private Sub MakeList(c As Integer)
        Set varPossible = myF.Columns(c).Offset(1).Resize(myF.Rows.Count - 1).Cells
        With CreateObject("Scripting.Dictionary")
            For Each varEach In varPossible
                If varEach.Value <> "" And (Not varEach.EntireRow.Hidden) And Not .exists(varEach.Value) Then
                    .Add IIf(c = 9, Format(varEach.Value, "h:mm"), varEach.Value), Nothing
                End If
            Next
            varList = .keys
        End With
        With Me.ListBox
            .Clear ' clear the listbox content
            .List = varList
            .ListIndex = 0 ' select the first item
        End With
    End Sub

    Monday, February 18, 2013 3:38 PM
  • Private Sub CmdButtonUnbook_Click()
    
    On Error Resume Next
    
    If TextBoxFirstName.Value = "" Then
    MsgBox ("You must enter a first name!")
    Exit Sub
    ElseIf TextBoxLastName.Value = "" Then
    MsgBox ("You must enter a last name!")
    Exit Sub
    ElseIf ComboBoxIdentity.Value = "" Then
    MsgBox ("You must select your identity!")
    Exit Sub
    ElseIf ListBoxTime.Value = "" Then
    MsgBox ("You must select a time!")
    Exit Sub
    Else
    
    Dim myF As Range
    
    Set myF = Worksheets("Student Information").Range("A3:Q100")
    myF.AutoFilter Field:=1, Criteria1:=TextBoxFirstName.Value
    myF.AutoFilter Field:=3, Criteria1:=TextBoxLastName.Value
    myF.AutoFilter Field:=5, Criteria1:=ComboBoxIdentity.Value
    myF.AutoFilter Field:=7, Criteria1:=TextBoxDate.Value
    myF.AutoFilter Field:=9, Criteria1:=ListBoxTime.Value
    
    myF.EntireRow.Clear
    
    Dim MyMonth As Integer
    Dim MonthName As String
    
    MyMonth = Month(TextBoxDate.Value)
    
    Select Case MyMonth
    Case 1
    MonthName = "January"
    Case 2
    MonthName = "February"
    Case 3
    MonthName = "March"
    Case 4
    MonthName = "April"
    Case 5
    MonthName = "May"
    Case 6
    MonthName = "June"
    Case 7
    MonthName = "July"
    Case 8
    MonthName = "August"
    Case 9
    MonthName = "September"
    Case 10
    MonthName = "October"
    Case 11
    MonthName = "November"
    Case 12
    MonthName = "December"
    
    End Select
    
    Dim myD As Range
    Dim myT As Range
    Dim myN As Range
    Dim myV As Integer
    
        myV = 0
        If ComboBoxIdentity.Value = "Adult" Then myV = 1
        If ComboBoxIdentity.Value = "Student" Then myV = 2
        If ComboBoxIdentity.Value = "Senior" Then myV = 3
        If myV <> 0 Then
            Set myD = Worksheets(MonthName).Range("C:L").Find(TextBoxDate.Value)
            If myD Is Nothing Then
                MsgBox TextBoxDate.Value & " was not found on sheet " & MonthName
            Else
                If ListBoxTime.Value = "10 AM - 12 PM" Then
                Set myT = myD.Offset(1, -1)
                    Set myN = myT.EntireRow.Find(myV).Replace(What:=myV, Replacement:=".")
                    MsgBox "Your space has been unbooked."
                    myF.AutoFilter
                    Unload Me
                 ElseIf ListBoxTime.Value = "5 PM - 7 PM" Then
                Set myT = myD.Offset(2, -1)
                    MsgBox "Your space has been unbooked."
                    myF.AutoFilter
                    Unload Me
                End If
            End If
        End If
    End If
    
    End Sub
    I finally have a working code! However the line of code "Set myN = myT.EntireRow.Find(myV)" does not work properly because it finds ALL the values. Is it possible to just find the FIRST occurrence of the value?
    Tuesday, February 19, 2013 1:54 PM
  • Replace

    Set myN = myT.EntireRow.Find(myV).Replace(What:=myV, Replacement:=".")

    with the two lines

    Set myN = myT.EntireRow.Find(myV, After:=myT.EntireRow.Cells(1, 1))
    myN.Replace What:=myV, Replacement:="."

    Tuesday, February 19, 2013 2:12 PM
  • Thank you so much for your continuous help.

    My code essentially functions properly, but I am assuming that the AutoFilter does not work. If I have two separate entries such as:

    Peter Robinson Adult 4/3/2013 10 AM - 12 PM peterobinson@gmail.com 555-555-5555

    David Jones Adult 4/3/2013 10 AM - 12 PM davidjones@yahoo.com 555-555-5555

    If I enter the details of David Jones in the UnBookForm, it deletes Peter Robinson's data from the sheet instead.

    I was wondering whether there is an alternative to using AutoFilter, or whether the code can be modified to account for all the fields.

    Friday, February 22, 2013 4:11 PM
  • You are filtering on first name. Put a break on the last myF.Autofilter line (click on it and press F9) in this block of code:

    Set myF = Worksheets("Student Information").Range("A3:Q100")
    myF
    .AutoFilter Field:=1, Criteria1:=TextBoxFirstName.Value
    myF
    .AutoFilter Field:=3, Criteria1:=TextBoxLastName.Value
    myF
    .AutoFilter Field:=5, Criteria1:=ComboBoxIdentity.Value
    myF
    .AutoFilter Field:=7, Criteria1:=TextBoxDate.Value
    myF
    .AutoFilter Field:=9, Criteria1:=ListBoxTime.Value

    myF.EntireRow.Clear

    and remove this line:

    myF.EntireRow.Clear

    Then run the code and when you get to the break take a look at your StudentInformation Page.  I suspect that you have much more data than 100 rows, so change "A3:Q100" to something larger, like "A3:Q10000"

    Friday, February 22, 2013 4:25 PM
  • With the break on the AutoFilter, it shows that the AutoFilter does not filter the name - it only shows the first entry of the identity. For instance, back to the previous example, if Peter Robinson is the first adult listed in the worksheet, it filters down to Peter Robinson regardless of what names I type in.
    Saturday, February 23, 2013 11:15 AM