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:.
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:.