mike scooter
08-13-2018, 11:04 AM
Hi
I’m trying to develop a small VBA program to help find a number and if positive divide the number. I’ve looked around but can’t find any software to solve this problem. I using Excel 2010, Windows 7 software.
Wondering if someone could look at it and provide any assistance. Thank-you very much.
1) I need a program to find any pairs of numbers that has a 15 (could be more than one in range of numbers) in the last number (eg. 8-15 or 1-15) in the following cells B30,E30,H30,K30,N30. If there is NO positive result end the program.
If it finds a 15 two things must happen:
1- As an example cell B30 contains “8-15” and the adjacent cell C30 contain the number 2.20.
Beside each of the above search cells, each cell has an adjacent cell as follows: C30, F30, I30, L30. O30 which contain numbers. In a positive result only, the numbers in these cells must be divided equally by 12 (rounding up). The only issue is the amount to be divided can not exceed 12.00 (after 12.00 see below). Up to 12.00 it would be divided (by 1 in this case) and the amount would be moved to the following cells (add to any existing amount in the cell): A36,D36,G36,J36,M36,Q36,A40,D40G40,J40,M40,O40.
The program would then move any excess over 12.00 via referencing the first number (of the set) in cell B30 (8 in this example) and search the following 12 cells to find 8:
1 is located in cell A35, place the EXCESS number in cell A36
2 is located in cell D35, place the EXCESS number in cell D36
3 is located in cell G35, place the EXCESS number in cell G36
4 is located in cell J35, place the EXCESS number in cell J36
5 is located in cell M35, place the EXCESS number in cell M36
6 is located in cell Q35, place the EXCESS number in cell Q36
7 is located in cell A39, place the EXCESS number in cell A40
8 is located in cell D39, place the EXCESS number in cell D40
9 is located in cell G39, place the EXCESS number in cell G40
10 is located in cell J39, place the EXCESS number in cell J40
11 is located in cell M39, place the EXCESS number in cell M40
12 is located in cell O39, place the EXCESS number in cell O40
The correct cell for 8-15 it in D39. The program would then put the EXCESS number in the cell below D40 in this case.
After this operation there are no contents in cell C30.
2- After a 15 positive result I also need the program to reference the following number in the following 5 cells:
Cell B30 would reference cell B32 for the number 1
Cell E30 would reference cell B32 for the number 2
Cell H30 would reference cell B32 for the number 3
Cell K30 would reference cell B32 for the number 4
Cell N30 would reference cell B32 for the number 5
Using example B30 the reference number is 1 so the program would search the range in column D1:D12 (see below) for the number 1 and move the set of numbers to the adjunct cell (see below) and increase the last digit, so it would become 8-16 in cell number E1, in this example. After this operation there are no contents in cell B30.
D1 = 1 adjacent cell E1
D2 = 2 adjacent cell E2
D3 = 3 adjacent cell E3
D4 = 4 adjacent cell E4
D5 = 5 adjacent cell E5
D6 = 6 adjacent cell E6
D7 = 7 adjacent cell E7
D8 = 8 adjacent cell E8
D9 = 9 adjacent cell E9
D10 = 10 adjacent cell E10
D11 = 11 adjacent cell E11
D12 = 12 adjacent cell E12
That’s the end of the program. The above is an example and if anything is not clear please allow me to assist you.
I’m trying to develop a small VBA program to help find a number and if positive divide the number. I’ve looked around but can’t find any software to solve this problem. I using Excel 2010, Windows 7 software.
Wondering if someone could look at it and provide any assistance. Thank-you very much.
1) I need a program to find any pairs of numbers that has a 15 (could be more than one in range of numbers) in the last number (eg. 8-15 or 1-15) in the following cells B30,E30,H30,K30,N30. If there is NO positive result end the program.
If it finds a 15 two things must happen:
1- As an example cell B30 contains “8-15” and the adjacent cell C30 contain the number 2.20.
Beside each of the above search cells, each cell has an adjacent cell as follows: C30, F30, I30, L30. O30 which contain numbers. In a positive result only, the numbers in these cells must be divided equally by 12 (rounding up). The only issue is the amount to be divided can not exceed 12.00 (after 12.00 see below). Up to 12.00 it would be divided (by 1 in this case) and the amount would be moved to the following cells (add to any existing amount in the cell): A36,D36,G36,J36,M36,Q36,A40,D40G40,J40,M40,O40.
The program would then move any excess over 12.00 via referencing the first number (of the set) in cell B30 (8 in this example) and search the following 12 cells to find 8:
1 is located in cell A35, place the EXCESS number in cell A36
2 is located in cell D35, place the EXCESS number in cell D36
3 is located in cell G35, place the EXCESS number in cell G36
4 is located in cell J35, place the EXCESS number in cell J36
5 is located in cell M35, place the EXCESS number in cell M36
6 is located in cell Q35, place the EXCESS number in cell Q36
7 is located in cell A39, place the EXCESS number in cell A40
8 is located in cell D39, place the EXCESS number in cell D40
9 is located in cell G39, place the EXCESS number in cell G40
10 is located in cell J39, place the EXCESS number in cell J40
11 is located in cell M39, place the EXCESS number in cell M40
12 is located in cell O39, place the EXCESS number in cell O40
The correct cell for 8-15 it in D39. The program would then put the EXCESS number in the cell below D40 in this case.
After this operation there are no contents in cell C30.
2- After a 15 positive result I also need the program to reference the following number in the following 5 cells:
Cell B30 would reference cell B32 for the number 1
Cell E30 would reference cell B32 for the number 2
Cell H30 would reference cell B32 for the number 3
Cell K30 would reference cell B32 for the number 4
Cell N30 would reference cell B32 for the number 5
Using example B30 the reference number is 1 so the program would search the range in column D1:D12 (see below) for the number 1 and move the set of numbers to the adjunct cell (see below) and increase the last digit, so it would become 8-16 in cell number E1, in this example. After this operation there are no contents in cell B30.
D1 = 1 adjacent cell E1
D2 = 2 adjacent cell E2
D3 = 3 adjacent cell E3
D4 = 4 adjacent cell E4
D5 = 5 adjacent cell E5
D6 = 6 adjacent cell E6
D7 = 7 adjacent cell E7
D8 = 8 adjacent cell E8
D9 = 9 adjacent cell E9
D10 = 10 adjacent cell E10
D11 = 11 adjacent cell E11
D12 = 12 adjacent cell E12
That’s the end of the program. The above is an example and if anything is not clear please allow me to assist you.