ANY HELP GREATLY APPRECIATED......

tuggers

Board Regular
Joined
Nov 10, 2005
Messages
145
Hi gang,

I work in a dairy and part of my job involves working in the gatehouse where we book tankers in and record there details.

The following is a sheet we use that i would like to improve if at all possible.
basically all the data is gained manually from tanker paperwork and weighbridge dockets. This is entered into the respective columns and the difference calculated in a third column. Then at the end of the shift the columns are totalled up (these can consist of 1 row or 100 rows)
The volumes created by the inputted data are then manually checked against another guaranteed source and the difference between the two recorded along with the totals.
Then finally on another part of the sheet we produce another calculation to show any milk that began pumping before and finished after midnight and the volume that was pumped after midnight is shown so it can be added to the following days sheet.
Has anybody got any ideas how it could be improved?
It currently requires a lot of copy and paste to get things into position, it would be great if a macro could be used for the arrangement and maybe some kind of conditional formatting may help?
Im a bit new to excel but learning fast, i really would appreciate your expert input.
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

tuggers

Board Regular
Joined
Nov 10, 2005
Messages
145
weighbridge reconciliation Jan 2005.xls
ABCDEFGHIJKLM
1SupplierDocketW/bridgeBC55DiffRouteCollectedB/F from previous day column M
2DFOB11115116,86816,857-1120330/01/2005Total B/F83500
3FMILK11115228,00427,995-930/01/2005
4DFOB11115327,16227,109-5330/01/2005DocketSilo No.Silo AllocationSilo AllocationPumped next day
5DFOB11115427,85927,9428330/01/200511115462696826,968
6DFOB11115528,42128,4816030/01/200511115528,421
7DFOB11115628,11128,052-5930/01/200511115628,111
8DFOB11115927,83027,807-2320730/01/2005C/F Total to next day column M83,500
9
10TOTALS184,255184,243-12
11net litresQualtrace184,2540
12Diff1
31
 

tuggers

Board Regular
Joined
Nov 10, 2005
Messages
145
I forgot to add, at the end of the reconciliation procedure the required area is selected and printed.
Maybe some way of automatically printing the desired area??
 

dgpenny

New Member
Joined
Jan 1, 2004
Messages
46
As a suggestion, copy the sheet to another sheet for working on it. Record a macro doing what you want, then view the macro code. I do that often, then tweaking the code for more of what I want. Then assign it to a macro button and voila!
 

tuggers

Board Regular
Joined
Nov 10, 2005
Messages
145

ADVERTISEMENT

Thats great advice, thanks a lot.

The trouble is im aware of how to record a macro but not too clever on anything to do with vba.....
 

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
tuggers said:
The trouble is im aware of how to record a macro but not too clever on anything to do with vba.....

Post your recorded macro and add what changes you need. Somebody will help you out.

Regards,
 

tuggers

Board Regular
Joined
Nov 10, 2005
Messages
145

ADVERTISEMENT

there are 3 rows of entry under the lists of figures containing totals etc.

My question is, can a macro be created to make that appear and do the total calculations under the last entry?

The problem is, the amount of rows changes every day and theres no way of knowing how many there will be, is there any way around this?
 

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
tuggers said:
My question is, can a macro be created to make that appear and do the total calculations under the last entry?

The problem is, the amount of rows changes every day and theres no way of knowing how many there will be, is there any way around this?

That's not a problem. Record your macro and post it. We can make the necessary changes to accomodate changing rows.
 

tuggers

Board Regular
Joined
Nov 10, 2005
Messages
145
I have recorded a macro to total the columns required after creating the relevant text in the relevant cells.
The problems i have are

1) The first part of the macro places the text "TOTALS" in a cell, but this is placed in the active cell wherever that is on the sheet and not in the required cell!!

2) I need to get the macro to place the relevant text and totals under the last entry and not in the cells i created it in.

the text should be placed as in the image below, but relevant to the last row produced, can anyone please help?
reconciliation test.xls
ABCDEFG
1SupplierDocketW/bridgeBC55DiffRouteCollected
2DFOB11115116,86816,868020330/01/2005
3FMILK11115228,00427,995-930/01/2005
4DFOB11115327,16227,109-5330/01/2005
5DFOB11115427,85927,9428330/01/2005
6DFOB11115528,42128,4816030/01/2005
7DFOB11115628,11128,052-5930/01/2005
8DFOB11115927,83027,807-2320730/01/2005
9FMILK11116727,96628,06810230/01/2005
10
11TOTALS212,221212,322101
12net litresQualtrace
13Diff212,221
Sheet1
 

tuggers

Board Regular
Joined
Nov 10, 2005
Messages
145
Here is the code created for the macro.

Sub totals()
'
' totals Macro
' Macro recorded 21/12/2005 by Dave
'
' Keyboard Shortcut: Ctrl+t
'
ActiveCell.FormulaR1C1 = "TOTALS"
Range("B12").Select
ActiveCell.FormulaR1C1 = "Qualtrace"
Range("B13").Select
ActiveCell.FormulaR1C1 = "Diff"
Range("A12").Select
ActiveCell.FormulaR1C1 = "net litres"
Range("C13").Select
ActiveCell.FormulaR1C1 = "=R[-2]C-R[-1]C"
Range("C13").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("E11").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Range("E11").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A11:E13").Select
Selection.Font.Bold = True
Range("C16").Select
Columns("B:B").EntireColumn.AutoFit
Range("C2:C11").Select
Range("C11").Activate
ActiveCell.FormulaR1C1 = "=SUM(R[-9]C:R[-1]C)"
Range("D2:D11").Select
Range("D11").Activate
ActiveCell.FormulaR1C1 = "=SUM(R[-9]C:R[-1]C)"
Range("D2:D11").Select
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,118,673
Messages
5,573,594
Members
412,537
Latest member
Mohamed_5966
Top