Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error91- What looks wrong with this code? - ideas?
Hi,
I get an error 91 - Object variable or with block variable not set - at line; Set rng10 = rng6.Offset(0, 20) ' 0 cells down 20 cells across is the offset which is part of; Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Dim rng4 As Range Dim rng5 As Range Dim rng6 As Range Dim rng10 As Range Dim rng11 As Range Dim cell As Range Dim res As Variant Dim z As Variant Sheet2.Visible = True Set rng1 = Sheet2.Range("E2:E100") 'Vertical range for new Imported Jobnumbers Sheet1.Visible = True Set rng2 = Sheet1.Range("F2:F60000") ' Vertical Range on main sheet to be added to 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 Else ' they don't match End If ' continue the search Next Any ideas. Dazed and frustrated at present - can't see why its doing it -- Regards Bill |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error91- What looks wrong with this code? - ideas?
Probably caused by
Set rng6 = Sheet1.Range("F2:F60000").Find(what:=z) being unsuccessfull, so rng6 is nothing. So, you will have to check for that and take action accordingly. RBS "justagrunt" wrote in message ... Hi, I get an error 91 - Object variable or with block variable not set - at line; Set rng10 = rng6.Offset(0, 20) ' 0 cells down 20 cells across is the offset which is part of; Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Dim rng4 As Range Dim rng5 As Range Dim rng6 As Range Dim rng10 As Range Dim rng11 As Range Dim cell As Range Dim res As Variant Dim z As Variant Sheet2.Visible = True Set rng1 = Sheet2.Range("E2:E100") 'Vertical range for new Imported Jobnumbers Sheet1.Visible = True Set rng2 = Sheet1.Range("F2:F60000") ' Vertical Range on main sheet to be added to 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 Else ' they don't match End If ' continue the search Next Any ideas. Dazed and frustrated at present - can't see why its doing it -- Regards Bill |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error91- What looks wrong with this code? - ideas?
Thanks,
Will investigate. -- Regards Bill "RB Smissaert" wrote: Probably caused by Set rng6 = Sheet1.Range("F2:F60000").Find(what:=z) being unsuccessfull, so rng6 is nothing. So, you will have to check for that and take action accordingly. RBS "justagrunt" wrote in message ... Hi, I get an error 91 - Object variable or with block variable not set - at line; Set rng10 = rng6.Offset(0, 20) ' 0 cells down 20 cells across is the offset which is part of; Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Dim rng4 As Range Dim rng5 As Range Dim rng6 As Range Dim rng10 As Range Dim rng11 As Range Dim cell As Range Dim res As Variant Dim z As Variant Sheet2.Visible = True Set rng1 = Sheet2.Range("E2:E100") 'Vertical range for new Imported Jobnumbers Sheet1.Visible = True Set rng2 = Sheet1.Range("F2:F60000") ' Vertical Range on main sheet to be added to 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 Else ' they don't match End If ' continue the search Next Any ideas. Dazed and frustrated at present - can't see why its doing it -- Regards Bill |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
This Macro halts any ideas what could be wrong | Excel Worksheet Functions | |||
Object required error - Still perservering with this code - Ideas | Excel Programming | |||
This code won't work, any ideas? | Excel Programming | |||
Can someone tell me what's wrong with this code please? | Excel Programming | |||
What is wrong with this code? | Excel Programming |