Results 1 to 10 of 10
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Controll if in column are.... (2000 sr 1)

    ... assuming i have a var MY_VAR and that is filed with "4500", how to control if into column A if this value, existis?...
    The range in column a is A5:A65536.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Controll if in column are.... (2000 sr 1)

    Use the Find method of Range("A5:A65536"). You already have many code examples of using Find.

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controll if in column are.... (2000 sr 1)

    Hi Hans maked this, but i'm sure into column 3 are present "DECADALE" (var NUM_PERIODO are filled with DECADALE), and the macro go in

    MsgBox "NESSUN DATO DA FILTRARE CON QUESTO CRITERIO DI RICERCA! RIPETERE.", vbCritical

    Sub SHOW_FILTRED_DATA_1()
    Sheets("MANCA").Select
    With Worksheets("MANCA").Range("A4")
    If INS_SPORT <> "" Then
    .AutoFilter Field:=1, Criteria1:=INS_SPORT
    End If
    If SK <> "" Then
    .AutoFilter Field:=2, Criteria1:=SK
    End If
    If NUM_PERIODO <> "" Then
    .AutoFilter Field:=3, Criteria1:=NUM_PERIODO
    End If
    If DATA <> "" Then
    .AutoFilter Field:=8, Criteria1:=DATA
    End If
    End With

    End Sub
    Sub SHOW_FILTRED_DATA()

    Dim NoMatch As Boolean

    With Worksheets("MANCA")
    NoMatch = False
    If IsError(Application.Match(INS_SPORT, .Columns(1), 0)) Then NoMatch = True
    If IsError(Application.Match(SK, .Columns(2), 0)) Then NoMatch = True
    If IsError(Application.Match(NUM_PERIODO, .Columns(3), 0)) Then NoMatch = True
    If IsError(Application.Match(DATA, .Columns(8), 0)) Then NoMatch = True

    If NoMatch Then
    MsgBox "NESSUN DATO DA FILTRARE CON QUESTO CRITERIO DI RICERCA! RIPETERE.", vbCritical
    Exit Sub
    End If

    End With

    Call SHOW_FILTRED_DATA_1

    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Controll if in column are.... (2000 sr 1)

    Your code requires a match on column 1 AND column 2 AND column 3 AND column 8, and you don't check in SHOW_FILTRED_DATA whether any of the criteria is blank.

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controll if in column are.... (2000 sr 1)

    AAAARRRRGGGG...
    if i have uderstand i can use a param "OR"...

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Controll if in column are.... (2000 sr 1)

    It depends on what you want.

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controll if in column are.... (2000 sr 1)

    Perhaps solved with this, but the code not is "elegant", have idea to change?
    Sub SHOW_FILTRED_DATA_1()

    Sheets("MANCA").Select
    With Worksheets("MANCA").Range("A4")
    If INS_SPORT <> "" Then
    .AutoFilter Field:=1, Criteria1:=INS_SPORT
    End If
    If SK <> "" Then
    .AutoFilter Field:=2, Criteria1:=SK
    End If
    If NUM_PERIODO <> "" Then
    .AutoFilter Field:=3, Criteria1:=NUM_PERIODO
    End If
    If DATA <> "" Then
    .AutoFilter Field:=8, Criteria1:=DATA
    End If
    End With

    End Sub
    Sub SHOW_FILTRED_DATA()

    Dim NoMatch As Boolean

    With Worksheets("MANCA")
    NoMatch = False
    If INS_SPORT = "" Then GoTo 10
    If IsError(Application.Match(INS_SPORT, .Columns(1), 0)) Then NoMatch = True
    If NoMatch Then
    MsgBox "NESSUN DATO DA FILTRARE CON QUESTO CRITERIO DI RICERCA! RIPETERE.", vbCritical
    Exit Sub
    End If
    10:

    If SK = "" Then GoTo 20
    If IsError(Application.Match(SK, .Columns(2), 0)) Then NoMatch = True
    If NoMatch Then
    MsgBox "NESSUN DATO DA FILTRARE CON QUESTO CRITERIO DI RICERCA! RIPETERE.", vbCritical
    Exit Sub
    End If
    20:

    If NUM_PERIODO = "" Then GoTo 30
    If IsError(Application.Match(NUM_PERIODO, .Columns(3), 0)) Then NoMatch = True
    If NoMatch Then
    MsgBox "NESSUN DATO DA FILTRARE CON QUESTO CRITERIO DI RICERCA! RIPETERE.", vbCritical
    Exit Sub
    End If
    30:

    If DATA = "" Then GoTo 40

    If IsError(Application.Match(DATA, .Columns(8), 0)) Then NoMatch = True
    If NoMatch Then
    MsgBox "NESSUN DATO DA FILTRARE CON QUESTO CRITERIO DI RICERCA! RIPETERE.", vbCritical
    Exit Sub
    End If
    40:
    End With

    Call SHOW_FILTRED_DATA_1

    End Sub

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Controll if in column are.... (2000 sr 1)

    You don't need to repeat the If NoMatch ... End If part, you only need to use it once, above Call SHOW_FILTRED_DATA_1.

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controll if in column are.... (2000 sr 1)

    ... Now i not uderstand...
    Where?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Controll if in column are.... (2000 sr 1)

    Exactly what I wrote. Please read my reply again.

Posting Permissions

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