Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I compare two data sheets to find variances? | Excel Discussion (Misc queries) | |||
FIND AND REPLACE DATA BETWEEN TWO EXCEL SHEETS USING FORMULA | Excel Discussion (Misc queries) | |||
Using INDEX and MATCH to find data in 2 different sheets | Excel Worksheet Functions | |||
how do i find data in multiple sheets in excel | Excel Discussion (Misc queries) | |||
how do i find data in multiple sheets in excel | Excel Worksheet Functions |