Reference Value not Formula in cell via VBA - Err91 prt3

[justagrunt]

Hi,
-the saga continues but the finish line is close.

In brief the vertical range of cells I am searching down have a numeric
value of say 20812 but the underlying cell has a formula reference like;
='R:\[2005 data base of jobs.xls]MASTER'!F1381

This returned an error 91 as the numeric value and above don't match - (been
a long arduous process of frustration).

The following line;

Set rng6 = Sheet1.Range("F2:F60000").Find(what:=z) 'find the correct cell

needs to reference the numeric value in the cell which is what z equals.
I've looked at special cells but do not see how this could be done or how to
add to the line required.

the line is part of the following - what is strange is that using "res"
matchs the numeric values are OK on both sheets but not when I latter want to
cut and paste.

For Each cell In rng1
res = "" 'res = some string on sheet 2
res = Application.Match(cell.Value, rng2, 0) ' match string on sheet
1 with something on sht 2

If Not IsError(res) Then ' they match
' do something
z = "" 'basically z = res
z = cell.Value 'set object from Match function

'sheet 2

Set rng3 = Sheet2.Range("E2:E100").Find(what:=z) 'find the value in
sht2 which is res
Set rng4 = rng3.Offset(0, 5) '0 cells down 5 cells across is the
offset
Set rng5 = rng4.Resize(, 5) 'add 0 rows and 5 columns is now the
range

'sheet 1

Set rng6 = Sheet1.Range("F2:F60000").Find(what:=z) 'find the correct
cell
Set rng10 = rng6.Offset(0, 20) ' 0 cells down 20 cells across is the
offset
Set rng11 = rng6.Resize(, 5) 'add 0 rows and 5 columns is now
the range

rng5.Copy Destination:=Sheet1.Range(rng11.Address) '.PasteSpecial
' copy the value

Your help is valued with thanks.

 

[Jim Jackson]

This is just a wild guess but have you tried z = ActiveCell.Value?

 

[NickHK]

Check out the help for the Find method. You will see the 3rd argument is
"LookIn".
<From Help>
LookIn Optional Variant. Can be one of the following XlFindLookIn
constants: xlFormulas, xlValues, or xlComments.
</From Help>

NickHK