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