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