Looping off user input

[Excel Ent]

Hello folks
I have a project workbook that takes the users input from sheet 1 and compares it against a range in sheet 2 ("Jan") If the match is found on this sheet ("Jan"), I need it to clear the row that the match is found on while preserving the formulas in "A" and "B2". So for example, if Jon is found in "A7", cells "A7" and "B7" are cleared but still hold the formula while cells "C" thru "G" are cleared as well. (cells C thru G are just normal cells). The highlighted text below keeps giving me a runtime error. One last level of complexity, if this can work, I need it to look through the other 11 worksheets "Feb" thru "Dec" and do the same. Thanks in advance!


Do
Ans = InputBox("Enter the name of the associate you wish to remove.")
If Ans = "" Then Exit Sub
If Application.CountIf(Range("A1:A358"), Ans) > 0 Then
Sheets("Jan").Select
Flg = True
For rw = 358 To 3 Step -1
If Cells(rw, "A") = Ans Then Rows(rw).Cells.SpecialCells(xlCellTypeConstants).ClearContents
Next rw
End If
If Not Flg Then If MsgBox("That entry is invalid. Do you want to try again?", vbYesNo) = vbNo Then Exit Sub
Loop Until Flg

 

[Excel Ent]

helpful if you could place a copy of your workbook with some dummy data on a file sharing site like dropbox & provide a link to it.

Dave

https://www.dropbox.com/s/kv26avaolbk8nrt/ASSOCIATE%20TRACKING%20TEMPLATE%20SAMPLE.xlsm?dl=0

 

[dmt32]

Seems to work ok if your sheet(s) have data in them

Try this update

Sub ClearText()
    Dim wsInput     As Worksheet, sh As Worksheet
    Dim Ans         As Variant
    Dim rw          As Long
    Dim i           As Integer
    
    Set wsInput = Worksheets("Inputs")
    
    Do
        Ans = InputBox("Enter the name of the associate you wish To remove.", "Enter Search Text")
        'cancel pressed
        If StrPtr(Ans) = 0 Then Exit Sub
        If Len(Ans) > 0 Then
            If Application.CountIf(wsInput.Range("A1:A358"), Ans) > 0 Then
                Application.EnableEvents = False
                For i = 1 To 12
                    Set sh = Worksheets(MonthName(i, True))
                    On Error Resume Next
                    For rw = 358 To 3 Step -1
                        'text comparison case-insensitive
                        If UCase(sh.Cells(rw, "A")) = UCase(Ans) Then _
                        sh.Rows(rw).SpecialCells(xlCellTypeConstants).ClearContents
                    Next rw
                    Set sh = Nothing
                    On Error GoTo 0
                Next i
                Exit Do
            Else
                If MsgBox("That entry Is invalid. Do you want To try again?", 36, "Not Found") = vbNo Then Exit Sub
            End If
        End If
    Loop
    
exitsub:
 Application.EnableEvents = True
End Sub

Dave

 

[Excel Ent]

Seems to work ok if your sheet(s) have data in them

Try this update

Sub ClearText()
    Dim wsInput     As Worksheet, sh As Worksheet
    Dim Ans         As Variant
    Dim rw          As Long
    Dim i           As Integer
   
    Set wsInput = Worksheets("Inputs")
   
    Do
        Ans = InputBox("Enter the name of the associate you wish To remove.", "Enter Search Text")
        'cancel pressed
        If StrPtr(Ans) = 0 Then Exit Sub
        If Len(Ans) > 0 Then
            If Application.CountIf(wsInput.Range("A1:A358"), Ans) > 0 Then
                Application.EnableEvents = False
                For i = 1 To 12
                    Set sh = Worksheets(MonthName(i, True))
                    On Error Resume Next
                    For rw = 358 To 3 Step -1
                        'text comparison case-insensitive
                        If UCase(sh.Cells(rw, "A")) = UCase(Ans) Then _
                        sh.Rows(rw).SpecialCells(xlCellTypeConstants).ClearContents
                    Next rw
                    Set sh = Nothing
                    On Error GoTo 0
                Next i
                Exit Do
            Else
                If MsgBox("That entry Is invalid. Do you want To try again?", 36, "Not Found") = vbNo Then Exit Sub
            End If
        End If
    Loop
   
exitsub:
Application.EnableEvents = True
End Sub

Dave

This works!!!!! Very nicely done!! Appreciate your guidance with this project and time to help me out.

 

[dmt32]

This works!!!!! Very nicely done!! Appreciate your guidance with this project and time to help me out.

welcome - glad resolved & appreciate feedback

Dave