My mistake.
try this:
Sub Shortfalls()
'
' Shortfalls Macro
' Macro recorded 12/07/2006 by terminal12
'
'
Dim cell As Range, rng As Range
Dim max1 As Long, max2 As Long, max3 As Long
Dim min1 As Long, min2 As Long, min3 As Long
min1 = 65536
min2 = 65536
min3 = 65536
Columns("E:K").Delete Shift:=xlToLeft
Columns("J:K").Delete Shift:=xlToLeft
Columns("M:U").Delete Shift:=xlToLeft
Range("A1").CurrentRegion.Sort _
Key1:=Range("F2"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("O7").FormulaR1C1 = "AA Total"
Range("O8").FormulaR1C1 = "
PR Total"
Range("O9").FormulaR1C1 = "HY Total"
Set rng = Range(Range("F2"), _
Cells(Rows.Count, "F").End(xlUp))
For Each cell In rng
Select Case cell.Value
Case "AA"
If cell.Row < min1 Then min1 = cell.Row
If cell.Row max1 Then max1 = cell.Row
Case "
PR"
If cell.Row < min2 Then min2 = cell.Row
If cell.Row max2 Then max2 = cell.Row
Case "HY"
If cell.Row < min3 Then min3 = cell.Row
If cell.Row max3 Then max3 = cell.Row
End Select
Next
Range("P7").FormulaR1C1 = _
"=SUM(R" & min1 & "C4:R" & max1 & "C4)"
Range("P8").FormulaR1C1 = _
"=SUM(R" & min2 & "C4:R" & max2 & "C4)"
Range("P9").FormulaR1C1 = _
"=SUM(R" & min3 & "C4:R" & max3 & "C4)"
End Sub
--
Regards,
Tom Ogilvy
"Barry Walker" wrote:
Column D Contains the values that need totalling and column F contains the
company names. I need to total the values for each company.
Regards
Barry
"Tom Ogilvy" wrote:
Assume column D contains AA, PR, or HY adjust for actual values if not.
Sub Shortfalls()
'
' Shortfalls Macro
' Macro recorded 12/07/2006 by terminal12
'
'
Dim cell As Range, rng As Range
Dim max1 As Long, max2 As Long, max3 As Long
Dim min1 As Long, min2 As Long, min3 As Long
min1 = 65536
min2 = 65536
min3 = 65536
Columns("E:K").Delete Shift:=xlToLeft
Columns("J:K").Delete Shift:=xlToLeft
Columns("M:U").Delete Shift:=xlToLeft
Range("A1").CurrentRegion.Sort _
Key1:=Range("F2"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("O7").FormulaR1C1 = "AA Total"
Range("O8").FormulaR1C1 = "PR Total"
Range("O9").FormulaR1C1 = "HY Total"
Set rng = Range(Range("D2"), _
Cells(Rows.Count, "D").End(xlUp))
For Each cell In rng
Select Case cell.Value
Case "AA"
If cell.Row < min1 Then min1 = cell.Row
If cell.Row max1 Then max1 = cell.Row
Case "PR"
If cell.Row < min2 Then min2 = cell.Row
If cell.Row max2 Then max2 = cell.Row
Case "HY"
If cell.Row < min3 Then min3 = cell.Row
If cell.Row max3 Then max3 = cell.Row
End Select
Next
Range("P7").FormulaR1C1 = _
"=SUM(R" & min1 & "C4:R" & max1 & "C4)"
Range("P8").FormulaR1C1 = _
"=SUM(R" & min2 & "C4:R" & max2 & "C4)"
Range("P9").FormulaR1C1 = _
"=SUM(R" & min3 & "C4:R" & max3 & "C4)"
End Sub
"Barry Walker" wrote:
I have come up with a macro (shown below), that sorts out data that I
download and totals it. The problem is that the macro works for this
particular download but not for the others which change every day. I download
the spreadsheet and at the macro deletes cells, sorts the data for me, splits
it into the 3 companys I am analysing and totals the amount for each of the
companies. However the deleting of cells is fine and sorting them is also
fine. From here I am stuck. Say in the first days spreadsheet there are 1000
values corresponding to one company 2000 to another and 1500 to the other,
this changes daily. The macro needs to recognise a Company name and then
total the values which are in a seperate column corresponding to that
company.
Sub Shortfalls()
'
' Shortfalls Macro
' Macro recorded 12/07/2006 by terminal12
'
'
Columns("E:K").Select
Selection.Delete Shift:=xlToLeft
Columns("J:K").Select
Selection.Delete Shift:=xlToLeft
Columns("M:U").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Columns("F:F").Select
Range("A1:L5622").Sort Key1:=Range("F2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("O7").Select
ActiveCell.FormulaR1C1 = "AA Total"
Range("O8").Select
ActiveCell.FormulaR1C1 = "PR Total"
Range("O9").Select
ActiveCell.FormulaR1C1 = "HY Total"
Range("P7").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-5]C[-12]:R[2049]C[-12])"
Range("P8").Select
ActiveCell.FormulaR1C1 = "=SUM(R[4601]C[-12]:R[5614]C[-12])"
Range("P9").Select
ActiveCell.FormulaR1C1 = "=SUM(R[2048]C[-12]:R[4599]C[-12])"
Range("P10").Select
End Sub
Can anyone help?
Regards
Barry