PDA

View Full Version : [SOLVED] Find a string and clear cell's contents to the right



Johnatha
01-11-2016, 11:36 AM
Hi everyone,

I have successfully created a macro that searches for a specific string (for example, "EEOG"), and clears the contents of the cell 3 columns to the right of said string. My question is- how can I get my code to find ALL of said strings, and clear the contents of the cell 3 columns to the right, throughout my entire workbook? Right now it only finds 1 row that contains "EEOG" but not all the other rows.

Here is my working code:


Sub Macro1()

Cells.Find(What:="EEOG", LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
True, SearchFormat:=False).Offset(, 3).Select
Selection.ClearContents

End Sub

Thanks :)
-Johnathan

Johnatha
01-11-2016, 12:10 PM
Solved! Ended up using :


Dim xRange As Range
Dim xCel As Range
Set xRange = Range("A:A")
For Each xCel In xRange
If xCel.Value = "EEOG" Then
xCel.Offset(, 3).ClearContents
End If
Next xCel

mancubus
01-11-2016, 12:19 PM
reading:
https://msdn.microsoft.com/en-us/library/office/ff839746.aspx

and adopting:


Sub vbax_54785_Find_All_Occurences_Of_SearchString()


Dim FoundCell As Range
Dim FirstAddress As String

With ActiveSheet.UsedRange.Cells
Set FoundCell = .Find(What:="EEOG", LookIn:=xlFormulas, MatchCase:=True)
If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
Do
FoundCell.Offset(, 3).ClearContents
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing And FoundCell.Address <> FirstAddress
End If
End With


End Sub

jolivanes
01-11-2016, 09:14 PM
Without looping.
Might be faster. Amount of data dependent.



Sub test()
Application.ScreenUpdating = False
Rows(1).Insert
With Columns("A")
.AutoFilter 1, "EEOG"
.Offset(, 3).ClearContents
.AutoFilter
End With
Rows(1).Delete
Application.ScreenUpdating = True
End Sub

snb
01-12-2016, 01:08 AM
Even simpler/faster


Sub M_snb()
[D1:D2000]=[if(A1:A2000="","",if(A1:A2000="EEOG","",D1:D2000))]
End sub