Need help with Search and Clear cells!

[fmarsei]

Hi,

I am trying to create a macro that will search for a row which contains two variable values (cells B3 and B4) and clear only some cells of the row.

I found some codes in the net, modified them a little, and achieved finding a ROW that contains ONE value (B3) and clearing the corresponding cells.

The problem here is that there may be two rows with the same B3 value but not B4 and that if the value in B3 isn't in the given range it deletes data randomly

Hope someone could help!

Sub Searchdelete1()
'
' Searchdelete1 Macro
' Macro recorded 16/06/2010 by FMARSEI
'
Dim aVab
Dim c As Range
Sheets("Control PE").Select
aVab = Range("B3").Value

For Each c In Range("C8:C100")
If c = aVab Then
c.Select
End If
Next c
ActiveCell.Offset(0, -1).Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Select
Selection.ClearContents
Sheets("NOMINACIONES").Select
End Sub

 

[machopicho]

Sub Searchdelete1()
'
' Searchdelete1 Macro
' Macro recorded 16/06/2010 by FMARSEI
'
Dim aVab
Dim c As Range
Sheets("Control PE").Select

Set aVab = Range("B3").Value
For Each c In Range("C8:C100")
If c = aVab Then
c.ClearContents
End If
Next c
 
Set aVab = Range("B4").Value
For Each c In Range("C8:C100")
If c = aVab Then
c.ClearContents
End If
Next c
 
End Sub

 

[fmarsei]

Hi machopicho,

Thanks for your reply!

The values I want to clear are the ones that have both B3 and B4 values, not the ones that have only B3 nor only B4.

My macro, as yours well as yours, clears them all :S

 

[machopicho]

Explaine with excel image or copying and paste here a piece of excel sheet conainig values,

 

[fmarsei]

I have a macros that copy the values from column C, D or E respectively (the ones coloured) to the first blank line in the list below (transposing values).

That works fine.

The values in the coloured cells vary acording to data from another spreadsheet.

Once the data is pasted, I would now need a macro that will clear the cells (not delete row) with the SAME values that are in the coloured cells. As their might be two lines with the name MESSI but some data from the row might VARY (as below).

I would like to CLEAR the cells that say MESSI ARGENTINA ROSARIO as the ones coloured above.

Could anyone help??

 

[fmarsei]

The macro I have been using is the one below. That clears the data when it matches ONLY the field MESSI (for example) and not the other cells.

Sub PEdelete1()
Sheets("Control PE").Select
Dim FindString As String
Dim Rng As Range
FindString = Sheets("Control PE").Range("B3").Value
If Trim(FindString) <> "" Then
With Sheets("Control PE").Range("C8:C100")
Set Rng = .Find(What:=FindString, _
After:=.Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
ActiveCell.Offset(0, -1).Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Select
Selection.ClearContents
ActiveCell.Offset(0, 1).Select
Selection.ClearContents

Else
'do nothing
End If
End With
End If
Range("A1").Select
Sheets("NOMINACIONES").Select
End Sub