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

    found not fouond stop (2000 sr 1)

    thi code make a serching on a column, have a problem:

    if the value not found appaear msg box "non trovato!", how to make that?

    Private Sub CommandButton5_Click()

    Dim CL As Object
    Dim T As String

    If TextBox9.Value = "" Then
    msgbox "INSERIRE UN VALORE PER LA RICERCA NEL CAMPO NOMINATIVO!", vbCritical
    Exit Sub
    End If

    Set NOMIMATIVO = ActiveSheet.Range(Cells(3, 11), Cells(3, 11).End(xlDown))

    For Each CL In NOMIMATIVO

    G = Me.TextBox9.Text

    If CL.Value Like "*" & G & "*" Then

    DOMANDA = msgbox("HO TROVATO " & CL.Value & ", VUOI FERMARTI ?", vbYesNo)

    If DOMANDA = vbYes Then

    Me.TextBox9.Value = ""

    T = CL.Row

    Me.ScrollBar1.Value = T

    Me.TextBox1.Value = Sheets("RATE").Cells(T, "A").Value
    Me.TextBox2.Value = Sheets("RATE").Cells(T, "K").Value
    Me.TextBox3.Value = Sheets("RATE").Cells(T, "J").Value
    Me.ComboBox1.Value = Sheets("RATE").Cells(T, "M").Value
    Me.TextBox5.Value = Sheets("RATE").Cells(T, "AD").Value
    Me.TextBox8.Value = Sheets("RATE").Cells(T, "E").Value
    Me.TextBox10.Value = Sheets("RATE").Cells(T, "I").Value
    Me.TextBox11.Value = Sheets("RATE").Cells(T, "L").Value
    Me.TextBox12.Value = Sheets("RATE").Cells(T, "AB").Value

    If Sheets("RATE").Cells(T, "AI").Value <> "" Then
    USER = Sheets("RATE").Cells(T, "AI").Value
    Me.TextBox13.Value = Application.WorksheetFunction.VLookup(USER, Worksheets("TABELLA").Range(Worksheets("TABELLA"). Range("O1:O3"), Worksheets("TABELLA").Range("P3").End(xlUp)), 2, False)
    Else
    Me.TextBox13.Value = Sheets("RATE").Cells(T, "AI").Value
    End If

    Me.TextBox14.Value = Sheets("RATE").Cells(T, "G").Value
    Me.TextBox15.Value = Sheets("RATE").Cells(T, "B").Value
    Me.TextBox16.Value = Sheets("RATE").Cells(T, "D").Value
    Me.TextBox18.Value = Sheets("RATE").Cells(T, "N").Value
    Me.TextBox19.Value = Sheets("RATE").Cells(T, "T").Value
    Me.TextBox20.Value = Sheets("RATE").Cells(T, "AE").Value

    If Sheets("RATE").Cells(T, "AF").Value > 0 Then
    Me.TextBox21.Value = Format((Sheets("RATE").Cells(T, "AF").Value), "#0000") * 1
    Else
    Me.TextBox21.Value = ""
    End If

    Me.TextBox22.Value = Sheets("RATE").Cells(T, "R").Value
    Me.TextBox23.Value = Sheets("RATE").Cells(T, "H").Value
    Me.TextBox24.Value = Sheets("RATE").Cells(T, "AJ").Value
    Me.TextBox6.Value = Format((Sheets("RATE").Cells(T, "F").Value), "##,##0.00")
    Me.TextBox26.Value = Sheets("RATE").Cells(T, "P").Value

    Exit For
    End If
    End If
    Next

    Label42.Caption = Sheets("RATE").Range("E1").Value
    Label41.Caption = Sheets("RATE").Range("C1").Value
    Label110.Caption = Sheets("RATE").Range("E1").Value - Sheets("RATE").Range("C1").Value

    'msgbox "NOMINATIVO NON TROVATO", vbInformation
    'TextBox9.Value = ""
    'Me.TextBox9.SetFocus

    End Sub

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

    Re: found not fouond stop (2000 sr 1)

    Add the following at the beginning of the code:

    Dim blnFound As Boolean

    Immediately above the line Exit for, insert this line:

    blnFound = True

    At the end of the code, add the following:

    If blnFound = False Then
    MsgBox "NOMINATIVO NON TROVATO", vbInformation
    End If

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

    Re: found not fouond stop (2000 sr 1)

    Good but....
    If you see the code, if the macro find many string "G" and not one, the macro permit to press the button NO "do you stop?" and if the user press NO msg box show all item with a part of "G" until not are present ani item with "G"... when the last item is show the macro goto the your instruction:

    If blnFound = False Then
    msgbox "NOMINATIVO NON TROVATO", vbInformation
    End If

    is different...

    Innthis case when all item are show with the param "G" for me the msg box is: "finish item with "G"


    hope you have understand me?:-(

    my new code:
    Private Sub CommandButton5_Click()

    Dim CL As Object
    Dim T As String
    Dim blnFound As Boolean

    If TextBox9.Value = "" Then
    msgbox "INSERIRE UN VALORE PER LA RICERCA NEL CAMPO NOMINATIVO!", vbCritical
    Exit Sub
    End If

    Set NOMIMATIVO = ActiveSheet.Range(Cells(3, 11), Cells(3, 11).End(xlDown))

    For Each CL In NOMIMATIVO

    G = Me.TextBox9.Text

    If CL.Value Like "*" & G & "*" Then

    DOMANDA = msgbox("HO TROVATO " & CL.Value & ", VUOI FERMARTI ?", vbYesNo)

    If DOMANDA = vbYes Then

    Me.TextBox9.Value = ""

    T = CL.Row

    Me.ScrollBar1.Value = T

    Me.TextBox1.Value = Sheets("RATE").Cells(T, "A").Value
    Me.TextBox2.Value = Sheets("RATE").Cells(T, "K").Value
    Me.TextBox3.Value = Sheets("RATE").Cells(T, "J").Value
    Me.ComboBox1.Value = Sheets("RATE").Cells(T, "M").Value
    Me.TextBox5.Value = Sheets("RATE").Cells(T, "AD").Value
    Me.TextBox8.Value = Sheets("RATE").Cells(T, "E").Value
    Me.TextBox10.Value = Sheets("RATE").Cells(T, "I").Value
    Me.TextBox11.Value = Sheets("RATE").Cells(T, "L").Value
    Me.TextBox12.Value = Sheets("RATE").Cells(T, "AB").Value

    If Sheets("RATE").Cells(T, "AI").Value <> "" Then
    USER = Sheets("RATE").Cells(T, "AI").Value
    Me.TextBox13.Value = Application.WorksheetFunction.VLookup(USER, Worksheets("TABELLA").Range(Worksheets("TABELLA"). Range("O1:O3"), Worksheets("TABELLA").Range("P3").End(xlUp)), 2, False)
    Else
    Me.TextBox13.Value = Sheets("RATE").Cells(T, "AI").Value
    End If

    Me.TextBox14.Value = Sheets("RATE").Cells(T, "G").Value
    Me.TextBox15.Value = Sheets("RATE").Cells(T, "B").Value
    Me.TextBox16.Value = Sheets("RATE").Cells(T, "D").Value
    Me.TextBox18.Value = Sheets("RATE").Cells(T, "N").Value
    Me.TextBox19.Value = Sheets("RATE").Cells(T, "T").Value
    Me.TextBox20.Value = Sheets("RATE").Cells(T, "AE").Value

    If Sheets("RATE").Cells(T, "AF").Value > 0 Then
    Me.TextBox21.Value = Format((Sheets("RATE").Cells(T, "AF").Value), "#0000") * 1
    Else
    Me.TextBox21.Value = ""
    End If

    Me.TextBox22.Value = Sheets("RATE").Cells(T, "R").Value
    Me.TextBox23.Value = Sheets("RATE").Cells(T, "H").Value
    Me.TextBox24.Value = Sheets("RATE").Cells(T, "AJ").Value
    Me.TextBox6.Value = Format((Sheets("RATE").Cells(T, "F").Value), "##,##0.00")
    Me.TextBox26.Value = Sheets("RATE").Cells(T, "P").Value

    blnFound = True

    Exit For
    End If
    End If
    Next

    Label42.Caption = Sheets("RATE").Range("E1").Value
    Label41.Caption = Sheets("RATE").Range("C1").Value
    Label110.Caption = Sheets("RATE").Range("E1").Value - Sheets("RATE").Range("C1").Value

    If blnFound = False Then
    msgbox "NOMINATIVO NON TROVATO", vbInformation
    End If

    End Sub

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

    Re: found not fouond stop (2000 sr 1)

    Does the attached code do what you want? You can modify the Select Case statement at the end.

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

    Re: found not fouond stop (2000 sr 1)

    NO WORDS!
    Tks for patience...

    See this and tell me if you like(only to make a little elegance of code):

    msgbox "TUTTI I NOMINATIVI CON " & G & " SONO STATI VISUALIZZATI.", vbInformation

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

    Re: found not fouond stop (2000 sr 1)

    Yes, that's more user-friendly.

Posting Permissions

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