mike scooter
08-22-2018, 08:30 AM
Hi
I知 trying to develop a VBA program to divide the contents of specified cells under certain conditions. I知 using Excel 2010 on Sheet 2, Windows 7 software. I have a jpeg of the Excel spreadsheet at the bottom to see expected placement.
I'm learning VBA but have a long way to go and sure would appreciate any help to get me there.
Thank-you for any help you can provide.
Mike
The program below finds any pairs of numbers that has a 15 as the last number (eg. 1-15 or 12-15, if no 15 end program) in the set of numbers. Where I知 really struggling is not so much how to divide but within certain conditions (below).
Sub do_it()
Dim n, sht As Worksheet, cell As Range, num, tmp, rngDest As Range
Set sht = sheet2
For Each cell In sht.Range("B30,E30,H30,K30,N30").Cells
tmp = cell.Offset(0, 1).Value
If cell.Value = n And tmp Like "*#-15*" Then
'get the first number
num = CLng(Trim(Split(tmp, "-")(0)))
Debug.Print "Found a positive result in " & cell.Address
'find the next empty cell in the appropriate row
Set rngDest = sht.Cells(num, sht.Columns.Count).End(xlToLeft).Offset(0, 1)
Exit For
End If
Next
End Sub
If it finds a 15 two things must happen:
1- As an example, for below, cell B30 contains the number set 8-15 and the adjacent cell C30 contain the number 15.00.
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 (2 decimal places, 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 12 and the amount would be moved to the following cells (added to any existing amount in the cell already, for this example there was no amount in the cells): A36,D36,G36,J36,M36,Q36,A40,D40G40,J40,M40,O40. So in my above example C30, 15.00 would be divided by first dividing 12 (the max) into 12 which equals 1.00 (in this case) and the remaining 3.00 would be moved to the 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, 3.00 in this example, in the cell below D40 in this case.
After this operation there are no contents in cell C30.
22758
I知 trying to develop a VBA program to divide the contents of specified cells under certain conditions. I知 using Excel 2010 on Sheet 2, Windows 7 software. I have a jpeg of the Excel spreadsheet at the bottom to see expected placement.
I'm learning VBA but have a long way to go and sure would appreciate any help to get me there.
Thank-you for any help you can provide.
Mike
The program below finds any pairs of numbers that has a 15 as the last number (eg. 1-15 or 12-15, if no 15 end program) in the set of numbers. Where I知 really struggling is not so much how to divide but within certain conditions (below).
Sub do_it()
Dim n, sht As Worksheet, cell As Range, num, tmp, rngDest As Range
Set sht = sheet2
For Each cell In sht.Range("B30,E30,H30,K30,N30").Cells
tmp = cell.Offset(0, 1).Value
If cell.Value = n And tmp Like "*#-15*" Then
'get the first number
num = CLng(Trim(Split(tmp, "-")(0)))
Debug.Print "Found a positive result in " & cell.Address
'find the next empty cell in the appropriate row
Set rngDest = sht.Cells(num, sht.Columns.Count).End(xlToLeft).Offset(0, 1)
Exit For
End If
Next
End Sub
If it finds a 15 two things must happen:
1- As an example, for below, cell B30 contains the number set 8-15 and the adjacent cell C30 contain the number 15.00.
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 (2 decimal places, 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 12 and the amount would be moved to the following cells (added to any existing amount in the cell already, for this example there was no amount in the cells): A36,D36,G36,J36,M36,Q36,A40,D40G40,J40,M40,O40. So in my above example C30, 15.00 would be divided by first dividing 12 (the max) into 12 which equals 1.00 (in this case) and the remaining 3.00 would be moved to the 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, 3.00 in this example, in the cell below D40 in this case.
After this operation there are no contents in cell C30.
22758