PDA

View Full Version : [SOLVED] Trouble With If/Else using Find and Replace In Specified Range of Cells



Mulsiphix
08-24-2015, 01:09 PM
I don't know VBA yet. Just about all of my knowledge comes from piecemealing scripts I find on the internet into what I need. In this particular situation I can't seem to get that method to work. Any chance someone can tell me what I'm doing wrong?


I want the script to highlight all of the rows in A1 that contain data.
Then, cell by cell, perform a Find.
If a cell contains ( then move to the next cell.
If it does not contain ( then perform a Replace, to remove the ")" character.
Do this for each cell in the selected range.

Just to be complete, here is an example of four rows from my data. In this case the script should remove the ) from Rows 1 and 2, but leave Rows 3 and 4 unaltered.



Tunnel)
1225


Road, Urban)
1713


Building L3-B (Class: Light)
0205


R & D Center (Class: Heavy)
0206




Here are my two attempts. First I tried this:

Sub Method1()
Dim lngLastRow As Long
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
With Range("A1:A" & lngLastRow).Select
Set c = .Find(What:="(", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
If Not c Is Nothing Then
firstAddress = c.Address
Do
ActiveCell.Replace What:=")", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
EndSub

Then tried this:

Sub Method2()
Dim lngLastRow As Long
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:A" & lngLastRow).Select
Do While True
Selection.Find(What:="(", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
If Cell Is Nothing Then Exit Do
ActiveCell.Replace What:=")", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Loop
End Sub
Both blocks generate the error "Object required" in the debugger. I feel like I am running in circles :banghead:.

p45cal
08-24-2015, 04:28 PM
try:
Sub blah()
For Each cll In Columns(1).SpecialCells(xlCellTypeConstants, 23).Cells
If InStr(cll.Value, "(") = 0 Then cll.Value = Replace(cll.Value, ")", "")
Next cll
End Sub

Mulsiphix
08-28-2015, 07:47 PM
I apologize for how long it has taken me to respond. I lost access to the internet between paychecks.


try:
Sub blah()
For Each cll In Columns(1).SpecialCells(xlCellTypeConstants, 23).Cells
If InStr(cll.Value, "(") = 0 Then cll.Value = Replace(cll.Value, ")", "")
Next cll
End Sub


p45cal you are amazing! This works flawlessly and it is so much smaller than my attempts. I hadn't even seen any scripts so far that use InStr. Thank you very, very much :cloud9:.