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

aikimark

Place this formula in B2 and filldown

=IF(ISNUMBER(C2),IF(ISNUMBER(C1),B1+1,1),"")

Open in new window

PRworker

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.

aikimark

It is a formula. You use it as needed, manually placing the formula into the cells that matter.

PRworker

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.

aikimark

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.

PRworker

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.

PRworker

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.

ASKER CERTIFIED SOLUTION

aikimark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PRworker

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!