find text / replace

[CaveMan]

Hello all,
I have reached a minor roadblock and after several days of searching
for the needed codes decided to ask the experts in order to try to
preserve my sanity.

I need to replace information on one "sheet" with information on
"another sheet" using a macro. I know this sounds simple, but the
problem is that the current information and the replacement
information will always vary. How do I search for designated text on
one page, find it on another page and replace it with info from the
first page. Hope I was able to explain this well enough.

Curtis

 

[Frank Kabel]

Hi Curtis
try something like the following (replacement takes place in sheet2,
info for replacement is on sheet 1, cells A1 and B1):

sub foo()
dim repl_wks as worksheet 'worksheet in which the replacement will
take place
Dim info_wks as worksheet 'contains replacement info
dim lookup_val
dim repl_val

set info_wks = worksheets("Sheet1")
set repl_wks = worksheets("sheet2")
lookup_val = info_wks.range("A1").value
repl_val = info_wks.range("B1").value

repl_wks.cells.Replace What:=lookup_val, Replacement:=repl_val,
LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

end sub

 

[Martyn]

Gives a "search argument not found" or a "named argument not found" compile
error for the
SearchFormat:=False
item when the macro is executed...(XL2000)
Martyn

 

[Frank Kabel]

Hi
this belongs to one line. That is the lines
repl_wks.cells.Replace What:=lookup_val, Replacement:=repl_val,
LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False,
ReplaceFormat:=False

belong to one line. Try
repl_wks.cells.Replace What:=lookup_val, Replacement:=repl_val, _
LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

 

[Martyn]

Sorry Frank but No way!..
Must be something else.
"named argument not found" compile error again for the "SearchFormat:="
syntax.
Regards
Martyn

 

[Dave Peterson]

the searchformat stuff was added in xl2002. Just get rid of both searchformat
and replaceformat portions.

 

[Martyn]

Thanks Dave, that did it!.

the searchformat stuff was added in xl2002. Just get rid of both searchformat
and replaceformat portions.

 

[Curtis Ellison]

Hello all,
First let me say thanks for trying to help me, but this still doesn't
work. I think that part of the problem might be that I haven't given
you enough detail.

Let me try again:
I am running MS Excel 2002 (If this helps)

The information that I am looking up is listed on a sheet called
"CHANGE" in cells "R1:AA1"
I need to find the same exact info on a sheet called "DATABASE" and
contains multiple lines of info (could be as much as 65000 lines in
time) in cells "A4:J65000".
Once the exact match has been found on sheet "DATABASE" I need to
replace it with info from sheet "CHANGE" cells "R2:RAA2".

Hope this helps>
Curtis

 

[Tom Ogilvy]

Sub UpdateDate()
Dim rng As Range, rng1 As Range
Dim rng2 As Range, rng3 As Range
Dim cell As Range, bMatch As Boolean
Dim i As Long
Set rng = Worksheets("Change").Range("R1")
Set rng1 = Worksheets("Database").Columns(1)
Set rng2 = rng1.Find(rng.Value)
If Not rng2 Is Nothing Then
fAddr = rng2.Address
Do
If rng3 Is Nothing Then
Set rng3 = rng2
Else
Set rng3 = Union(rng3, rng2)
End If
Set rng2 = rng1.FindNext(rng2)
Loop While rng2.Address <> fAddr
For Each cell In rng3
bMatch = True
For i = 1 To 10
If cell.Offset(0, i - 1) <> rng.Offset(0, i - 1) Then
bMatch = False
Exit For
End If
Next
If bMatch Then
rng.Offset(1, 0).Resize(1, 10).Copy _
Destination:=cell
End If
Next
End If

End Sub

worked for me (based on my interpretation of your description of your
situation).

 

[Curtis Ellison]

Thanks, that worked great.

Curtis Ellison
NSS# 36307

NSS SCCi TCS AG
Co-Chairman - Appalachian Grotto Inc.
Safety & Training Coordinator - Appalachian Grotto Inc.
Property Manager - Rattling Cave

http://www.caves.org/grotto/appalachian/

reply at:
(e-mail address removed) or

"Take nothing but pictures,
Leave nothing but well placed footprints,
Kill nothing but time"