Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Need to tailor my macro code.
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 31
Default Need to tailor my macro code.
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default Need to tailor my macro code.
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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 31
Default Need to tailor my macro code.
cheer's for the reply but it has come up as

#name? in the boxes where the totals for each company are supposed to go?
Any ideas?

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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 31
Default Need to tailor my macro code.
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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default Need to tailor my macro code.
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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 31
Default Need to tailor my macro code.
One more thing and i think it will be complete

its still coming up with name error

It think it may be this

AA,HY and PR are abbreviations used but the actual names in column F

are

Allen & Allen
Hyperformance
Prestige

Does this help?
"Tom Ogilvy" wrote:

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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default Need to tailor my macro code.
Sure that is exactly what is in the cell?

Anyway:

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 Trim(cell.Value)
Case "Allen & Allen"
If cell.Row < min1 Then min1 = cell.Row
If cell.Row max1 Then max1 = cell.Row
Case "Prestige"
If cell.Row < min2 Then min2 = cell.Row
If cell.Row max2 Then max2 = cell.Row
Case "Hyperformance"
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:

One more thing and i think it will be complete

its still coming up with name error

It think it may be this

AA,HY and PR are abbreviations used but the actual names in column F

are

Allen & Allen
Hyperformance
Prestige

Does this help?
"Tom Ogilvy" wrote:

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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Deleting code from a macro (by a macro) Brettjg Excel Discussion (Misc queries) 2 May 8th 07 10:14 PM
Macro code to remove a macro after it executes Charles Excel Programming 4 March 22nd 06 12:11 PM
read macro code by vb code Francesco Geri Excel Programming 2 October 7th 05 10:24 AM
do anybody have a sample code for executing excel macro from vb code?<eom B Deepak Excel Programming 2 September 30th 05 09:59 AM
Macro copying macro code TNSKHelp Excel Programming 1 June 17th 05 04:59 PM


All times are GMT +1. The time now is 06:06 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement