Error91- What looks wrong with this code? - ideas?

J

justagrunt

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
 
R

RB Smissaert

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
 
J

justagrunt

Thanks,
Will investigate.
--
Regards
Bill


RB Smissaert said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top