Reference Value not Formula in cell via VBA - Err91 prt3
 

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

Reference Value not Formula in cell via VBA - Err91 prt3
 

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

Reference Value not Formula in cell via VBA - Err91 prt3
 

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