Quantcast

Maximum PC

It is currently Thu Jul 31, 2014 4:20 am

All times are UTC - 8 hours




Post new topic Reply to topic  [ 2 posts ] 
Author Message
 Post subject: EXCEL/VB help needed
PostPosted: Thu Oct 23, 2008 9:18 am 
Team Member
Team Member
User avatar

Joined: Fri Oct 29, 2004 7:39 am
Posts: 691
Location: Folding as QQue - que'ing up #300 of pork and beef
Does anyone have experience in writing/editing Excel macros? I need to alter the absolute range references noted by "SS-*************************** " to allow for a variable number of records.

I am using Selection.End(xlDown).Select to use an existing column of data to find the last record, but the record macro function then used the absolute range (L3:L321 or A1:L321) in future steps.

Thanks

Code:
Sub TechData()
'
' TechData Macro
' Macro recorded 10/23/2008 by SS
'
' Keyboard Shortcut: Ctrl+q
'
'
' SS-Copy all original POS line item data to Sheet1
    Cells.Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("A1").Select
    ActiveSheet.Paste
'
' SS-Rename Sheet1 as Summary
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "Summary"
'
' SS-Delete unnecessary columns
    Columns("B:B").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("D:O").Select
    Selection.Delete Shift:=xlToLeft
'
' SS-Move product number column in front of product description
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight
    Columns("F:F").Select
    Selection.Cut
    Columns("D:D").Select
    ActiveSheet.Paste
    Columns("F:F").Select
    Selection.Delete Shift:=xlToLeft
'
' SS-Add NET column (ExtCOGS-ExtCOGR)
    Range("L1").Select
    Selection.Style = "Normal_Sheet1"
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.NumberFormat = "$#,##0.00"
    ActiveCell.FormulaR1C1 = "Net"
    With ActiveCell.Characters(Start:=1, Length:=3).Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 1
    End With
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "=RC[-4]-RC[-1]"
    Range("K3").Select
    Selection.End(xlDown).Select
    Range("L2").Select
    Selection.Copy
    Range("K2").Select
    Selection.End(xlDown).Select
'
' SS-*************************** need to correct absolute range reference
'
    Range("L3:L321").Select
    Range("L321").Activate
    ActiveSheet.Paste
    Range("A1").Select
    Application.CutCopyMode = False
'
' SS-Sort data records
' SS-*************************** need to correct absolute range reference
'
    Range("A1:L321").Sort Key1:=Range("C2"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
       
' SS-Subtotal sorted data records break on customer name and sum on net
    Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(12), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub


Top
  Profile  
 
 Post subject:
PostPosted: Thu Oct 23, 2008 1:10 pm 
Team Member
Team Member
User avatar

Joined: Fri Oct 29, 2004 7:39 am
Posts: 691
Location: Folding as QQue - que'ing up #300 of pork and beef
figured it out


Top
  Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 2 posts ] 

All times are UTC - 8 hours


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group