Carpiem
07-06-2006, 12:40 PM
Good Afternoon,
Some help would be appreciated with 2 coding errors:
1st - The “Sales” sheet table shows column H (NetSales) with zero sales. Something is horribly wrong. :dunno
Column I sales numbers should appear in column H (NetSales). The “Expected” sheet shows what I was trying to do with code below.
Sub FillTable()
Dim TCol As Long
Dim LastRow As Long
Application.ScreenUpdating = False
'Clear upper table
Rows("1:13").ClearContents
'Set Group Titles
Range("A1") = "Group"
Range("A1").Interior.ColorIndex = 37
Range("A1").Font.Bold = True
Range("A2") = "Drives"
Range("A3") = "GMS"
Range("A4") = "HMI"
Range("A5") = "IC"
Range("A6") = "MCC"
Range("A7") = "Motion"
Range("A8") = "Other"
Range("A9") = "PLC"
Range("A10") = "Safety"
Range("A11") = "Sensors"
Range("A12") = "Software"
Range("A13") = "Total"
Range("A13").Font.Bold = True
Range("A1:A13").HorizontalAlignment = xlLeft
'Select Customer row to last cell and column used
TCol = Range("C16").End(xlToRight).Column
'Copy Customers to upper table - From B1 across
Range("C16").Resize(, TCol).Copy Range("B1")
LastRow = Range("B16").End(xlDown).Row - 1
'Total Sales by Group - Column "B"
Range("B2").Formula = "=SUMIF($B17:$B" & LastRow & ",""Drives"",C17:C" & LastRow & ")"
Range("B3").Formula = "=SUMIF($B17:$B" & LastRow & ",""GMS"",C17:C" & LastRow & ")"
Range("B4").Formula = "=SUMIF($B17:$B" & LastRow & ",""HMI"",C17:C" & LastRow & ")"
Range("B5").Formula = "=SUMIF($B17:$B" & LastRow & ",""IC"",C17:C" & LastRow & ")"
Range("B6").Formula = "=SUMIF($B17:$B" & LastRow & ",""MCC"",C17:C" & LastRow & ")"
Range("B7").Formula = "=SUMIF($B17:$B" & LastRow & ",""Motion"",C17:C" & LastRow & ")"
Range("B8").Formula = "=SUMIF($B17:$B" & LastRow & ",""Other"",C17:C" & LastRow & ")"
Range("B9").Formula = "=SUMIF($B17:$B" & LastRow & ",""PLC"",C17:C" & LastRow & ")"
Range("B10").Formula = "=SUMIF($B17:$B" & LastRow & ",""Safety"",C17:C" & LastRow & ")"
Range("B11").Formula = "=SUMIF($B17:$B" & LastRow & ",""Sensors"",C17:C" & LastRow & ")"
Range("B12").Formula = "=SUMIF($B17:$B" & LastRow & ",""Software"",C17:C" & LastRow & ")"
Range("B13").Formula = "=SUM(C17:C" & LastRow & ")"
Range("B2:B13").Copy Range("B2:B13").Resize(, TCol - 1)
Range("A2").Offset(, TCol).Resize(12) = "=SUM(" & Range("B2").Resize(, TCol - 1).Address(0, 0) & ")"
'Last Column Title = NetSales
Range("A1").End(xlToRight).Offset(0, 1).Value = "NetSales"
Range("A1").End(xlToRight).Interior.ColorIndex = 37
Range("A1").End(xlToRight).Font.Bold = True
'Align Table
Range("B1").End(xlToRight).HorizontalAlignment = xlCenter
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).HorizontalAlignment = xlGeneral
Borders ‘format Upper Table Borders
Range("A2").Select
Application.ScreenUpdating = True
End Sub
Dream Time – Dumb Question
Would VBA recognize the Groups automatically? This applies to the code portion under ? 'Set Group Titles. If yes, would it also be able to automatically adjust the rest of the coding?
I thought this might appeal to the “Gurus” at Vbax. Hope I don’t get an “earful”. :)
------------------------------------------------------------------
2nd- I would like the Vlookup function used in column “B”, to run down as far as column “A” is filled (from at row 16). My “Vlookup” macro is set to a specific depth for Column “A”. So it is pretty useless, as the items in Col “A” change on a daily, weekly and monthly basis…. depending on when run.
Sub Vlookup()
'Return Group
Range("B17").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(R16C1:R1000C1,GroupCodes!C1:C2,2,0)"
Selection.AutoFill Destination:=Range("B17:B31")
Range("B17:B207").Select
Range("A17").Select
'Convert Vlookup To Values
Range("B16").Select
Range(Selection, Selection.End(xlDown)).Copy
Range("B16").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
Range("A2").Select
End Sub
Thank you,
Brian
Some help would be appreciated with 2 coding errors:
1st - The “Sales” sheet table shows column H (NetSales) with zero sales. Something is horribly wrong. :dunno
Column I sales numbers should appear in column H (NetSales). The “Expected” sheet shows what I was trying to do with code below.
Sub FillTable()
Dim TCol As Long
Dim LastRow As Long
Application.ScreenUpdating = False
'Clear upper table
Rows("1:13").ClearContents
'Set Group Titles
Range("A1") = "Group"
Range("A1").Interior.ColorIndex = 37
Range("A1").Font.Bold = True
Range("A2") = "Drives"
Range("A3") = "GMS"
Range("A4") = "HMI"
Range("A5") = "IC"
Range("A6") = "MCC"
Range("A7") = "Motion"
Range("A8") = "Other"
Range("A9") = "PLC"
Range("A10") = "Safety"
Range("A11") = "Sensors"
Range("A12") = "Software"
Range("A13") = "Total"
Range("A13").Font.Bold = True
Range("A1:A13").HorizontalAlignment = xlLeft
'Select Customer row to last cell and column used
TCol = Range("C16").End(xlToRight).Column
'Copy Customers to upper table - From B1 across
Range("C16").Resize(, TCol).Copy Range("B1")
LastRow = Range("B16").End(xlDown).Row - 1
'Total Sales by Group - Column "B"
Range("B2").Formula = "=SUMIF($B17:$B" & LastRow & ",""Drives"",C17:C" & LastRow & ")"
Range("B3").Formula = "=SUMIF($B17:$B" & LastRow & ",""GMS"",C17:C" & LastRow & ")"
Range("B4").Formula = "=SUMIF($B17:$B" & LastRow & ",""HMI"",C17:C" & LastRow & ")"
Range("B5").Formula = "=SUMIF($B17:$B" & LastRow & ",""IC"",C17:C" & LastRow & ")"
Range("B6").Formula = "=SUMIF($B17:$B" & LastRow & ",""MCC"",C17:C" & LastRow & ")"
Range("B7").Formula = "=SUMIF($B17:$B" & LastRow & ",""Motion"",C17:C" & LastRow & ")"
Range("B8").Formula = "=SUMIF($B17:$B" & LastRow & ",""Other"",C17:C" & LastRow & ")"
Range("B9").Formula = "=SUMIF($B17:$B" & LastRow & ",""PLC"",C17:C" & LastRow & ")"
Range("B10").Formula = "=SUMIF($B17:$B" & LastRow & ",""Safety"",C17:C" & LastRow & ")"
Range("B11").Formula = "=SUMIF($B17:$B" & LastRow & ",""Sensors"",C17:C" & LastRow & ")"
Range("B12").Formula = "=SUMIF($B17:$B" & LastRow & ",""Software"",C17:C" & LastRow & ")"
Range("B13").Formula = "=SUM(C17:C" & LastRow & ")"
Range("B2:B13").Copy Range("B2:B13").Resize(, TCol - 1)
Range("A2").Offset(, TCol).Resize(12) = "=SUM(" & Range("B2").Resize(, TCol - 1).Address(0, 0) & ")"
'Last Column Title = NetSales
Range("A1").End(xlToRight).Offset(0, 1).Value = "NetSales"
Range("A1").End(xlToRight).Interior.ColorIndex = 37
Range("A1").End(xlToRight).Font.Bold = True
'Align Table
Range("B1").End(xlToRight).HorizontalAlignment = xlCenter
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).HorizontalAlignment = xlGeneral
Borders ‘format Upper Table Borders
Range("A2").Select
Application.ScreenUpdating = True
End Sub
Dream Time – Dumb Question
Would VBA recognize the Groups automatically? This applies to the code portion under ? 'Set Group Titles. If yes, would it also be able to automatically adjust the rest of the coding?
I thought this might appeal to the “Gurus” at Vbax. Hope I don’t get an “earful”. :)
------------------------------------------------------------------
2nd- I would like the Vlookup function used in column “B”, to run down as far as column “A” is filled (from at row 16). My “Vlookup” macro is set to a specific depth for Column “A”. So it is pretty useless, as the items in Col “A” change on a daily, weekly and monthly basis…. depending on when run.
Sub Vlookup()
'Return Group
Range("B17").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(R16C1:R1000C1,GroupCodes!C1:C2,2,0)"
Selection.AutoFill Destination:=Range("B17:B31")
Range("B17:B207").Select
Range("A17").Select
'Convert Vlookup To Values
Range("B16").Select
Range(Selection, Selection.End(xlDown)).Copy
Range("B16").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
Range("A2").Select
End Sub
Thank you,
Brian