Divide number under certain conditions

[mike scooter]

Hi


I’m trying to develop a VBA program to divide the contents of specified cells under certain conditions. I’m 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’m 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.




Capture.JPG

[Paul_Hossler]

Is this any different than

http://www.vbaexpress.com/forum/show...-the-number-15

[mike scooter]

Yes, I was doing a search option which I now have and the calculation has changed.
Thanks
Mike

[mike scooter]

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]

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]

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]

Thanks for your replay but I'm working on doing all this in a VBA program

That’s okay Mike, but let’s 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]

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]

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/show...de-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]

On my screen I see the sample Excel spreadsheet in the original posting but I will gladly do so again.
Mike

[NoSparks]

That's not a spreadsheet, it's a picture of a spreadsheet.

[mike scooter]

Hi

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