PDA

View Full Version : Divide number under certain conditions



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

Paul_Hossler
08-22-2018, 09:01 AM
Is this any different than

http://www.vbaexpress.com/forum/showthread.php?63439-VBA-program-to-find-the-number-15

mike scooter
08-22-2018, 09:06 AM
Yes, I was doing a search option which I now have and the calculation has changed.
Thanks
Mike

mike scooter
08-23-2018, 09:27 AM
To clarify the above a little:

I have a program that finds any pairs of numbers that has a 15 as the last number (eg. 1-15, 7-15 or 12-15 in a set of numbers. Where I’m really struggling is not so much how to divide but within certain conditions (below).




When 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 the number would be divided by 12 and the amount would be moved to the following 12 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 cells 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.

Aussiebear
08-23-2018, 06:39 PM
Why not just use a formula in Cell A35 =Mod(A35,12) to find the remainder of cell A35? If the value 12 was to change then simply link the value as a cell location as in =Mod(A35,A7) if A7 contained the value for A35 to be divided by.

mike scooter
08-24-2018, 10:15 AM
Thanks for your replay but I'm working on doing all this in a VBA program and sure not an expert on this but I think I have to continue using VBA.
Thank-you

Aussiebear
08-24-2018, 02:33 PM
Thanks for your replay but I'm working on doing all this in a VBA program
That痴 okay Mike, but let痴 see if we can get the results you are seeking using excels functions and then we can write the vba. I am a little unclear as to what it is that you are chasing, could you attach a sample worksheet that we can use?

mike scooter
08-27-2018, 01:32 PM
Sorry for the delay in getting back to you. The sample workbook is in an above posting that I'm using.This really is a test of effective communication. Okay, your suggestion of "Why not just use a formula in Cell A35 =Mod(A35,12) to find the remainder of cell A35? If the value 12 was to change then simply link the value as a cell location as in =Mod(A35,A7) if A7 contained the value for A35 to be divided by" would work but the issue that dividing by 12 will never change so there is no need for the value 12 to change.

My example of cell C30 which contains 15.00 must be divided by 12 cells and only up to 12.00 (i know a lot of 12's in this example). So each cell A36,D36,G36,J36,M36,Q36,A40,D40G40,J40,M40,O40 would receive 1.00 for a total of 12.00 and the remaining 3.00 would go to D40 (see above for better explanation, not enough room here). Please let me know if is clear or I need to do a better job.
Thanks
Mike

Kenneth Hobs
08-29-2018, 08:08 AM
This thread and your others could have been solved more quickly had you attached an example workbook. To do that, click Go Advanced button in lower right of reply and then click the paperclip icon in the toolbar or Manage Attachments hyperlink below reply box. From there, it is easy to browse and upload the file. Some don't like to open attachments but I do, I'm lazy...

For future code posts, beside typing the html code tags as explained in the cross-post https://www.excelguru.ca/forums/showthread.php?9335-Divide-cell-number, you can click the # icon in the toolbar to add that html tag quickly.

It looks like a Select Case method with use of MOD might be of use.

mike scooter
08-29-2018, 10:01 AM
On my screen I see the sample Excel spreadsheet in the original posting but I will gladly do so again.
Mike

NoSparks
08-29-2018, 11:05 AM
That's not a spreadsheet, it's a picture of a spreadsheet.

mike scooter
09-13-2018, 08:57 AM
Hi

I'm really sorry for the delay in getting back to you. Attached is my workbook.
Mike