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

    find value in row range... (2000 sr-1)

    with a macro in vba...

    assuming i ahve thsi range of cells:

    P1 to S1

    in this range are present a value similar
    in P1 ="A"
    in Q1="B"
    in R1="C"
    in S1 =D"

    i ahve the variale named TEST

    I would want to find the value of this variable in all cells or range and if the value of this var not is present in range show msgbox "the valueXX not present"

    note:
    exclude for controll if the var assume the value="00"

    eaxmple var="00" not show msgbox
    example var="K" show the msgbox
    ...

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

    Re: find value in row range... (2000 sr-1)

    <code>Dim rng As Range
    If Not Test = "00" Then
    Set rng = Range("P1:S1").Find(What:=Test, LookIn:=xlValues, LookAt:=xlWhole)
    If rng Is Nothing Then
    MsgBox 'The value " & Test & " was not found.", vbInformation
    End If
    End If</code>

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

    Re: find value in row range... (2000 sr-1)

    kiss....

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

    Re: find value in row range... (2000 sr-1)

    Hans the code go in msgbox with th value 0126/0800/0000 and 0126/0801/0000 but i have exclude THIS 2 VALUE from controll but why in other case the code go in msgbox?

    PART OF CODE
    ..........
    dim TEST_CE as string
    dim RNG as range
    DATA_INS = Replace((Sess0.SCREEN.GetString(4, 41, 14)), " ", "")

    Sheets("TOT_CE").Range("K" + RIGA_C).Value = DATA_INS

    For X = 9 To 20


    TEST_CE = Sess0.SCREEN.GetString(X, 3, 4) & "/" & Sess0.SCREEN.GetString(X, 8, 4) & "/" & Sess0.SCREEN.GetString(X, 13, 4)

    If Not TEST_CE = "0126/0801/0000" Or "0126/0800/0000" Then
    Set RNG = Sheets("TOT_CE").Range("P1:X1").Find(What:=TEST_CE , LookIn:=xlValues, LookAt:=xlWhole)
    If RNG Is Nothing Then
    MsgBox "NON TROVATO!", vbInformation
    End If
    End If
    ............

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

    Re: find value in row range... (2000 sr-1)

    The line

    If Not TEST_CE = "0126/0801/0000" Or "0126/0800/0000" Then

    should be

    If Not (TEST_CE = "0126/0801/0000" Or TEST_CE = "0126/0800/0000") Then

    Try single-stepping through the code (usung F8) to see what the value of TEST_CE is.

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

    Re: find value in row range... (2000 sr-1)

    ACCCCCCCCCCCCCCCCCCC..................

    The code not consider my range for exlusion....
    TEST_CE = Sess0.SCREEN.GetString(X, 3, 4) & "/" & Sess0.SCREEN.GetString(X, 8, 4) & "/" & Sess0.SCREEN.GetString(X, 13, 4)

    If Not (TEST_CE = "0126/0801/0000" Or TEST_CE = "0126/0800/0000" Or TEST_CE = " / / ") Then
    Set RNG = Sheets("TOT_CE").Range("P1:X1").Find(What:=Val(TES T_CE), LookIn:=xlValues, LookAt:=xlWhole)
    If RNG Is Nothing Then
    MsgBox "NON TROVATO!", vbInformation
    End If
    End If

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

    Re: find value in row range... (2000 sr-1)

    Why do you use Val(TEST_CE)? TEST_CE is not a number value.

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

    Re: find value in row range... (2000 sr-1)

    OK for val...
    but if the var assuming 0127/0099/0000 the code go on msgbox but i have this value in range....(in X1)

    TEST_CE = Sess0.SCREEN.GetString(X, 3, 4) & "/" & Sess0.SCREEN.GetString(X, 8, 4) & "/" & Sess0.SCREEN.GetString(X, 13, 4)

    If Not (TEST_CE = "0126/0801/0000" Or TEST_CE = "0126/0800/0000" Or TEST_CE = " / / ") Then
    Set RNG = Sheets("TOT_CE").Range("P1:X1").Find(What:=TEST_CE , LookIn:=xlValues, LookAt:=xlWhole)
    If RNG Is Nothing Then
    MsgBox "NON TROVATO!", vbInformation
    End If
    End If

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

    Re: find value in row range... (2000 sr-1)

    You should use the range P1:Y1 instead of P1:X1.

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

    Re: find value in row range... (2000 sr-1)

    ... hummm
    perpahs the cells is joined and the last cell in range not is X but Y???
    correct?

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

    Re: find value in row range... (2000 sr-1)

    Yes, that must be the cause.

Posting Permissions

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