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

    cicling item not present (2000 sr 1)

    in column A have list of value similar:
    45000000
    45000001
    45000031
    45000033
    ....
    45999999

    how to cicling , (with a for next or other way) the not present value in this column and store the the value in a var...

    example:

    in the range 45000001 to 45000031 not present 45000002, 45000003, ....45000030

    note:
    the first value is 45000000 the last 45999999

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

    Re: cicling item not present (2000 sr 1)

    What do you wsnt to do with the missing values?
    Is the list always sorted in ascending order?

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

    Re: cicling item not present (2000 sr 1)

    What do you wsnt to do with the missing values? YES(perfect, conosci molto ben il mio inglese;-) you know very well my english)
    Is the list always sorted in ascending order?YES (already ordered with other macro)

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

    Re: cicling item not present (2000 sr 1)

    I meant. of course, "what do you want to do with the missing values?", or "how are you going to use the missing values?".
    Could you answer that question, please? "YES" or "NO" is not an appropriate answer.

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

    Re: cicling item not present (2000 sr 1)

    "what do you want to do with the missing values" ... store the missing value in a VAR for next use
    example:

    if the value is already present skip this value, is missing goto the next line of code...

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

    Re: cicling item not present (2000 sr 1)

    That's not detailed enough. Since there can be many missing values, you will need an array or a collection to store the missing values in. I need to have an idea of how you will use this array or collection.

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

    Re: cicling item not present (2000 sr 1)

    for x = 1 to 45999999
    if value is missing goto
    call mymacro(value missing)
    end if
    next x

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

    Re: cicling item not present (2000 sr 1)

    OK. Try this:

    Dim x As Long
    Dim lngMin As Long
    Dim lngMax As Long
    lngMin = Application.Min(Range("A:A"))
    lngMax = Application.Max(Range("A:A"))
    For x = lngMin To lngMax
    If Not IsNumeric(Application.Match(x, Range("A:A"), 0)) Then
    Call MyMacro(x)
    End If
    Next x

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

    Re: cicling item not present (2000 sr 1)

    Code arranged for the sheet attached... but not work

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

    Re: cicling item not present (2000 sr 1)

    Of course it doesn't work. You provided incorrect information. In your first post, you wrote that column A contains numbers, but in reality it contains text values. The code I wrote works for numbers only.
    To work around this, insert formulas in column B that extract the number values from column A, and use column B in the macro. See attached version.
    Attached Files Attached Files

Posting Permissions

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