Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Jay Petrulis or Anyone.

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Mike
    Posts
    796
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This is a carryover from yesterday.
    I'm going to list my code and I hope
    you will see where my problem is.

    Application.ScreenUpdating = False 'This hides the Physical running Process of the Macro.
    Application.Goto Reference:="R2C2" 'Places the Cursor at B2.
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Sort Key1:=ActiveCell.Offset(0, 15).Range("A1"), Order1:= _
    xlAscending, Key2:=ActiveCell.Offset(0, 1).Range("A1"), Order2:=xlAscending _
    , Key3:=ActiveCell, Order3:=xlAscending, Header:=xlNo, OrderCustom:=1, _
    MatchCase:=False, Orientation:=xlTopToBottom
    'We've just Sorted the entire Worsheet by COMM CODE.
    ActiveCell.Offset(-1, 0).Range("A1").Select
    Selection.AutoFilter 'Turns the AutoFilter "ON".
    Application.Goto Reference:="R1C2"
    Selection.End(xlToRight).Select 'Places the Cursor at R1.
    Selection.AutoFilter Field:=17, Criteria1:="=026", Operator:=xlOr, _
    Criteria2:="=009"
    'The Previous Code Goes to Field 17 (which is Col "Q" the 16th letter of the Alphabet duh!).
    'and only shows 026 which is Clarks COMM CODE.
    Application.Goto Reference:="R1C2" 'GOING FROM HERE TO THE NEXT LINE IS WHERE I'M HAVING MY PROBLEM.
    ActiveCell.Offset(39, 0).Range("A1").Select 'NEED TO GET TO HERE ANOTHER WAY. NEXT MONTH IT MAY BE (50,0).
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy 'The previous 4 lines copies over only the Visible Cells to Salesman's Sheet.
    Selection.SpecialCells(xlCellTypeVisible).Select 'Sets up for Deletion of the Visible Rows Only.

    Sheets("CLARK #026 #009").Select 'Then we select Only the Info for Clark GOTO his Sheet for Pasting.
    Application.Goto Reference:="R10C2"
    ActiveSheet.Paste
    Application.CutCopyMode = False


    After using the Data Filter Row #1. How can
    I go to the next visible line and CTRL+Shift+End, and Copy?
    Going here is my problem:
    ActiveCell.Offset(39, 0).Range("A1").Select

    If I use the same code in original macro I
    could ignore data to be copied. Especially since it remembers (39,0) If my data runs from (20:50), I'm "toast".

    Please get back to me and I'll try to
    explain further if necessary.


    Zac

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try the following with variable length data sets and report the results.

    Code:
    Sub test()
    Dim DataRng As Range, DataRng1 As Range
    Dim DataRng2 As Range, DataRng3 As Range
    
    With Sheets("Sheet1")
        Set DataRng = .UsedRange
        Set DataRng1 = .Range("A2", .Range("A2").End(xlDown))
        Set DataRng2 = .Range(DataRng1, DataRng1.End(xlToRight))
        
        With .Range("A1").CurrentRegion
            .Sort Key1:=.Range("Q2"), Order1:=xlAscending, Key2:=.Range("C2"), Order2:=xlAscending _
            , Key3:=.Range("B2"), Order3:=xlAscending, Header:=xlYes
            .AutoFilter Field:=18, Criteria1:="=026", Operator:=xlOr, Criteria2:="=009"
            DataRng2.SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet2").Range("A10")
            .AutoFilter
        End With
    End With
    
    End Sub
    EDIT: This assumes the data is on Sheet1 and is pasting to Sheet2. Please change as required.

    [ This Message was edited by: Jay Petrulis on 2002-05-03 08:37 ]

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Mike
    Posts
    796
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Jay,
    Here's all my code.
    I use this to move all the Salesmans data to their respective Sheet.
    I'm more than sure it can be cleaned up to run better.
    Being a Novice, I will get better as time goes on.

    I'm going to try and use your suggestion provided I can understand how it works.

    Here's the code (hope it doesn't mess up the MESSAGE Board).

    Sub CommReports()
    '
    ' CommReports Macro
    ' Macro recorded 4/19/2002 by Mike Zaccardo
    '

    '
    Application.ScreenUpdating = False 'This hides the Physical running Process of the Macro.
    Application.Goto Reference:="R2C2" 'Places the Cursor at B2.
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Sort Key1:=ActiveCell.Offset(0, 15).Range("A1"), Order1:= _
    xlAscending, Key2:=ActiveCell.Offset(0, 1).Range("A1"), Order2:=xlAscending _
    , Key3:=ActiveCell, Order3:=xlAscending, Header:=xlNo, OrderCustom:=1, _
    MatchCase:=False, Orientation:=xlTopToBottom 'We've just Sorted the entire Worsheet by COMM CODE.
    ActiveCell.Offset(-1, 0).Range("A1").Select
    Selection.AutoFilter 'Turns the AutoFilter "ON".
    Application.Goto Reference:="R1C2"
    Selection.End(xlToRight).Select 'Places the Cursor at R1.
    Selection.AutoFilter Field:=17, Criteria1:="=026", Operator:=xlOr, _
    Criteria2:="=009"
    'The Previous Code Goes to Field 17 and only shows 026 which is Clarks COMM CODE.
    Application.Goto Reference:="R1C2" 'GOING FROM HERE TO THE NEXT LINE IS WHERE I'M HAVING MY PROBLEM.
    ActiveCell.Offset(39, 0).Range("A1").Select 'NEED TO GET TO HERE ANOTHER WAY. NEXT MONTH IT MAY BE (50,0).
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.Copy 'The previous 4 lines copies over only the Visible Cells to Salesman's Sheet.
    Selection.SpecialCells(xlCellTypeVisible).Select 'Sets up for Deletion of the Visible Rows Only.

    Sheets("CLARK #026 #009").Select 'Then we select Only the Info for Clark GOTO his Sheet for Pasting.
    Application.Goto Reference:="R10C2"
    ActiveSheet.Paste
    Application.CutCopyMode = False

    Application.Goto Reference:="R10C2"
    Application.Goto Reference:="R9C8"
    ActiveCell.Columns("A:A").EntireColumn.Select
    Selection.Insert Shift:=xlToRight
    ActiveCell.Offset(8, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "TOTAL INV" & Chr(10) & "Payments"
    With ActiveCell.Characters(Start:=1, Length:=18).Font
    .Name = "Arial"
    .FontStyle = "Bold"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    End With
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .ShrinkToFit = False
    .MergeCells = False
    End With

    'Data Xferred to Joe's Sheet.
    'Next Salesman (DeSimone).

    Sheets("PAI EFP MERGED").Select 'We return to the First Sheet in Workbook.
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.EntireRow.Delete 'All Rows are deleted.
    Application.Goto Reference:="R1C2"

    ActiveWindow.ScrollColumn = 6
    Selection.AutoFilter Field:=17
    Selection.End(xlToRight).Select
    Selection.AutoFilter Field:=17
    Range("A1").Select
    Selection.End(xlToRight).Select
    Selection.AutoFilter Field:=17, Criteria1:="=027", Operator:=xlOr, _
    Criteria2:="=006"
    'DeSimone Data Criteria.

    Application.Goto Reference:="R1C2"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy

    Sheets("DESIMONE #027 #006").Select
    Application.Goto Reference:="R10C2"
    ActiveSheet.Paste
    Application.CutCopyMode = False

    Application.Goto Reference:="R9C8"
    ActiveCell.Columns("A:A").EntireColumn.Select
    Selection.Insert Shift:=xlToRight
    ActiveCell.Offset(8, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "TOTAL INV" & Chr(10) & "Payments"
    With ActiveCell.Characters(Start:=1, Length:=18).Font
    .Name = "Arial"
    .FontStyle = "Bold"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    End With
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .ShrinkToFit = False
    .MergeCells = False
    End With
    Sheets("PAI EFP MERGED").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.EntireRow.Delete

    'DeSimone Data XFerred.
    'Next Salesman (Salicondro).

    ActiveWindow.ScrollColumn = 6
    Selection.AutoFilter Field:=17
    Selection.End(xlToRight).Select
    Selection.AutoFilter Field:=17
    Range("A1").Select
    Selection.AutoFilter Field:=17
    Selection.AutoFilter Field:=17, Criteria1:="=029", Operator:=xlOr, _
    Criteria2:="=008"
    'Salicondro Data Criteria.

    Application.Goto Reference:="R1C2"
    ActiveCell.Offset(1, 0).Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy

    Sheets("SALICONDRO #029 & #008").Select
    Application.Goto Reference:="R10C2"
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Application.Goto Reference:="R9C8"

    ActiveCell.Columns("A:A").EntireColumn.Select
    Selection.Insert Shift:=xlToRight
    ActiveCell.Offset(8, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "TOTAL INV" & Chr(10) & "Payments"
    With ActiveCell.Characters(Start:=1, Length:=18).Font
    .Name = "Arial"
    .FontStyle = "Bold"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    End With
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .ShrinkToFit = False
    .MergeCells = False
    ActiveCell.Offset(0, -3).Columns("A:A").EntireColumn.Select
    Selection.Columns.AutoFit
    ActiveCell.Offset(8, 0).Range("A1").Select
    Application.Goto Reference:="R10C2"

    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    With Selection
    .VerticalAlignment = xlCenter
    .Orientation = 0
    .AddIndent = False
    .ShrinkToFit = False
    .MergeCells = False
    End With
    ActiveCell.Select
    Selection.End(xlDown).Select
    Selection.End(xlUp).Select
    ActiveWorkbook.Save

    'All Sheets Updated.

    Sheets("PAI EFP MERGED").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.ClearContents
    Selection.AutoFilter Field:=17

    'Comin to the END.

    Application.Goto Reference:="R1C2"

    Selection.AutoFilter
    Sheets("PAIINV Formatted").Select
    Application.Goto Reference:="R1C1"
    ActiveCell.Columns("A:A").EntireColumn.Select
    'Selection.Delete Shift:=xlToLeft

    Selection.Delete Shift:=xlToLeft
    ActiveCell.Select
    Sheets("EFPEXT Formatted").Select
    Application.Goto Reference:="R1C1"
    ActiveCell.Columns("A:A").EntireColumn.Select
    Selection.Delete Shift:=xlToLeft
    ActiveCell.Select
    Sheets("PAI EFP MERGED").Select
    ActiveCell.Select

    'THIS CODE ADDS 5 BLANK LINES AND ADDS A NEW HEADER.
    Sheets("CLARK #026 #009").Select
    Application.Goto Reference:="R10C1"
    ActiveCell.Rows("1:5").EntireRow.Select
    Selection.Insert Shift:=xlDown
    ActiveCell.Select

    Sheets("DESIMONE #027 #006").Select
    Application.Goto Reference:="R10C1"
    ActiveCell.Rows("1:5").EntireRow.Select
    Selection.Insert Shift:=xlDown
    ActiveCell.Select

    Sheets("SALICONDRO #029 & #008").Select
    Application.Goto Reference:="R10C1"
    ActiveCell.Rows("1:5").EntireRow.Select
    Selection.Insert Shift:=xlDown
    ActiveCell.Offset(4, 1).Range("A1:R1").Select
    With Selection.Interior
    .ColorIndex = 15
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    End With
    ActiveCell.Offset(-1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "UNCOLLECTED Invoice Detail"
    With ActiveCell.Characters(Start:=1, Length:=26).Font
    .Name = "Arial"
    .FontStyle = "Bold"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    End With
    With Selection
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 1
    .ShrinkToFit = False
    .MergeCells = False
    End With
    With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .ShrinkToFit = False
    .MergeCells = False
    End With
    Selection.InsertIndent 1
    ActiveCell.Select
    Sheets("DESIMONE #027 #006").Select
    ActiveCell.Offset(4, 1).Range("A1:R1").Select
    With Selection.Interior
    .ColorIndex = 15
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    End With
    ActiveCell.Offset(-1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "UNCOLLECTED Invoice Detail"
    With ActiveCell.Characters(Start:=1, Length:=26).Font
    .Name = "Arial"
    .FontStyle = "Bold"
    .Size = 10
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    End With
    With Selection
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 1
    .ShrinkToFit = False
    .MergeCells = False
    End With
    Sheets("DESIMONE #027 #006").Select
    ActiveCell.Range("A1:R2").Select
    Selection.Copy
    Sheets("CLARK #026 #009").Select
    ActiveCell.Offset(3, 1).Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveCell.Select

    Sheets("PAI EFP MERGED").Select
    Sheets("PAIINV Formatted").Select
    Sheets("PAI EFP MERGED").Select
    ActiveCell.Select
    Application.ScreenUpdating = True
    End With
    ActiveWorkbook.Save
    End Sub


    *****

    How do I read your coding?
    Dim DataRng As Range, etc, etc.,
    ending with,
    DataRng1.End(xlToRight).

    *****

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •