tricky search and replace

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

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

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

"john" wrote in message ...
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...?

Hi John,

replace XXXX with this

=OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-2,3)
--
Allllen


"john" wrote:

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

replace XXXX with this

=OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-2,3)

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

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


"john" wrote:

replace XXXX with this

=OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),-2,3)

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

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

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!