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.



--
Regards
Bill

[Jim Jackson]

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

Jim


"justagrunt" wrote:

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

[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

"justagrunt" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>
>
> --
> Regards
> Bill