im so close, someone please help

this is soo close to working, can someone pls help...


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
On Error Resume Next
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

The cells im trying to compare are text. Im trying to find duplicates on two
different pages by comparing columns abc, and then when I find a match
populate the G column on the new page with what was on the previous page...it
dosn't work all the time, only 30% of the duplicates copy over.. and i know
they are exact.. cause i typed them in to test this.. someone please respond.

try changing
Set rng = tsh.Range(tsh.Cells(FR, 1), tsh.Cells(LR, 1))

to:
Set rng = tsh.Range(Cells(FR, 1), Cells(LR, 1))


"Michael A" wrote:

> this is soo close to working, can someone pls help...
>
>
> 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
> On Error Resume Next
> 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
>
> The cells im trying to compare are text. Im trying to find duplicates on two
> different pages by comparing columns abc, and then when I find a match
> populate the G column on the new page with what was on the previous page...it
> dosn't work all the time, only 30% of the duplicates copy over.. and i know
> they are exact.. cause i typed them in to test this.. someone please respond.

See one response at your first post--not the second post.

Michael A wrote:
>
> this is soo close to working, can someone pls help...
>
> 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
> On Error Resume Next
> 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
>
> The cells im trying to compare are text. Im trying to find duplicates on two
> different pages by comparing columns abc, and then when I find a match
> populate the G column on the new page with what was on the previous page...it
> dosn't work all the time, only 30% of the duplicates copy over.. and i know
> they are exact.. cause i typed them in to test this.. someone please respond.

--

Dave Peterson

That means that those cells(fr,1) and cells(lr,1) will be unqualified. They'll
belong to the activesheet instead of TSH.

Better to not change these.

But if you want to change them to save typing, I like this style:

with tsh
set rng = .range(.cells(fr,1),.cells(lr,1))
end with

those leading dots mean that those things belong to the previous With object
(tsh in this case).



gocush wrote:
>
> try changing
> Set rng = tsh.Range(tsh.Cells(FR, 1), tsh.Cells(LR, 1))
>
> to:
> Set rng = tsh.Range(Cells(FR, 1), Cells(LR, 1))
>
> "Michael A" wrote:
>
> > this is soo close to working, can someone pls help...
> >
> >
> > 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
> > On Error Resume Next
> > 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
> >
> > The cells im trying to compare are text. Im trying to find duplicates on two
> > different pages by comparing columns abc, and then when I find a match
> > populate the G column on the new page with what was on the previous page...it
> > dosn't work all the time, only 30% of the duplicates copy over.. and i know
> > they are exact.. cause i typed them in to test this.. someone please respond.

--

Dave Peterson