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

    Value not found in lookup... (2000 sr-1)

    the macro CICLA_SHEET_TEST scann in all column for each sheet LUST*** and summ value in column P of sheet TABELLA based cod SERV...
    dubt on cod SERV = NX???
    the macro not found NX
    Naturally other way are welcome....

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

    Re: Value not found in lookup... (2000 sr-1)

    There is no macro in the workbook.

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

    Re: Value not found in lookup... (2000 sr-1)

    Attached....

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

    Re: Value not found in lookup... (2000 sr-1)

    When looking for "NX", the code finds "CONX" and increments the value for that row. To avoid this, specify that you want to match the whole cell instead of part of the cell. Change all occurrences of

    Set Found_INDEX = WS.Columns("O:O").Find(SERV, LookIn:=xlFormulas)

    to

    Set Found_INDEX = WS.Columns("O:O").Find(SERV, LookIn:=xlFormulas, LookAt:=xlWhole)

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

    Re: Value not found in lookup... (2000 sr-1)

    WORK FINE NOW!!!!
    Tks.
    But wath you think about my code?
    Elegant?

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

    Re: Value not found in lookup... (2000 sr-1)

    You can simplify the AGGIORNA_SERV_TEST procedure a bit:
    <code>
    Sub AGGIORNA_SERV_TEST(FOGLIO, SERV)
    Dim ULTIMA As Long
    Dim WS As Worksheet
    Dim Found_INDEX As Range
    Dim RIGA1 As Long
    Dim I As Long
    Dim Colonna As String

    Set WS = Worksheets("TABELLA")

    Select Case FOGLIO
    Case "LUST091"
    Colonna = "H"
    Case "LUST10C"
    Colonna = "I"
    Case Else
    Colonna = "E"
    End Select

    ULTIMA = Worksheets(FOGLIO).Cells(Rows.Count, Colonna).End(xlUp).Row
    For I = 2 To ULTIMA
    If Sheets(FOGLIO).Range(Colonna & I) = SERV Then
    Set Found_INDEX = WS.Columns("O:O").Find(SERV, LookIn:=xlFormulas)
    If Not Found_INDEX Is Nothing Then
    RIGA1 = Found_INDEX.Row
    WS.Range("P" & RIGA1) = WS.Range("P" & RIGA1).Value + 1
    End If
    End If
    Next I
    End Sub
    </code>
    This way, you don't have to repeat code that is the same except for the column letter.

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

    Re: Value not found in lookup... (2000 sr-1)

    Sorry for delay....
    good and elelgant solution...!
    note:My mind not is your;-)

Posting Permissions

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