find data on 2 different sheets?
 

Hello,

I'm creating a little program for my job.

What I want to create is to compare 2 different sheets.

One sheet contains a number of accounts and some figures
of 2002.
The other sheet also contains a number of accounts and
some figures of
2003.

What I want to do is the following:

I want that my macro takes the first account of the first
sheet. He
needs to search this account on the second sheet.

If he can find the account on the second sheet, the macro
needs to put
the figure of 2002 next to the figure of 2003.

If he CAN'T find the account, the macro needs to create a
line on the
second sheet (2003) and he needs to copy the figure of
2002 on that
sheet.


I can arrange the first thing, but I can't manage the
second step when
an account exists on one sheet and not on the other
sheet.

Can somebody please contact me so that I can sent him an
exemple of my
macro?


Thanks in advance


Chris

find data on 2 different sheets?
 

A private email exchange suggested this:

Option Explicit
Sub testme01()

Dim wksParent As Worksheet
Dim wksChild As Worksheet
Dim rngParent As Range
Dim rngChild As Range
Dim myCell As Range
Dim res As Variant

Set wksParent = Worksheets("2002")
Set wksChild = Worksheets("2003")

With wksParent
Set rngParent = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With wksChild
Set rngChild = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In rngParent.Cells
res = Application.Match(myCell.Value, rngChild, 0)
If IsError(res) Then
'no match
With wksChild
With .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
.Value = myCell.Value
.Offset(0, 2).Value = myCell.Offset(0, 2).Value 'column c?
End With
End With
Else
myCell.Offset(0, 3).Value = rngChild(res).Offset(0, 2).Value
End If
Next myCell

End Sub

Chris Vandecasteele wrote:

Hello,

I'm creating a little program for my job.

What I want to create is to compare 2 different sheets.

One sheet contains a number of accounts and some figures
of 2002.
The other sheet also contains a number of accounts and
some figures of
2003.

What I want to do is the following:

I want that my macro takes the first account of the first
sheet. He
needs to search this account on the second sheet.

If he can find the account on the second sheet, the macro
needs to put
the figure of 2002 next to the figure of 2003.

If he CAN'T find the account, the macro needs to create a
line on the
second sheet (2003) and he needs to copy the figure of
2002 on that
sheet.

I can arrange the first thing, but I can't manage the
second step when
an account exists on one sheet and not on the other
sheet.

Can somebody please contact me so that I can sent him an
exemple of my
macro?

Thanks in advance

Chris



--

Dave Peterson