# 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
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Place this formula in B2 and filldown
``````=IF(ISNUMBER(C2),IF(ISNUMBER(C1),B1+1,1),"")
``````
0
Author Commented:
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 ?

0
Commented:
It is a formula.  You use it as needed, manually placing the formula into the cells that matter.
0
Author Commented:
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?

0
Commented:
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.
0
Author Commented:
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?

0
Author Commented:
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.
0
Commented:
Please accept my comment (http:#a41250844 ) as the solution, since it is the comment with the formula.

I have been unable to use the directprecedents() collection.  It can be done with VBA, but I was hoping for a formula-only solution.
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
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!
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.