Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Help with error handing

  1. #1
    Guest

    Default

    I am using the Marco below, pieced together from previous code from this board, to move selected data to a new sheet. It works like I need except that if a user puts the column letter instead of the column number for the Auto Filter field, flagged in bold in the Marco, I get and error. How can this error be handled? I used on error Resume Next and it takes care of the error but instead of filtered data all data is put in the new sheet, thought there might be a better way, Thanks for your help

    Option Explicit
    Dim FilterCriteria
    Dim CurrentsheetName As String
    Dim NewFileName As String
    Dim ws As Worksheet
    Dim Flag As Boolean
    Dim C

    Public Sub Filter_Data()
    Flag = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    CurrentsheetName = ActiveSheet.Name
    FilterCriteria = UCase(InputBox("Enter Your Filter Criteria"))

    'CHANGE THIS RANGE WHEN UPDATING THE RANGE
    'THERE ARE two PLACES THAT NEED TO BE CHANGED!
    For Each C In Range("a1:f752") '#1
    If UCase(C.Value) = FilterCriteria Then Flag = True: Exit For
    Next C
    If Not Flag Or FilterCriteria = "" Then MsgBox "No Match To Your Criteria ": Exit Sub
    'CHANGE THIS RANGE WHEN UPDATING THE RANGE
    'THERE ARE two PLACES THAT NEED TO BE CHANGED!
    Range("A1:f752").Select '#2
    Selection.AutoFilter
    'The error happens below if someone puts the column letter and not the number
    Selection.AutoFilter field:=(InputBox("Enter Column NUMBER, A=1, B=2, C=3, D=4, E=5, F=6... THIS MUST BE A NUMBER AND NOT A LETTER")), Criteria1:=FilterCriteria
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy

    With Worksheets.Add
    .Range("A1").PasteSpecial Paste:=xlPasteAll
    .Move after:=Worksheets(Worksheets.Count)
    For Each ws In Worksheets
    If ws.Name = FilterCriteria Then ws.Delete
    Next ws
    .Name = FilterCriteria
    End With

    Cells.Select
    Selection.Columns.AutoFit
    Range("A1").Select
    Worksheets(CurrentsheetName).Activate
    Selection.AutoFilter field:=1
    Selection.AutoFilter
    Range("B65536").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, 0).Select
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    End Sub

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can use the inputbox of the Excel application rather than the VBA inputbox. The Excel one allow you to restrict the values the user can enter by setting it's type argument to one of the following or a combination (e.g. for text and number use 3)


    0 A formula
    1 A number
    2 Text (a string)
    4 A logical value (True or False)
    8 A cell reference, as a Range object
    16 An error value, such as #N/A
    64 An array of values

    Just change the line with the second inputbox to this:-

    Selection.AutoFilter field:=(Application.InputBox(prompt:="Enter Column NUMBER, A=1, B=2, C=3, D=4, E=5, F=6... THIS MUST BE A NUMBER AND NOT A LETTER", Type:=1)), Criteria1:=FilterCriteria


    HTH,
    D

  3. #3
    Guest

    Default

    Thanks DK, always get fast and useful info on this board

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
  •