Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing info between worksheets
I could really use some help with a macro. It has been several years
since I did any macro programming and I feel completely lost. I have recently volunteered my services to the local waterboard to help them with their books. They are really a mess. One Excel file has a worksheet with all the original water accounts then a second worksheet that has all the current billing information, this account is always updated. The problem is that no one ever updated the Main Accounts worksheet or otherwise known as the Original accounts worksheet. So here is the problem: Worksheet that is named "Main accounts" Columns A: Person's name, Column C: Account number Column E: Address Worksheet that is named "Current accounts" Column A: Person's name, Column D: Account numbers Column G: Address What I was trying to do unsuccessfully was create a macro that would go through the worksheet named "Main accounts" check the account numbers in Column C (Account number column) and look at the name that is in Column A for that account number. It would then compare the name that is assigned to the account number with the name in the worksheet "Current accounts" and if the name was different it would then put the current name for that account number on the Main accounts worksheet. One extra part if possible would be to add the correct address from the worksheet named "Current accounts" to the address column on the "Main accounts" worksheet in column E. One of my fears is that someone might have added accounts to the Current worksheet and not include it on the Main worksheet. I would really appreciate some help with this. Also if you could recommend a website or book that would help me brush up on my programming skills I would truly appreciate it. Thank you LeeLee |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing info between worksheets
the code coul;d be simplified a little bit. You don't need to test the
account names are different. Just copy the account name from Current Account to Main Accounts. Sub checkaccounts() Sheets("Current accounts").Activate LastRowCurrent = Sheets("Current accounts").Cells(Rows.Count, "D"). _ End(xlUp).Row Set searchrange = Sheets("Current accounts"). _ Range(Cells(1, "D"), Cells(LastRowCurrent, "D")) LastRowMain = Sheets("Main accounts").Cells(Rows.Count, "C"). _ End(xlUp).Row Sheets("Main accounts").Activate Set MainRange = Sheets("Main accounts"). _ Range(Cells(1, "C"), Cells(LastRowCurrent, "C")) For Each cell In MainRange Set c = searchrange.Find(cell, LookIn:=xlValues) If Not c Is Nothing Then If Sheets("Main accounts").Cells(cell.Row, "A") < _ Sheets("Current accounts").Cells(c.Row, "A") Then Sheets("Main accounts").Cells(cell.Row, "A") = _ Sheets("Current accounts").Cells(c.Row, "A") End If Sheets("Main accounts").Cells(cell.Row, "E") = _ Sheets("Current accounts").Cells(c.Row, "G") End If Next cell " wrote: I could really use some help with a macro. It has been several years since I did any macro programming and I feel completely lost. I have recently volunteered my services to the local waterboard to help them with their books. They are really a mess. One Excel file has a worksheet with all the original water accounts then a second worksheet that has all the current billing information, this account is always updated. The problem is that no one ever updated the Main Accounts worksheet or otherwise known as the Original accounts worksheet. So here is the problem: Worksheet that is named "Main accounts" Columns A: Person's name, Column C: Account number Column E: Address Worksheet that is named "Current accounts" Column A: Person's name, Column D: Account numbers Column G: Address What I was trying to do unsuccessfully was create a macro that would go through the worksheet named "Main accounts" check the account numbers in Column C (Account number column) and look at the name that is in Column A for that account number. It would then compare the name that is assigned to the account number with the name in the worksheet "Current accounts" and if the name was different it would then put the current name for that account number on the Main accounts worksheet. One extra part if possible would be to add the correct address from the worksheet named "Current accounts" to the address column on the "Main accounts" worksheet in column E. One of my fears is that someone might have added accounts to the Current worksheet and not include it on the Main worksheet. I would really appreciate some help with this. Also if you could recommend a website or book that would help me brush up on my programming skills I would truly appreciate it. Thank you LeeLee |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing info on 2 spreadsheets | Excel Worksheet Functions | |||
Comparing Two Columns on Two Worksheets and then Adding New Info. | Excel Programming | |||
Comparing info | New Users to Excel | |||
Comparing files and extracting info. | Excel Worksheet Functions | |||
Comparing worksheets (extra info) | Excel Programming |