| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Object required error - Still perservering with this code - Ideas
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 -- Regards Bill |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Object required error - Still perservering with this code - Ideas
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) ???? In article , justagrunt wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Object required error - Still perservering with this code - Id
Arrrrrr,
I'll try that now. Cheers and many thanks. -- Regards Bill "JE McGimpsey" wrote: 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) ???? In article , justagrunt wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Object required error - Still perservering with this code - Ideas
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. -- Regards Bill "justagrunt" wrote: 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 -- Regards Bill |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Object required error - Still perservering with this code - Ideas
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) ? In article , justagrunt wrote: 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? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Object required error - Still perservering with this code - Id
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. -- Regards Bill "JE McGimpsey" wrote: 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) ? In article , justagrunt wrote: 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? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Object required error - Still perservering with this code - Id
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... In article , justagrunt wrote: 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. -- Regards Bill "JE McGimpsey" wrote: 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) ? In article , justagrunt wrote: 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Object Required Error 424 | Excel Programming | |||
| 424 Object required error | Excel Programming | |||
| Object Required Error | Excel Programming | |||
| Syntax Error Runtime Error '424' Object Required | Excel Programming | |||
Linear Mode
