highlighting / selecting cells to a sum total using VBA - is it possible?

[SNA400]

Hi All

I am not sure if this is possible - but I know I am in the right place to ask!

I need to set up a macro to call some code to total up the number of cells in a column - but it needs to total no more than 100,000 before it starts again (sometimes the column may fully total 500,000+) - and I need it to show the total figure.
Is this possible??

it is just a simple number in the cell - nothing fancy.

If it's possible great - if not it's just another manual job we have to do

THANKS FOR YOUR HELP

Simon

 

[Joe4]

I don't know if it is quite clear (at least it is lacking some important details).
Can you provide more details, maybe walk us through an actual example of what you have and what you want to happen?

 

[SNA400]

HI

Not sure if this will help - below is a excel report from a system we use and we have to tally up the cartons column for our customs system - but the customs system has a limit of 99,999 cartons so I need the report to sort by container (not a problem) and then add up the carton column and give a figure at the end, but if the total of the column adds to more than 99,999 it needs to insert a row - total the cells above and then continue down adding the cells - doing this as many times as it needs to until the end of the file (figure in red below) now this may be ten lines long or 110 lines but it needs to calculate these correctly so we can enter the data.

is it possible?

Master Ref.	Container No.	Container Type	Ship Name	Origin	Destination	Carrier	Gross Weight	Vol/Cube Weight	Hazardous Cargo	Cartons
NYKSSGNS38574700	NYKU4250434	40ft HC GOH 2 Tier	OOCL EGYPT	HO CHI MINH	SOUTHAMPTON	NYK LINE (NIPPON YUS	1532.2	60	N	43700
NYKSSGNS38574700	TCLU5795459	20ft HC GOH 2 Tier	OOCL EGYPT	HO CHI MINH	SOUTHAMPTON	NYK LINE (NIPPON YUS	1526.8	60	N	43700
										87400
MOLU11041634336	TCLU1536660	40ft HC	OOCL EGYPT	NINGBO	SOUTHAMPTON	MITSUI O S K LINES L	7207.2	61.707	N	85800
MOLU11041634336	TCLU6433541	40ft HC	OOCL EGYPT	NINGBO	SOUTHAMPTON	MITSUI O S K LINES L	6843.3	60.7983	N	10220
										96020
MOLU11041634700	DRYU4294740	20ft	OOCL EGYPT	NINGBO	SOUTHAMPTON	MITSUI O S K LINES L	5037	53	N	690
MOLU11041634700	MOFU0618350	40ft	OOCL EGYPT	NINGBO	SOUTHAMPTON	MITSUI O S K LINES L	5037	53	N	690
MOLU11044333300	CAIU8675039	40ft HC	OOCL EGYPT	SHANGHAI	SOUTHAMPTON	MITSUI O S K LINES L	8887.8	68.8151	N	1808

<colgroup><col width="138" style="width: 104pt; mso-width-source: userset; mso-width-alt: 5046;"> <col width="95" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3474;"> <col width="121" style="width: 91pt; mso-width-source: userset; mso-width-alt: 4425;"> <col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;"> <col width="87" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3181;"> <col width="107" style="width: 80pt; mso-width-source: userset; mso-width-alt: 3913;"> <col width="157" style="width: 118pt; mso-width-source: userset; mso-width-alt: 5741;"> <col width="52" style="width: 39pt; mso-width-source: userset; mso-width-alt: 1901;"> <col width="64" style="width: 48pt;"> <col width="63" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2304;"> <col width="55" style="width: 41pt; mso-width-source: userset; mso-width-alt: 2011;"> <tbody> </tbody>

 

[SNA400]

Can anyone help with this - or am I asking to much of the software?

 

[Joe4]

Why is there a blank row in your second to last row?
Might there be blank rows/gaps in your data?
What column is "Cartons" in?
Does your data start on row 2?
What column are you wanting to sort by before totalling?
Do you want the total to be a formula, or just the hard-coded value?

 

[SNA400]

Hi Joe4

The blank is down to different Master Bill numbers

Yes - there may be data breaks (but they can be removed if it makes things easier)

the Cartons are in Column K

Yes - data starts at Row 2

this needs to be sorted alpha on column B

the total would be better if it is a formula as the 'cartons' may change due to the type of goods they are

Simon

 

[Joe4]

Try this:

Sub MyTotalMacro()
'
    Dim myLastRow As Long
    Dim myRow As Long
    Dim firstRow As Long
    Dim rng1 As Range
    Dim rng2 As Range

    Application.ScreenUpdating = False

'   Find last row in A
    myLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Sort data by column A
    Range("A1:K" & myLastRow).Sort Key1:=Range("A1"), Order1:=xlAscending, _
        Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        
'   Loop through all data starting in row 2
    firstRow = 2
    myRow = 2
    Do
'       Check to see if current row pushes total
        Set rng1 = Range(Cells(firstRow, "K"), Cells(myRow, "K"))
        Set rng2 = Range(Cells(firstRow, "K"), Cells(myRow + 1, "K"))
        If Application.WorksheetFunction.Sum(rng1) < 100000 And _
            Application.WorksheetFunction.Sum(rng2) >= 100000 Then
            Rows(myRow + 1).Insert
            Cells(myRow + 1, "K").FormulaR1C1 = "=SUM(R[-" & myRow - firstRow + 1 & "]C:R[-1]C)"
            myRow = myRow + 2
            firstRow = myRow
        Else
            myRow = myRow + 1
        End If
'   Loop until current row is blank
    Loop Until Cells(myRow, "A") = ""
    
'   Enter last sum
    Cells(myRow, "K").FormulaR1C1 = "=SUM(R[-" & myRow - firstRow & "]C:R[-1]C)"
    
    Application.ScreenUpdating = True

End Sub

 

[SNA400]

HI Joe4

That kind of works! it calculates to the point of where it enters a value but then its sending the rest of the data down to the bottom of the sheet (sheet is 1630 rows long).

Could I just add a loop in to rerun from the figure entry point or is that to complicated?

Simon

 

[Joe4]

That kind of works! it calculates to the point of where it enters a value but then its sending the rest of the data down to the bottom of the sheet (sheet is 1630 rows long).

Could I just add a loop in to rerun from the figure entry point or is that to complicated?

I am sorry, but I am not sure I follow what you are trying to tell me.
Do you have a lot of blank rows in there that are getting sorted to the top?