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

 

[dmt32]

Hi,
see if this update to your code helps you

Sub ClearText(ByVal sh As Object)
    Dim wsInput     As Worksheet
    Dim Ans         As Variant
    Dim rw          As Long
   
    Set wsInput = Worksheets("Sheet1")
   
    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
                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
                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
   
End Sub

To call it

ClearText WorkSheets("Jan")

Change the name of sheet you want to search as required

Solution may need adjusting to meet your specific project need but hopefully, goes in right direction for you.

Dave

 

[Excel Ent]

Thank a bunch. Would you know how to get it to loop through all the worksheets (FEB-DEC) to perform the same function? The range of worksheets is specific to FEB-DEC because there are other sheets that are feeding the FEB-DEC tabs.

 

[dmt32]

Thank a bunch. Would you know how to get it to loop through all the worksheets (FEB-DEC) to perform the same function? The range of worksheets is specific to FEB-DEC because there are other sheets that are feeding the FEB-DEC tabs.

Hi,
If it is your intention to loop all 12 month sheets for the same search then 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("Sheet1")
    
    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
                For i = 1 To 12
                
                    Set sh = Worksheets(MonthName(i, True))
                    
                    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
                Next i
            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
    
End Sub

Dave

 

[Excel Ent]

Thank you for your help I really appreciate your time. Every time I run the macro I get this error. It says no cells were found

 

[Excel Ent]

Hello again. This works when I change the following

If UCase(sh.Cells(rw, "A")) = UCase(Ans) Then _
sh.Rows(rw).SpecialCells(xlCellTypeConstants).ClearContents

to

If UCase(sh.Cells(rw, "A")) = UCase(Ans) Then _
sh.Rows(rw).entirerow.ClearContents

however this clears the formulas. Any thoughts?

 

[dmt32]

Hi,
You have set the Input sheet to one of you comparison sheets

Set wsInput = Worksheets("Jan")

This line must be the Input sheet only & not any of the month name sheets (Jan -Dec)

Set wsInput = Worksheets("Sheet1")

change the sheet name (Sheet1) as required but it cannot be any of the sheet names the code is clearing ranges on

Also, can you use CODE TAGS when posting code please

Dave

 

[Excel Ent]

Hi,
You have set the Input sheet to one of you comparison sheets

Set wsInput = Worksheets("Jan")

This line must be the Input sheet only & not any of the month name sheets (Jan -Dec)

Set wsInput = Worksheets("Sheet1")

change the sheet name (Sheet1) as required but it cannot be any of the sheet names the code is clearing ranges on

Also, can you use CODE TAGS when posting code please

Dave

Copy on the Code Tags. Thanks for reminder and ill continue to do so when I post!

One last and final question as you have been a tremendous help. I have 12 tabs Jan-Dec. There is a "Data" tab feeding these 12 monthly tabs. if I clear the info on the "Data" tab, the 12 monthly tabs wont have the data to find. So if on my "Data" tab A3 is Larry, cells A3 in tabs Jan Dec is also Larry. I need that loop to take the user entry and find in the 12 monthly tabs and clear the row but leave the formulas referencing the "Data" tab. I have coding after this is completed to clear the found user input on the "Data" tab. Does that make sense or is your head spinning?

 

[Excel Ent]

Copy on the Code Tags. Thanks for reminder and ill continue to do so when I post!

One last and final question as you have been a tremendous help. I have 12 tabs Jan-Dec. There is a "Data" tab feeding these 12 monthly tabs. if I clear the info on the "Data" tab, the 12 monthly tabs wont have the data to find. So if on my "Data" tab A3 is Larry, cells A3 in tabs Jan Dec is also Larry. I need that loop to take the user entry and find in the 12 monthly tabs and clear the row but leave the formulas referencing the "Data" tab. I have coding after this is completed to clear the found user input on the "Data" tab. Does that make sense or is your head spinning?

I also changed the wsInput to "Data" tab it still gives me the same error. I understand the code and what it is doing but I cant figure out why it keeps giving me the no cells found. Again when I change it to entire row clear contents or special cells formulas, it works but clears all....im stumped.

 

[dmt32]

I also changed the wsInput to "Data" tab it still gives me the same error. I understand the code and what it is doing but I cant figure out why it keeps giving me the no cells found. Again when I change it to entire row clear contents or special cells formulas, it works but clears all....im stumped.

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