Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB question- please help!
This probably sounds confusing on what I need. I will try to explain a little
bit better. The first quarter my company buys accounts receivable and scores them by risk. There are numerous fields that identify the criteria. To test to see if we are scoring them correctly we would like to look back and see why we made the decision to buy it and if it was a good decision. So in my first sheet, as I said, if I started with 5000 accounts after 8 quarters I still want to have 5000 accounts, regardless of whether they were paid off. So I can look at them and see how we scored them. All information from column A-AK will never change, columns AL-BA would have information, times past due- etc that would change during the life of the account. (It's a pretty big file - which is one reason I need the macro)The common field in every quarter (or sheet) would be the note number, which I have in column B, starting in row 2 - which will be the same column and row on every sheet . I want it to find this number and if it is still on the newest sheet (or newest quarter) copy the data (from AL-BA, or if there is a better way to see if something has changed??)and carry the new information to the first sheet if not do nothing. So every quarter I can download the newest quarter, run the macro and it would update the account. Like I said something I am having trouble with is I have to make sure if the original month started with 5000 accounts at the end of the 8th quarter I still have to have 5000 accounts. Make sense?? Is this possible? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB question- please help!
Sub copydata()
Dim rng1 as Range, rng2 as Range Dim cell as Range with Worbooks("Book1.xls").Worksheets(1) set rng1 = .Range(.Cells(2,"B"),.Cells(rows.count,2).End(xlup )) End With With Workbooks("Book2.xls").Worksheets(1) set rng2 = .Range(.Cells(2,"B"),.Cells(rows.count,2).End(xlup )) End With for each cell in rng1 res = application.Match(cell.Value,rng2,0) if not iserror(res) then rng2.offset(0,36).Resize(1,16).copy _ Destination:=cell.offset(0,36) end if Next End Sub would be a start. Change Book1 and Book2 to match your requirements/situation. The data is copied from Book2 to Book1 in the code. -- Regards, Tom Ogilvy "Marie" wrote in message ... This probably sounds confusing on what I need. I will try to explain a little bit better. The first quarter my company buys accounts receivable and scores them by risk. There are numerous fields that identify the criteria. To test to see if we are scoring them correctly we would like to look back and see why we made the decision to buy it and if it was a good decision. So in my first sheet, as I said, if I started with 5000 accounts after 8 quarters I still want to have 5000 accounts, regardless of whether they were paid off. So I can look at them and see how we scored them. All information from column A-AK will never change, columns AL-BA would have information, times past due- etc that would change during the life of the account. (It's a pretty big file - which is one reason I need the macro)The common field in every quarter (or sheet) would be the note number, which I have in column B, starting in row 2 - which will be the same column and row on every sheet . I want it to find this number and if it is still on the newest sheet (or newest quarter) copy the data (from AL-BA, or if there is a better way to see if something has changed??)and carry the new information to the first sheet if not do nothing. So every quarter I can download the newest quarter, run the macro and it would update the account. Like I said something I am having trouble with is I have to make sure if the original month started with 5000 accounts at the end of the 8th quarter I still have to have 5000 accounts. Make sense?? Is this possible? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB question- please help!
Tom,
I guess I didn't explain that there are multiple note numbers in column B. The first number starts in column B row 2. There are 5000 different numbers that I need the code to look for on every sheet and then match. The code worked but it copied everything it found in column B row 2 to every account. I guess I didn't explain it well enough. Do I need to name a range for the column the note numbers are in? I have tried changing your code to pick up the entire column of numbers with no luck. Could you please help, again? Thanks Marie "Tom Ogilvy" wrote: Sub copydata() Dim rng1 as Range, rng2 as Range Dim cell as Range with Worbooks("Book1.xls").Worksheets(1) set rng1 = .Range(.Cells(2,"B"),.Cells(rows.count,2).End(xlup )) End With With Workbooks("Book2.xls").Worksheets(1) set rng2 = .Range(.Cells(2,"B"),.Cells(rows.count,2).End(xlup )) End With for each cell in rng1 res = application.Match(cell.Value,rng2,0) if not iserror(res) then rng2.offset(0,36).Resize(1,16).copy _ Destination:=cell.offset(0,36) end if Next End Sub would be a start. Change Book1 and Book2 to match your requirements/situation. The data is copied from Book2 to Book1 in the code. -- Regards, Tom Ogilvy "Marie" wrote in message ... This probably sounds confusing on what I need. I will try to explain a little bit better. The first quarter my company buys accounts receivable and scores them by risk. There are numerous fields that identify the criteria. To test to see if we are scoring them correctly we would like to look back and see why we made the decision to buy it and if it was a good decision. So in my first sheet, as I said, if I started with 5000 accounts after 8 quarters I still want to have 5000 accounts, regardless of whether they were paid off. So I can look at them and see how we scored them. All information from column A-AK will never change, columns AL-BA would have information, times past due- etc that would change during the life of the account. (It's a pretty big file - which is one reason I need the macro)The common field in every quarter (or sheet) would be the note number, which I have in column B, starting in row 2 - which will be the same column and row on every sheet . I want it to find this number and if it is still on the newest sheet (or newest quarter) copy the data (from AL-BA, or if there is a better way to see if something has changed??)and carry the new information to the first sheet if not do nothing. So every quarter I can download the newest quarter, run the macro and it would update the account. Like I said something I am having trouble with is I have to make sure if the original month started with 5000 accounts at the end of the 8th quarter I still have to have 5000 accounts. Make sense?? Is this possible? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB question- please help!
I think you need to spend some time understanding what the code does. It
only works on two worksheets as written. It loops through all the entries in column B of Book1, worksheet1 and then looks in column B of Book2, worksheet 2. If it finds a matching entry, it copies columns AL-BA of that row back to columns AL-BA of the row in Book1, worksheet 1. You can certainly alter it to search through multiple sheets of another workbooks or even multiple books with single or multiple sheets. -- Regards, Tom Ogilvy "Marie" wrote in message ... Tom, I guess I didn't explain that there are multiple note numbers in column B. The first number starts in column B row 2. There are 5000 different numbers that I need the code to look for on every sheet and then match. The code worked but it copied everything it found in column B row 2 to every account. I guess I didn't explain it well enough. Do I need to name a range for the column the note numbers are in? I have tried changing your code to pick up the entire column of numbers with no luck. Could you please help, again? Thanks Marie "Tom Ogilvy" wrote: Sub copydata() Dim rng1 as Range, rng2 as Range Dim cell as Range with Worbooks("Book1.xls").Worksheets(1) set rng1 = .Range(.Cells(2,"B"),.Cells(rows.count,2).End(xlup )) End With With Workbooks("Book2.xls").Worksheets(1) set rng2 = .Range(.Cells(2,"B"),.Cells(rows.count,2).End(xlup )) End With for each cell in rng1 res = application.Match(cell.Value,rng2,0) if not iserror(res) then rng2.offset(0,36).Resize(1,16).copy _ Destination:=cell.offset(0,36) end if Next End Sub would be a start. Change Book1 and Book2 to match your requirements/situation. The data is copied from Book2 to Book1 in the code. -- Regards, Tom Ogilvy "Marie" wrote in message ... This probably sounds confusing on what I need. I will try to explain a little bit better. The first quarter my company buys accounts receivable and scores them by risk. There are numerous fields that identify the criteria. To test to see if we are scoring them correctly we would like to look back and see why we made the decision to buy it and if it was a good decision. So in my first sheet, as I said, if I started with 5000 accounts after 8 quarters I still want to have 5000 accounts, regardless of whether they were paid off. So I can look at them and see how we scored them. All information from column A-AK will never change, columns AL-BA would have information, times past due- etc that would change during the life of the account. (It's a pretty big file - which is one reason I need the macro)The common field in every quarter (or sheet) would be the note number, which I have in column B, starting in row 2 - which will be the same column and row on every sheet . I want it to find this number and if it is still on the newest sheet (or newest quarter) copy the data (from AL-BA, or if there is a better way to see if something has changed??)and carry the new information to the first sheet if not do nothing. So every quarter I can download the newest quarter, run the macro and it would update the account. Like I said something I am having trouble with is I have to make sure if the original month started with 5000 accounts at the end of the 8th quarter I still have to have 5000 accounts. Make sense?? Is this possible? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB question- please help!
Disregard. See discussion in later thread.
-- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... I think you need to spend some time understanding what the code does. It only works on two worksheets as written. It loops through all the entries in column B of Book1, worksheet1 and then looks in column B of Book2, worksheet 2. If it finds a matching entry, it copies columns AL-BA of that row back to columns AL-BA of the row in Book1, worksheet 1. You can certainly alter it to search through multiple sheets of another workbooks or even multiple books with single or multiple sheets. -- Regards, Tom Ogilvy "Marie" wrote in message ... Tom, I guess I didn't explain that there are multiple note numbers in column B. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Macro/VB Question DDE Question | Excel Worksheet Functions | |||
where can I see my question and answer? Yesterday I ask a question | Excel Discussion (Misc queries) | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
The question is an excel question that I need to figure out howto do in excel. | Excel Worksheet Functions | |||
Question about {} | Excel Worksheet Functions |