debug error: object variable not set

Can someone help me with this macro please.. I keep getting an error towards
the end of the macro.. its the error "object variable or with block variable
not set" , im not sure what to do from here..


Option Explicit

Sub put_next_to_list()
Dim rng As Range
Dim r1 As Long
Dim r2 As Long
Dim r3 As Long
Dim cell As Variant
Dim FR As Long 'first row
Dim LR As Long 'last row
Dim i As Long
Dim ssh As Object 'source sheet
Dim tsh As Object 'target sheet

Set ssh = Sheets(9)
Set tsh = Sheets(10)

FR = 1
LR = tsh.Cells(65536, 1).End(xlUp).Row
Set rng = tsh.Range(tsh.Cells(FR, 1), tsh.Cells(LR, 1))

For Each cell In rng
r1 = 0: r2 = 0: r3 = 0
r1 = ssh.Columns(1).Find(cell.Offset(0, 0)).Row
r2 = ssh.Columns(2).Find(cell.Offset(0, 1)).Row
r3 = ssh.Columns(3).Find(cell.Offset(0, 2)).Row
If r1 = 0 Or r1 <> r2 Or r1 <> r3 Then
Else: cell.Offset(0, 6) = ssh.Cells(r1, 7)
End If
Next cell
End Sub

Could someone please help me.. thankyou

Michael
Not checked it properly but I would guess one of your finds is not finding
anything so returns nothing, you then try to get the row (of nothing).
You could try
on error resume next
at the top of the routine to ignore errors

cheers
Simon
"Michael A" wrote:

> Can someone help me with this macro please.. I keep getting an error towards
> the end of the macro.. its the error "object variable or with block variable
> not set" , im not sure what to do from here..
>
>
> Option Explicit
>
> Sub put_next_to_list()
> Dim rng As Range
> Dim r1 As Long
> Dim r2 As Long
> Dim r3 As Long
> Dim cell As Variant
> Dim FR As Long 'first row
> Dim LR As Long 'last row
> Dim i As Long
> Dim ssh As Object 'source sheet
> Dim tsh As Object 'target sheet
>
> Set ssh = Sheets(9)
> Set tsh = Sheets(10)
>
> FR = 1
> LR = tsh.Cells(65536, 1).End(xlUp).Row
> Set rng = tsh.Range(tsh.Cells(FR, 1), tsh.Cells(LR, 1))
>
> For Each cell In rng
> r1 = 0: r2 = 0: r3 = 0
> r1 = ssh.Columns(1).Find(cell.Offset(0, 0)).Row
> r2 = ssh.Columns(2).Find(cell.Offset(0, 1)).Row
> r3 = ssh.Columns(3).Find(cell.Offset(0, 2)).Row
> If r1 = 0 Or r1 <> r2 Or r1 <> r3 Then
> Else: cell.Offset(0, 6) = ssh.Cells(r1, 7)
> End If
> Next cell
> End Sub
>
> Could someone please help me.. thankyou

Simon, thanks for responding. I did put that back in there, the issue im
running into now is that it isn't returning everything it should. Im not very
good at VB so im sure this could be streamlined allot better, but not sure
how to do it. What it should be doing is matching up any columns abc on the
first sheet to any abc on the second sheet that are the same and copying over
the value in G to the second sheets line. Its not working quite right
though..

"Simon Murphy" wrote:

> Michael
> Not checked it properly but I would guess one of your finds is not finding
> anything so returns nothing, you then try to get the row (of nothing).
> You could try
> on error resume next
> at the top of the routine to ignore errors
>
> cheers
> Simon
> "Michael A" wrote:
>
> > Can someone help me with this macro please.. I keep getting an error towards
> > the end of the macro.. its the error "object variable or with block variable
> > not set" , im not sure what to do from here..
> >
> >
> > Option Explicit
> >
> > Sub put_next_to_list()
> > Dim rng As Range
> > Dim r1 As Long
> > Dim r2 As Long
> > Dim r3 As Long
> > Dim cell As Variant
> > Dim FR As Long 'first row
> > Dim LR As Long 'last row
> > Dim i As Long
> > Dim ssh As Object 'source sheet
> > Dim tsh As Object 'target sheet
> >
> > Set ssh = Sheets(9)
> > Set tsh = Sheets(10)
> >
> > FR = 1
> > LR = tsh.Cells(65536, 1).End(xlUp).Row
> > Set rng = tsh.Range(tsh.Cells(FR, 1), tsh.Cells(LR, 1))
> >
> > For Each cell In rng
> > r1 = 0: r2 = 0: r3 = 0
> > r1 = ssh.Columns(1).Find(cell.Offset(0, 0)).Row
> > r2 = ssh.Columns(2).Find(cell.Offset(0, 1)).Row
> > r3 = ssh.Columns(3).Find(cell.Offset(0, 2)).Row
> > If r1 = 0 Or r1 <> r2 Or r1 <> r3 Then
> > Else: cell.Offset(0, 6) = ssh.Cells(r1, 7)
> > End If
> > Next cell
> > End Sub
> >
> > Could someone please help me.. thankyou