Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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



  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2,824
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how can I compare two data sheets to find variances? lizm Excel Discussion (Misc queries) 1 December 2nd 09 12:49 AM
FIND AND REPLACE DATA BETWEEN TWO EXCEL SHEETS USING FORMULA gkb Excel Discussion (Misc queries) 4 December 7th 06 10:41 AM
Using INDEX and MATCH to find data in 2 different sheets RMF Excel Worksheet Functions 5 February 1st 06 02:02 PM
how do i find data in multiple sheets in excel Karline Excel Discussion (Misc queries) 2 May 13th 05 03:33 PM
how do i find data in multiple sheets in excel Karline Excel Worksheet Functions 2 May 13th 05 03:26 PM


All times are GMT +1. The time now is 09:16 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"