vba delete rows and find &replace based on criteria

[djossh]

hi. I would like to delete some rows based on the textbox value (as criteria) then to replace the next column with "cancel" please see below sample data

Sample data below (in this example i will input "224" as criteria in my textbox)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]123[/TD]
[TD]XXX[/TD]
[TD]12[/TD]
[TD]541[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]123[/TD]
[TD]DDS[/TD]
[TD]245[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]123[/TD]
[TD]GGS[/TD]
[TD]54[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]224[/TD]
[TD]HRR[/TD]
[TD]554[/TD]
[TD]65[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]224[/TD]
[TD]SDFS[/TD]
[TD]645[/TD]
[TD]84[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]415[/TD]
[TD]SDG[/TD]
[TD]25[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]415[/TD]
[TD]HRE[/TD]
[TD]564[/TD]
[TD]87[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]415[/TD]
[TD]REE[/TD]
[TD]697[/TD]
[TD]56[/TD]
[/TR]
</tbody>[/TABLE]

RESULT data should be like this. (1 row with "224" was deleted, and retain 1 data but replace the column B with "Cancel" and other data were remove)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]123[/TD]
[TD]XXX[/TD]
[TD]12[/TD]
[TD]541[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]123[/TD]
[TD]DDS[/TD]
[TD]245[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]123[/TD]
[TD]GGS[/TD]
[TD]54[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]224[/TD]
[TD]CANCEL[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]415[/TD]
[TD]SDG[/TD]
[TD]25[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]415[/TD]
[TD]HRE[/TD]
[TD]564[/TD]
[TD]87[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]415[/TD]
[TD]REE[/TD]
[TD]697[/TD]
[TD]56[/TD]
[/TR]
</tbody>[/TABLE]

thanks in advance

 

[My Aswer Is This]

Your subject title say Delete Rows

But your image shows cancelled being entered.
So I assume you do not want to delete the row.
Otherwise there would be no purpose putting Cancelled into column B if we were to delete the row.

Try this:

Sub Look_For_Value()
'Modified  12/9/2018  10:32:37 PM  EST
Application.ScreenUpdating = False
Dim ans As String
ans = ActiveSheet.TextBox1.Value
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow
    If Cells(i, 1).Value = ans Then
        Cells(i, 2).Value = "Canceled"
        Cells(i, 3).Resize(, 2).Value = ""
    End If
Next
Application.ScreenUpdating = True
End Sub

 

[Akuini]

I assumed your data is sorted by column A.
Try this:

Sub a1080281b()
'https://www.mrexcel.com/forum/excel-questions/1080281-vba-delete-rows-find-replace-based-criteria.html
Dim n As Long, c As Long, d As Long
Dim rng As Range
Dim v As String


n = Range("A" & Rows.count).End(xlUp).Row
Set rng = Range("A1:A" & n)

v = 123
c = rng.Find(What:=v, LookIn:=xlValues, After:=Cells(n, "A"), lookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row
    Cells(c, "B").Resize(1, 3).ClearContents
    Cells(c, "B") = "cancel"

d = rng.Find(What:=v, LookIn:=xlValues, After:=Cells(1, "A"), lookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False).Row
    If d > c Then Range(Cells(c + 1, "A"), Cells(d, "A")).EntireRow.Delete


End Sub

 

[djossh]

I assumed your data is sorted by column A.
Try this:

Sub a1080281b()
'https://www.mrexcel.com/forum/excel-questions/1080281-vba-delete-rows-find-replace-based-criteria.html
Dim n As Long, c As Long, d As Long
Dim rng As Range
Dim v As String


n = Range("A" & Rows.count).End(xlUp).Row
Set rng = Range("A1:A" & n)

v = 123
c = rng.Find(What:=v, LookIn:=xlValues, After:=Cells(n, "A"), lookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row
    Cells(c, "B").Resize(1, 3).ClearContents
    Cells(c, "B") = "cancel"

d = rng.Find(What:=v, LookIn:=xlValues, After:=Cells(1, "A"), lookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False).Row
    If d > c Then Range(Cells(c + 1, "A"), Cells(d, "A")).EntireRow.Delete


End Sub

THANK YOU SO MUCH! GOD Bless.

 

[djossh]

Your subject title say Delete Rows

But your image shows cancelled being entered.
So I assume you do not want to delete the row.
Otherwise there would be no purpose putting Cancelled into column B if we were to delete the row.

Try this:

Sub Look_For_Value()
'Modified  12/9/2018  10:32:37 PM  EST
Application.ScreenUpdating = False
Dim ans As String
ans = ActiveSheet.TextBox1.Value
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow
    If Cells(i, 1).Value = ans Then
        Cells(i, 2).Value = "Canceled"
        Cells(i, 3).Resize(, 2).Value = ""
    End If
Next
Application.ScreenUpdating = True
End Sub

Thank you!! God Bless

 

[Akuini]

THANK YOU SO MUCH! GOD Bless.

You're welcome