Object required error - Still perservering with this code - Ideas

[justagrunt]

Hi,

I'm getting a "Object Required" error at

Set rng5.Resize(, 5) = rng10 'add 0 rows and 5 columns

which is part of

If Not IsError(res) Then
' they match, do something

z = ""
z = cell.Value 'set object from Match function

Set rng3 = Sheet2.Range("E2:E100").Find(what:=z) 'find the value in
sht2

Set rng5 = rng3.Offset(0, 5) '0 cells down 5 cells across is
the offset
Set rng5.Resize(, 5) = rng10 'add 0 rows and 5 columns


Set rng4 = Sheet1.Range("F2:F65536").Find(what:=z) 'find the correct
cell
Set rng6 = rng4.Offset(0, 20) ' 0 cells down 20 cells across is the
offset
Set rng6.Resize(, 5) = rng11 'add 0 rows and 4 columns to range
rng10.Copy Destination:=Sheet1.Range(rng11.Address) '.PasteSpecial
' copy the value


Else
' they don't match
End If
' continue the search
Next

Cn the above work or do I have to make 5 copies of the above to get a copy
paste which steps across 1 column at a time, five times - the offset.
Many thanks in advance

 

[JE McGimpsey]

Not sure why you're getting the "Object Required" error - you should be
getting the "Invalid use of property" error on that line.

You can only apply Set to object variables. rng5.Resize(, 5) returns a
Range, not a variable.

Did you perhaps mean

Set rng10 = rng5.Resize( , 5)

????

 

[justagrunt]

Arrrrrr,
I'll try that now.
Cheers and many thanks.

 

[justagrunt]

Hi,
Updated the code to,
If Not IsError(res) Then
' they match, do something

z = ""
z = cell.Value 'set object from Match function

Set rng3 = Sheet2.Range("E2:E100").Find(what:=z) 'find the value in
sht2

Set rng5 = rng3.Offset(0, 5) '0 cells down 5 cells across is
the offset
Set rng10 = rng5.Resize(, 5) 'add 0 rows and 5 columns


Set rng4 = Sheet1.Range("F2:F65536").Find(what:=z) 'find the correct
cell
rng6 = rng4.Offset(0, 20) ' 0 cells down 20 cells across is the offset
Set rng11 = rng6.Resize(, 5) 'add 0 rows and 4 columns to range
rng10.Copy Destination:=Sheet1.Range(rng11.Address) '.PasteSpecial
' copy the value


Else
' they don't match
End If
' continue the search
Next

And now error at
rng6 = rng4.Offset(0, 20) ' 0 cells down 20 cells across is the offset
which has the same error even with "Set rng6......." of
"Object variable or with block variable not set"

What could be causing this now?
Basically I have taken a tried method and just added the resize.
Any ideas - I'm lost for now.

 

[JE McGimpsey]

Your now trying to assign the value found at rng4.Offset(0, 20) to the
range pointed to by rng6, but you haven't set rng6 yet. Did you mean

Set rng6 = rng4.Offset(0, 20)

?

 

[justagrunt]

Hi,
I changed the code to read.
Set rng4 = Sheet1.Range("F2:F65536").Find(what:=z) 'find the correct
cell
Set rng6 = rng4.Offset(0, 20) ' 0 cells down 20 cells across is the
offset
Set rng11 = rng6.Resize(, 5) 'add 0 rows and 4 columns to range
rng10.Copy Destination:=Sheet1.Range(rng11.Address) '.PasteSpecial
' copy the value

Unfortunately still getting the error message
"Object variable or with block variable not set" error 91, which debuggs to
line,
Set rng6 = rng4.Offset(0, 20) ' 0 cells down 20 cells across is the offset

???????????????? dazed and confused - don't know apossible reason for this.

Thanks for the support and your perserverance.

 

[JE McGimpsey]

Find isn't finding whatever you have in variable z in the range
F2:F65536, so your first statement sets rng4 to Nothing (in other words,
the object variable is not set). Your second statement then tries to use
that variable, which isn't set to any range, as the basis for the
Offset, which returns the error you get...