PRworker
asked on
Excel VBA, Sequential Number JE group, and move large ABS group to new tab
I have a list of "grouped" JE, and I want to sequentially number each "group". Most of the JE are a 2-10 rows, but there are some very large groups (up to 500 sometimes annually). In an Excel 2010 Macro (VBA) how can I number each group, when I have say 5-25 groups (it varies), which consists of anywhere from 2-500 rows (which also varies)?
Secondly, I would like to separate each "group" based on the ABS value amount, and move the groups >20k ABS to a separate worksheet, and also move groups <20k ABS to a separate worksheet. Again, the number of rows in each group and the number of groups vary.
Attached is a sample worksheet, which includes the numbering desired, as well as the tabs which are the desired end result, where the >20K are on one tab and the <20k are on the next tab. I am using an Excel Macro to perform to complete these tasks.
Thank you for your help beforehand!
JE-Numbering.xlsx
Secondly, I would like to separate each "group" based on the ABS value amount, and move the groups >20k ABS to a separate worksheet, and also move groups <20k ABS to a separate worksheet. Again, the number of rows in each group and the number of groups vary.
Attached is a sample worksheet, which includes the numbering desired, as well as the tabs which are the desired end result, where the >20K are on one tab and the <20k are on the next tab. I am using an Excel Macro to perform to complete these tasks.
Thank you for your help beforehand!
JE-Numbering.xlsx
ASKER
Thank you for the formula, it works! For the macro though, how does the macro know when to stop? In other words, since there is a blank row between each "group", how can the formula be written so the macro will stop filling downward when there are two blank rows?
Also, do you know how to move the large ABS group to another tab ?
Many thanks in advance for your time and assistance.
Also, do you know how to move the large ABS group to another tab ?
Many thanks in advance for your time and assistance.
It is a formula. You use it as needed, manually placing the formula into the cells that matter.
ASKER
Thank you for your response, however, it will not be manually run, as it will be part of a macro. The problem is the blank row and filling down in a macro.
Also, can anyone suggest how to move a whole group that has an ABS total that is large to a new tab?
Many thanks in advance for your help.
Also, can anyone suggest how to move a whole group that has an ABS total that is large to a new tab?
Many thanks in advance for your help.
Your VBA code will reference a range with a range object that contains all the cells in column B from B2 to the last used row. Then use the FillDown method for the range object.
I'm looking at a sane method for identifying the blocks of cells.
I'm looking at a sane method for identifying the blocks of cells.
ASKER
OK, I will use a reference range, and see how it works. Thank you!
I will await your suggestion for moving the second question to Accept the solution; would that be ok?
Many thanks for all your time and assistance in advance.
I will await your suggestion for moving the second question to Accept the solution; would that be ok?
Many thanks for all your time and assistance in advance.
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for PRworker's comment #a41259346
for the following reason:
I will accept the formula as one solution and post the second question as a separate question. Thank you for your time and assistance.
Accepted answer: 0 points for PRworker's comment #a41259346
for the following reason:
I will accept the formula as one solution and post the second question as a separate question. Thank you for your time and assistance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
HI Aikimark,
My apologies, I thought I had accepted the solution, and when I had not heard a response on the second part, I thought it may be best to post it as a separate question.
For the second question, I would prefer VBA as I am using a macro. Many thanks again for all your time and assistance!
My apologies, I thought I had accepted the solution, and when I had not heard a response on the second part, I thought it may be best to post it as a separate question.
For the second question, I would prefer VBA as I am using a macro. Many thanks again for all your time and assistance!
Open in new window