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

    Still on vlookup (2000 sr 1)

    If i insert a value in box NR. CONTRATTO (insert for example 143034) the vlooukp not work, error 1004????????????

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

    Re: Still on vlookup (2000 sr 1)

    The values in column G are numbers, but you try to look up a string (text) value. Try

    Me.txtLoc.Value = Application.WorksheetFunction.VLookup(Val(TEST_CTR), ...

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

    Re: Still on vlookup (2000 sr 1)

    good now work....
    But have a prob also on the line to find value in column G... in effect is a part of my old code to controll into a column is present a value, the line are:

    Set RNG = ELENCO.Range("G3:G5000").End(xlDown).Find( _
    What:=TEST_CTR, LookIn:=xlValues, lookat:=xlWhole)

    If Not RNG Is Nothing Then

    MsgBox "NUMERO CONTRATTO ERRATO O INESITENTE!", vbCritical
    Me.txtPart.Value = ""
    Me.txtLoc.Value = ""
    Me.txtPart.SetFocus
    Exit Sub

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

    Re: Still on vlookup (2000 sr 1)

    1) As noted before, you don't need .End(xlDown) here.
    2) The test is the wrong way round. It should be

    If RNG Is Nothing Then

    You can simplify this part of the code to

    Set RNG = ELENCO.Range("G3:G5000").Find( _
    What:=TEST_CTR, LookIn:=xlValues, LookAt:=xlWhole)

    If RNG Is Nothing Then
    MsgBox "NUMERO CONTRATTO ERRATO O INESITENTE!", vbCritical
    Me.txtPart = ""
    Me.txtLoc = ""
    Me.txtPart.SetFocus
    Else
    Me.txtLoc = RNG.Offset(0, 1)
    End If

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

    Re: Still on vlookup (2000 sr 1)

    Tks ...
    But during your answer i have found in my code other solution, and it work...

    Now i have the prob to fill other text box.
    ... if you see the comment line, i would to fill the related text box after the value are found...
    in effect if the value found is 143324 (line 15) fill the related textbox...

    TEST_CTR = Me.txtPart.Value
    INDEX = TEST_CTR

    Set Found_INDEX = ELENCO.Columns("G:G").Find((TEST_CTR), LookIn:=xlFormulas)
    If Found_INDEX Is Nothing Then

    MsgBox "NUMERO CONTRATTO ERRATO O INESITENTE!", vbCritical
    Me.txtPart.Value = ""
    Me.txtLoc.Value = ""
    Me.txtPart.SetFocus
    Exit Sub

    Else

    End If

    Me.txtLoc.Value = Application.WorksheetFunction.VLookup(Val(TEST_CTR ), Worksheets("STORICO").Range(Worksheets("STORICO"). Range("G2"), Worksheets("STORICO").Range("H5000").End(xlUp)), 2, False)

    'Me.TextBox1.Value = RELATED VALUE OF CELLS F15
    'Me.TextBox2.Value = RELATED VALUE OF CELL B15
    'Me.TextBox3.Value = RELATED VALUE OF CELL I15
    'Me.TextBox4.Value = RELATED VALUE OF CELL J15

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

    Re: Still on vlookup (2000 sr 1)

    Me.txtLoc = Found_Index.Offset(0, 1)
    Me.TextBox1 = Found_INDEX.Offset(0, -1)
    Me.TextBox2 = Found_INDEX.Offset(0, -5)
    Me.TextBox3 = Found_INDEX.Offset(0, 2)
    Me.TextBox4 = Found_INDEX.Offset(0, 3)

    BTW you don't need Else

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

    Re: Still on vlookup (2000 sr 1)

    GREAT!!!!!!!!!!!!

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

    Re: Still on vlookup (2000 sr 1)

    only one...
    I would want to store in a var the real number of line where is found the calue is possible?
    Similar:

    Found_INDEX=NUMBER_of_line

    is 15 in the case of discussion...

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

    Re: Still on vlookup (2000 sr 1)

    In the code you have now, Found_Index is a range, not a number. The code to get the row number is

    Dim rng As Range
    Dim Found_Index As Long
    Set rng = ELENCO.Columns("G:G").Find((TEST_CTR), LookIn:=xlFormulas)
    If rng Is Nothing Then
    ...
    End If
    Found_Index = rng.Row

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

    Re: Still on vlookup (2000 sr 1)

    Vlookup "the return"...
    I use this macro to insert in column K a value NO AUT or OK . In effect i would want to insert in column K "NO AUT." if the value in column J not is present in column U... else "OK"
    But now in line 21 to 24 is the same value.... the macro insert "NO AUT", why?

    Naturally orther way are welcome:-)

    Sub TROVA_MATRICOLE_TAB_A()

    Dim lRow As Long
    Dim RIGA As String

    RIGA = 3
    Set ELENCO = Worksheets("STORICO")

    While Not ELENCO.Range("J" + RIGA) = ""

    With ELENCO
    CODICE_R = Right(ELENCO.Range("J" & RIGA), 5)
    lRow = 0
    On Error Resume Next
    lRow = Application.WorksheetFunction.Match(CODICE_R, _
    Worksheets("STORICO").Range(Worksheets("STORICO"). Range("P2"), Worksheets("STORICO").Range("P100").End(xlUp)), False)
    On Error GoTo 0
    If lRow = 0 Then
    ELENCO.Range("K" & RIGA) = "NO AUT."
    Else
    ELENCO.Range("K" & RIGA) = "OK"
    End If
    End With
    RIGA = RIGA + 1
    Wend
    End Sub

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

    Re: Still on vlookup (2000 sr 1)

    In your code, you llook up a value in
    Worksheets("STORICO").Range(Worksheets("STORICO"). Range("P2"), Worksheets("STORICO").Range("P100").End(xlUp))
    This is in column P, not in column U.
    And you should use Val(CODICE_R), just like in <post:=577,627>post 577,627</post:>.

Posting Permissions

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