VBA search and delete

[Manolocs]

Hello,
I have a cell with a name on it,
Is there a possibility to look for in a table in other sheet
same workbook, find that name in the first column and then
clear a range of column in this row?
Is like a vlookup but clear the 5 columns in the line.

 

[xenou]

One try:

Sub DeleteMatches()
Dim wsLookOnSheet As Worksheet
Dim ans As Integer
Dim msg As String
Dim c As Range

Set wsLookOnSheet = Worksheets("Sheet2") 'Change to actual sheet name as needed


msg = "Value to find: " & ActiveCell.Value & vbCrLf
msg = msg & "Look on sheet: " & wsLookOnSheet.Name & vbCrLf & vbCrLf
msg = msg & "Continue?"
ans = MsgBox(msg, vbYesNo)

If ans = vbYes Then
    With wsLookOnSheet
        Set c = .Cells.Find(What:=ActiveCell.Value)
            If Not c Is Nothing Then
                .Cells(c.Row, 1).Resize(1, 5).ClearContents
                MsgBox "Cells cleared in range " & .Cells(c.Row, 1).Resize(1, 5).Address
            Else
            MsgBox "No match was found."
            End If
    End With
End If

End Sub

 

[VoG]

Try this. It assumes that the name to find is in A1 of Sheet1 and clears the contents of 5 columns on Sheet2.

Sub Clr()
Dim ToFind As String, Found As Range
ToFind = Sheets("Sheet1").Range("A1").Value
With Sheets("Sheet2")
    Set Found = .Cells.Find(what:=ToFind)
    If Not Found Is Nothing Then Range(Found, Found.Offset(0, 4)).ClearContents
End With
End Sub