Home |
Search |
Today's Posts |
#1
|
|||
|
|||
I need some help with a Marco
Good Afternoon, I need some help regarding a marco. I created a marco for a process at my job that I need to add something too. I'm just not sure how to add what I need to add. Here's the problem: At my job we D/L pricing files for contracts. The files can be from 5 lines to 10,000 lines. I made a Marco to format the price list to look a certain way from a SAP download (see first attachment). Everything was working fine until a line appeared after all the formatted was done with no material number (it starting appearing after a SAP enchancement). This line always appears at the bottem no matter how large the price list. I need this line to be deleted as part of my marco. I'm not sure how to make is consistant regardless of the size of the spreadsheet. I was thinking of a IF formula starting from B7 and going to the bottem of the spreadsheet deleting any line without a material number after B7 in column B. I'm just not sure how to to it. Can someone please help me out, thanks. Below is the Marco I already have: Sub pricingmacro() ' ' pricingmacro Macro ' Macro recorded 09/18/2003 by Walt Davis Charles ' ' Keyboard Shortcut: Ctrl+Shift+N ' Cells.Select Cells.EntireColumn.AutoFit Rows("1:1").Select Selection.Insert Shift:=xlDown Selection.Insert Shift:=xlDown Selection.Insert Shift:=xlDown Selection.Insert Shift:=xlDown Selection.Insert Shift:=xlDown Selection.Insert Shift:=xlDown Range("A1").Select ActiveCell.FormulaR1C1 = "Becton Dickinson & Company" Range("B8").Select Selection.Cut Destination:=Range("A2") Range("D8").Select Selection.Cut Destination:=Range("A3") Range("A1:A3").Select Selection.Cut Destination:=Range("F1:F3") Columns("D:E").Select Range("E1").Activate Selection.Delete Shift:=xlToLeft Columns("A:B").Select Range("B1").Activate Selection.Delete Shift:=xlToLeft Range("B1:B3").Select Selection.Cut Destination:=Range("A1:A3") Range("A7").Select Selection.Copy Range("A4").Select ActiveSheet.Paste Range("A8").Select Application.CutCopyMode = False Selection.Copy Range("B4").Select ActiveSheet.Paste Columns("B:B").ColumnWidth = 15 Range("B1").Select Columns("B:B").ColumnWidth = 7 Columns("B:B").EntireColumn.AutoFit Columns("D:D").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("H:H").Select Selection.Delete Shift:=xlToLeft Selection.Delete Shift:=xlToLeft Selection.Delete Shift:=xlToLeft Range("I7").Select ActiveCell.FormulaR1C1 = "SP Price" Columns("J:J").Select Selection.Delete Shift:=xlToLeft Range("K7").Select ActiveCell.FormulaR1C1 = "CS Price" Columns("L:L").Select Selection.Delete Shift:=xlToLeft Columns("M:M").Select Selection.Delete Shift:=xlToLeft Range("K8").Select Selection.End(xlDown).Select Range("A7").Select Range(Selection, Selection.End(xlToRight)).Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With Range(Selection, Selection.End(xlDown)).Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Range("F7").Select Range(Selection, Selection.End(xlDown)).Select Selection.NumberFormat = "0.00000" Columns("I:K").Select Selection.NumberFormat = "0.00" Range("A1:L5").Select Range("A8").Select Selection.Sort Key1:=Range("B8"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Columns("I:I").EntireColumn.AutoFit With ActiveSheet.PageSetup .PrintTitleRows = "$1:$7" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False End With Range("M8").Select ActiveCell.FormulaR1C1 = "=IF(RC[-2]="""",1,0)" Range("L7").Select Selection.End(xlToLeft).Select Selection.End(xlDown).Select ActiveCell.Offset(1, 12).Select ActiveCell.FormulaR1C1 = "2" ActiveCell.Offset(-1).Select Range(Selection, Selection.End(xlUp)).Select Selection.FillDown Range("M8").Select Do If Selection = "1" Then ActiveCell.Offset(0, -4).Select Range(Selection, Selection.Offset(0, 1)).Select Selection.Copy ActiveCell.Offset(0, 2).Select ActiveSheet.Paste ActiveCell.Offset(0, -2).Select Range(Selection, Selection.Offset(0, 1)).Select Selection.ClearContents ActiveCell.Offset(1, 4).Select ElseIf Selection = "0" Then ActiveCell.Offset(1, 0).Select End If Loop While Selection < "2" Range(Selection, Selection.End(xlUp)).Select Selection.ClearContents Selection.End(xlUp).Select Selection.End(xlToLeft).Select Columns("H:H").Select Selection.Delete Shift:=xlToLeft Columns("J:K").EntireColumn.AutoFit Range("A1").Select End Sub +-------------------------------------------------------------------+ |Filename: 1000058416 formatted.doc | |Download: http://www.excelforum.com/attachment.php?postid=3658 | +-------------------------------------------------------------------+ -- xgunda420x ------------------------------------------------------------------------ xgunda420x's Profile: http://www.excelforum.com/member.php...o&userid=25559 View this thread: http://www.excelforum.com/showthread...hreadid=391359 |
#2
|
|||
|
|||
hi,
what column is the material number in? what data is next to it? regards FSt1 "xgunda420x" wrote: Good Afternoon, I need some help regarding a marco. I created a marco for a process at my job that I need to add something too. I'm just not sure how to add what I need to add. Here's the problem: At my job we D/L pricing files for contracts. The files can be from 5 lines to 10,000 lines. I made a Marco to format the price list to look a certain way from a SAP download (see first attachment). Everything was working fine until a line appeared after all the formatted was done with no material number (it starting appearing after a SAP enchancement). This line always appears at the bottem no matter how large the price list. I need this line to be deleted as part of my marco. I'm not sure how to make is consistant regardless of the size of the spreadsheet. I was thinking of a IF formula starting from B7 and going to the bottem of the spreadsheet deleting any line without a material number after B7 in column B. I'm just not sure how to to it. Can someone please help me out, thanks. Below is the Marco I already have: Sub pricingmacro() ' ' pricingmacro Macro ' Macro recorded 09/18/2003 by Walt Davis Charles ' ' Keyboard Shortcut: Ctrl+Shift+N ' Cells.Select Cells.EntireColumn.AutoFit Rows("1:1").Select Selection.Insert Shift:=xlDown Selection.Insert Shift:=xlDown Selection.Insert Shift:=xlDown Selection.Insert Shift:=xlDown Selection.Insert Shift:=xlDown Selection.Insert Shift:=xlDown Range("A1").Select ActiveCell.FormulaR1C1 = "Becton Dickinson & Company" Range("B8").Select Selection.Cut Destination:=Range("A2") Range("D8").Select Selection.Cut Destination:=Range("A3") Range("A1:A3").Select Selection.Cut Destination:=Range("F1:F3") Columns("D:E").Select Range("E1").Activate Selection.Delete Shift:=xlToLeft Columns("A:B").Select Range("B1").Activate Selection.Delete Shift:=xlToLeft Range("B1:B3").Select Selection.Cut Destination:=Range("A1:A3") Range("A7").Select Selection.Copy Range("A4").Select ActiveSheet.Paste Range("A8").Select Application.CutCopyMode = False Selection.Copy Range("B4").Select ActiveSheet.Paste Columns("B:B").ColumnWidth = 15 Range("B1").Select Columns("B:B").ColumnWidth = 7 Columns("B:B").EntireColumn.AutoFit Columns("D:D").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("H:H").Select Selection.Delete Shift:=xlToLeft Selection.Delete Shift:=xlToLeft Selection.Delete Shift:=xlToLeft Range("I7").Select ActiveCell.FormulaR1C1 = "SP Price" Columns("J:J").Select Selection.Delete Shift:=xlToLeft Range("K7").Select ActiveCell.FormulaR1C1 = "CS Price" Columns("L:L").Select Selection.Delete Shift:=xlToLeft Columns("M:M").Select Selection.Delete Shift:=xlToLeft Range("K8").Select Selection.End(xlDown).Select Range("A7").Select Range(Selection, Selection.End(xlToRight)).Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With Range(Selection, Selection.End(xlDown)).Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Range("F7").Select Range(Selection, Selection.End(xlDown)).Select Selection.NumberFormat = "0.00000" Columns("I:K").Select Selection.NumberFormat = "0.00" Range("A1:L5").Select Range("A8").Select Selection.Sort Key1:=Range("B8"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Columns("I:I").EntireColumn.AutoFit With ActiveSheet.PageSetup .PrintTitleRows = "$1:$7" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False End With Range("M8").Select ActiveCell.FormulaR1C1 = "=IF(RC[-2]="""",1,0)" Range("L7").Select Selection.End(xlToLeft).Select Selection.End(xlDown).Select ActiveCell.Offset(1, 12).Select ActiveCell.FormulaR1C1 = "2" ActiveCell.Offset(-1).Select Range(Selection, Selection.End(xlUp)).Select Selection.FillDown Range("M8").Select Do If Selection = "1" Then ActiveCell.Offset(0, -4).Select Range(Selection, Selection.Offset(0, 1)).Select Selection.Copy ActiveCell.Offset(0, 2).Select ActiveSheet.Paste ActiveCell.Offset(0, -2).Select Range(Selection, Selection.Offset(0, 1)).Select Selection.ClearContents ActiveCell.Offset(1, 4).Select ElseIf Selection = "0" Then ActiveCell.Offset(1, 0).Select End If Loop While Selection < "2" Range(Selection, Selection.End(xlUp)).Select Selection.ClearContents Selection.End(xlUp).Select Selection.End(xlToLeft).Select Columns("H:H").Select Selection.Delete Shift:=xlToLeft Columns("J:K").EntireColumn.AutoFit Range("A1").Select End Sub +-------------------------------------------------------------------+ |Filename: 1000058416 formatted.doc | |Download: http://www.excelforum.com/attachment.php?postid=3658 | +-------------------------------------------------------------------+ -- xgunda420x ------------------------------------------------------------------------ xgunda420x's Profile: http://www.excelforum.com/member.php...o&userid=25559 View this thread: http://www.excelforum.com/showthread...hreadid=391359 |
#3
|
|||
|
|||
Hey, The material number is in column B and the material description is in Column C. Thanks FSt1 Wrote: hi, what column is the material number in? what data is next to it? regards FSt1 "xgunda420x" wrote: Good Afternoon, I need some help regarding a marco. I created a marco for a process at my job that I need to add something too. I'm just not sure how to add what I need to add. Here's the problem: At my job we D/L pricing files for contracts. The files can be from 5 lines to 10,000 lines. I made a Marco to format the price list to look a certain way from a SAP download (see first attachment). Everything was working fine until a line appeared after all the formatted was done with no material number (it starting appearing after a SAP enchancement). This line always appears at the bottem no matter how large the price list. I need this line to be deleted as part of my marco. I'm not sure how to make is consistant regardless of the size of the spreadsheet. I was thinking of a IF formula starting from B7 and going to the bottem of the spreadsheet deleting any line without a material number after B7 in column B. I'm just not sure how to to it. Can someone please help me out, thanks. Below is the Marco I already have: Sub pricingmacro() ' ' pricingmacro Macro ' Macro recorded 09/18/2003 by Walt Davis Charles ' ' Keyboard Shortcut: Ctrl+Shift+N ' Cells.Select Cells.EntireColumn.AutoFit Rows("1:1").Select Selection.Insert Shift:=xlDown Selection.Insert Shift:=xlDown Selection.Insert Shift:=xlDown Selection.Insert Shift:=xlDown Selection.Insert Shift:=xlDown Selection.Insert Shift:=xlDown Range("A1").Select ActiveCell.FormulaR1C1 = "Becton Dickinson & Company" Range("B8").Select Selection.Cut Destination:=Range("A2") Range("D8").Select Selection.Cut Destination:=Range("A3") Range("A1:A3").Select Selection.Cut Destination:=Range("F1:F3") Columns("D:E").Select Range("E1").Activate Selection.Delete Shift:=xlToLeft Columns("A:B").Select Range("B1").Activate Selection.Delete Shift:=xlToLeft Range("B1:B3").Select Selection.Cut Destination:=Range("A1:A3") Range("A7").Select Selection.Copy Range("A4").Select ActiveSheet.Paste Range("A8").Select Application.CutCopyMode = False Selection.Copy Range("B4").Select ActiveSheet.Paste Columns("B:B").ColumnWidth = 15 Range("B1").Select Columns("B:B").ColumnWidth = 7 Columns("B:B").EntireColumn.AutoFit Columns("D:D").Select Application.CutCopyMode = False Selection.Delete Shift:=xlToLeft Columns("H:H").Select Selection.Delete Shift:=xlToLeft Selection.Delete Shift:=xlToLeft Selection.Delete Shift:=xlToLeft Range("I7").Select ActiveCell.FormulaR1C1 = "SP Price" Columns("J:J").Select Selection.Delete Shift:=xlToLeft Range("K7").Select ActiveCell.FormulaR1C1 = "CS Price" Columns("L:L").Select Selection.Delete Shift:=xlToLeft Columns("M:M").Select Selection.Delete Shift:=xlToLeft Range("K8").Select Selection.End(xlDown).Select Range("A7").Select Range(Selection, Selection.End(xlToRight)).Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With Range(Selection, Selection.End(xlDown)).Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Range("F7").Select Range(Selection, Selection.End(xlDown)).Select Selection.NumberFormat = "0.00000" Columns("I:K").Select Selection.NumberFormat = "0.00" Range("A1:L5").Select Range("A8").Select Selection.Sort Key1:=Range("B8"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Columns("I:I").EntireColumn.AutoFit With ActiveSheet.PageSetup .PrintTitleRows = "$1:$7" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False End With Range("M8").Select ActiveCell.FormulaR1C1 = "=IF(RC[-2]="""",1,0)" Range("L7").Select Selection.End(xlToLeft).Select Selection.End(xlDown).Select ActiveCell.Offset(1, 12).Select ActiveCell.FormulaR1C1 = "2" ActiveCell.Offset(-1).Select Range(Selection, Selection.End(xlUp)).Select Selection.FillDown Range("M8").Select Do If Selection = "1" Then ActiveCell.Offset(0, -4).Select Range(Selection, Selection.Offset(0, 1)).Select Selection.Copy ActiveCell.Offset(0, 2).Select ActiveSheet.Paste ActiveCell.Offset(0, -2).Select Range(Selection, Selection.Offset(0, 1)).Select Selection.ClearContents ActiveCell.Offset(1, 4).Select ElseIf Selection = "0" Then ActiveCell.Offset(1, 0).Select End If Loop While Selection < "2" Range(Selection, Selection.End(xlUp)).Select Selection.ClearContents Selection.End(xlUp).Select Selection.End(xlToLeft).Select Columns("H:H").Select Selection.Delete Shift:=xlToLeft Columns("J:K").EntireColumn.AutoFit Range("A1").Select End Sub +-------------------------------------------------------------------+ |Filename: 1000058416 formatted.doc | |Download: http://www.excelforum.com/attachment.php?postid=3658 | +-------------------------------------------------------------------+ -- xgunda420x ------------------------------------------------------------------------ xgunda420x's Profile: http://www.excelforum.com/member.php...o&userid=25559 View this thread: http://www.excelforum.com/showthread...hreadid=391359 -- xgunda420x ------------------------------------------------------------------------ xgunda420x's Profile: http://www.excelforum.com/member.php...o&userid=25559 View this thread: http://www.excelforum.com/showthread...hreadid=391359 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to assign a marco to a command button | Excel Worksheet Functions | |||
virus marco | Excel Discussion (Misc queries) | |||
excel Marco question ... need help please | Excel Discussion (Misc queries) | |||
use marco to consolidate | Excel Worksheet Functions | |||
Marco | New Users to Excel |