tricky search and replace

J

john

I need to search for a particular string in a very large spreadsheet, and
when I find it I need to replace it with the contents of, or a reference to,
the cell 3 to the right and 2 above it.

For example... Let's say the string is 'XXXX'. Lets say the 1st instance of
'XXXX' is in B10 and the 2nd is in B24. So what I want is for B10 to equal
"=E8" (or the contents of E8) and B24 to equal "=E22" (or the contents of
E22).

This is for a one off manipulation of a large file (a1:bg56856). Any
ideas...?
 
J

john

hmmm... nearly got it - this *should* work in theory, but it keeps giving an
error...

=CELL("contents",ADDRESS(ROW()-2,5,4))

if I just put =ADDRESS(ROW()-2,5,4) then it returns a valid cell reference
as text.
if I put =CELL("contents",[validcellreference]) then that also works as
expected. But when I put the 2 together it gives a formula error...

and that's assuming it let's me replace the text with a formula like this...
 
B

Bernie Deitrick

John,

Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub FindValues()
Dim c As Range ' The cell found with what you want
Dim d As Range ' All the cells found with what you want
Dim myFindString As String
Dim firstAddress As String


myFindString = "XXXXX"
With Cells

Set c = .Find(myFindString, LookIn:=xlValues, lookAt:=xlWhole)

If Not c Is Nothing Then
Set d = c
firstAddress = c.Address
Else:
MsgBox "Not Found"
End
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address <> firstAddress Then
Do
Set d = Union(d, c)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

'Then put a formula in all those cells....
d.FormulaR1C1 = "=R[-2]C[3]"

End Sub
 
J

john

replace XXXX with this
=OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-2,3)
Click to expand...

ok, that woirks on an individual basis, but search and replace doesn't like
it. looks like I'll have to go the macro route...
 
A

Allllen

works fine for me, even with a few values.
what problem do you get with find and replace?
 
J

john

works fine for me, even with a few values.
what problem do you get with find and replace?
Click to expand...

oh yeah, so it does. When I said 'find' it found it, but when I pressed
'replace' it said it couldn't find it...(?) But if I select the whole xls
first then it works - thanks!
 
Top