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

tricky search and replace
 

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

tricky search and replace
 

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

tricky search and replace
 

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

tricky search and replace
 

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

tricky search and replace
 

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

tricky search and replace
 

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!