Thread
:
macro that adds formula to replace text
View Single Post
#
5
Posted to microsoft.public.excel.worksheet.functions
Don Guillett
external usenet poster
Posts: 10,124
macro that adds formula to replace text
Sub ReplaceDashesRowAbove()
With Range("a1:a" & Cells(Rows.Count, 1).End(xlUp).Row)
Set c = .Find(What:="--", After:=Cells(1, 1), _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext)
On Error GoTo nomo:
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Value = c.Offset(-1)
Set c = .FindNext(c)
Loop While Not c Is Nothing _
And c.Address < firstAddress
End If
End With
nomo:
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Lawribird" wrote in message
...
I have a list of Account and phone numbers that looks like this
A B C
1 2302009591 SMITH
2 2302009591 (123)123-0000
3 2302009591 (123)123-1000
4 2302009912 SAMS
5 --------- (123)123-1001
6 2302009949 HUGHES
7 2302009949 (123)123-1999
8 2302009947 WILLIAMS
9 2302009947 (123)123-2000
I want to create a macro that searches for the " ---------" and replaces
it
with the account number directly above it. For example in this case A5
would
be replaced automatically with the results in A4. But since there are
multiple occurance of " ---------" and each time I run this report those
occurances are on in different rows, I need something that will do this
replacement no matter what row this occures on. Can anyone help?
Reply With Quote
Don Guillett
View Public Profile
Find all posts by Don Guillett