VBA to subtract Cell A value from Cell B value until Cell B is 0, BUT.........

[idontknowhow]

Hi all! I need some real big help, as this has been driving me crazy.

I have 28 columns, with Column R containing a number (ie R2). I need to subtract the R2 value number from a cell that has a total value number in AB2. After Row 2 has R2 subtracted from AB2, I need to copy/paste that whole row to another sheet, delete Row 2, then repeat (loop) that process for Row 3, 4, 5, etc.. until AB2 equals 0 from the continuous subtraction from Column R values.

Any help or suggestions would be appreciated!!

 

[StephenCrump]

Welcome to the Forum!

Sorry you've had no responses. Usually that indicates a question is not clear, or it's too complicated.

I'm far from clear what you're asking. But here's my guess based on what you've said so far. Cell AB2 contains a number, let's say 32. In Column R you have these values, say:

Book
	R
1
2
3
4
5	5
6	2
7	7
8	8
9	6
10	4
11	9
12	12
Sheet1

You want your code to copy rows 5:10 to another sheet (and delete them in this sheet) because these R values add to 32, i.e. if you consecutively took them away from AB2, you'd be left with nil?

Bearing in mind that we have no idea what's in your workbook, it's always helpful if you can post a screenshot showing sample data, and the results you'd like to see, preferably using the XL2BB add-in, so that we can copy directly into a test workbook.

 

[idontknowhow]

Sorry, probably having an attachment would work better to explain. So I need to start at Row2/Col B (hours), subtract that 'hour value' from Row1/Col E (200), then copy/paste that Row to another sheet, then delete that Row on this sheet, then continue through that process down the Rows until 200 is 'nil'.

In this example, Data 1-17 and 21 would be used, since they total 200. Data 1-17 equals 193, so the macro would skip Data 18-20, then chose Data 21 to make the total 'nil'.

Column A (Data)	Column B (Hours)	Column C (Details)	Column D	200
Data 1	13	Details
Data 2	12	Details
Data 3	11	Details
Data 4	9	Details
Data 5	12	Details
Data 6	11	Details
Data 7	14	Details
Data 8	9	Details
Data 9	10	Details
Data 10	12	Details
Data 11	12	Details
Data 12	12	Details
Data 13	11	Details
Data 14	11	Details
Data 15	10	Details
Data 16	12	Details
Data 17	12	Details
Data 18	12	Details
Data 19	12	Details
Data 20	12	Details
Data 21	7	Details

Thanks again for looking at this!

 

[StephenCrump]

Sorry, probably having an attachment would work better to explain ....

Yes, thanks for that.

You've given us one simple example, but I suspect the possibilities may be more complex.

Consider the following:

	A	B	C	D	E	F	G	H	I
1					Required		Option 1	Option 2	Option 3
2	Data	Hours	Details		60		60	60	60
3	Data 1	10					10	10	10
4	Data 2	10					10	10	10
5	Data 3	10					10	10	10
6	Data 4	10					10	10	10
7	Data 5	10					10	10	10
8	Data 6	8					8
9	Data 7	5							5
10	Data 8	5							5
11	Data 9	10						10
12	Data 10	10
13	Data 11	10
14	Data 12	2					2
15	Data 13	10
16	Data 14	10
Sheet1

Cell Formulas
Range	Formula
G2:I2	G2	=SUM(G3:G16)

Using your approach, Option 1 would be the solution. But if it's a requirement to take Data1, Data2, Data 3 ... in order, perhaps Option 2 or 3 would be preferred?

Also, what happens if it's not possible to exactly add to the required amount?

With VBA, the code will need to know exactly how to behave in all these possible circumstances. This could get complicated, and require more time than a volunteer forum is willing to put in.