Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA for VLoop (XP02)

    I use the following code to perform a VLookup on a range.

    Sub DoVLookup()
    For Each oCell in Range("A1:A50")
    On Error GoTo ErrorHandler
    Application.WorksheetFunction.VLookup(oCell), Range("C1:C100"), 1, False)
    Next oCell
    Exit Sub

    ErrorHandler:
    If not found in VLookup then append to last line in column C
    Resume

    End Sub


    My issue is when the next oCell is equal to the value that was just appended in Col C. I can not seem to get the function to recognize that the number appended is in the range("C1:C100"). In my case there are only 20 values in column C. Therefore I am not exceeding the 100.

    Thanks for your assistance,
    John

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VBA for VLoop (XP02)

    I don't understand what your code is supposed to do
    Your error handler line is an error. Vlookup line has 2 error:
    1) no parenthesis after ocell
    2) it is a function, you need to put the result somewhere

    You don't do anything with the result.

    What is the goal of the sub?

    Steve

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA for VLoop (XP02)

    Steve,

    Thanks for the reply. I keyed in the parenthesis by accident while posting it. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    To make a long story short, the reason for the appended value not being recognized is due to how VLookup handles text and numbers/values. The numers/values have to be in the list in ascending order. To overcome this issue I made each cell in Column C text.

    Thanks again,
    John

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VBA for VLoop (XP02)

    I still don't understand your question.

    Also, other than the typo, all my other questions were not answered. We can't answer your question(s) if we don't understand what you are doing. As written (even with the typo fixed) your code does nothing. It won't run.

    Steve

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA for VLoop (XP02)

    Steve,

    The purpose of the code is to look up each value in Col A's range to Col C. If the value is not in Col C then the value is appended to Col C. The goal is to end up with unique values in Col C. Essentially the code works just fine but I have encountered instances (not always) where I would end up with a duplicate value in Col C.

    This is the code I came up with prior to tweaking the values in column C to text:

    Sub DoVLookup()
    For Each oCell In Range("A1:A50")
    Application.StatusBar = oCell.Address
    On Error GoTo ErrorHandler
    Result = Application.WorksheetFunction.VLookup(oCell, Range("C1:C100"), 1, False)
    If Not Result = Empty Then
    MsgBox "Already Exists " & oCell ' Or add additional code here
    End If
    Line1:
    Next oCell
    Exit Sub

    ErrorHandler:
    Sheets("Sheet1").Range("C65536").End(xlUp).Offset( 1, 0) = oCell
    Resume Line1
    MsgBox "Finished"
    End Sub

    Hope this helps,
    John

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: VBA for VLoop (XP02)

    John, the code is now more meaningful, but it would help to see sample data. Also, now you have explained your objective, have you looked at Data | Filter Advanced, with the Unique Records Only and Copy To Range options?
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA for VLoop (XP02)

    Steve,

    I now see the pros and cons about error trapping in my example. It is always good to see things from someone elses point of view: that is how we learn.

    One other thing - Is it possible the obtain the address of the value if it is "InList"?

    Thanks,
    John

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VBA for VLoop (XP02)

    the value is given by:

    Application.WorksheetFunction.VLookup(rCell.Value, rLookup, 1, False)

    But this is exactly the same as rCell.value since you told it to find an exact match

    Steve

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA for VLoop (XP02)

    Steve,

    I became more curious to know the cell address of the value that was found in Col C. If the range in Col C was increased say to 5,000 rows and the code did find the value already existing on one of the 5,000 rows. Which row or cell address did it find it in?

    Thanks,
    John

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VBA for VLoop (XP02)

    Use MATCH instead of VLOOKUP (match would also work in the function. I used VLOOKUP, to keep with your "thought process")
    This will select the cell with an existing value and tell you the value and the address Replace the lines as directed in the code:

    <pre> If InList(rCell.Value, rLookup) Then
    'Replace lines from above line
    Set rExists = rLookup.Cells(Application.WorksheetFunction. _
    Match(rCell.Value, rLookup, 0))
    rExists.Select
    MsgBox rCell.Value & " in " & _
    rExists.Address & _
    " Already Exists "
    'to the next line in my other code
    Else</pre>


    Steve

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VBA for VLoop (XP02)

    I am not a big proponent of causing an error and trapping it in this way unless unavoidable. Vlookup will return an error if not found. There are ways around it.
    Here is some code. I created a function (using Vlookup) to tell if is is "InList". The function I created ignores the error and in doing so, keeps the variable empty, which is tested.

    Personally, I would get rid of the msgbox when you are already in the list, It can get annoying. [Before you get defensive, I suspect you put it in as a debugging tool to make sure you knew when things were "found"and you knew to eliminate it in the final version <img src=/S/grin.gif border=0 alt=grin width=15 height=15>].

    Steve

    <pre>Sub DoVLookupMod()
    Dim rCell As Range
    Dim rLookup As Range
    Dim iLookupCount As Integer
    Set rLookup = Range(Range("c1"), Range("C65536").End(xlUp))
    iLookupCount = rLookup.Count
    For Each rCell In Range("A1:A50")
    If Not IsEmpty(rCell) Then
    If InList(rCell.Value, rLookup) Then
    MsgBox "Already Exists " & rCell ' Or add additional code here
    Else
    iLookupCount = iLookupCount + 1
    Set rLookup = rLookup.Resize(iLookupCount, 1)
    rLookup.Cells(iLookupCount) = rCell.Value
    End If
    End If
    Next
    Set rLookup = Nothing
    Set rCell = Nothing
    MsgBox "Finished"
    End Sub

    Function InList(vValue, rng As Range) As Boolean
    Dim vResult
    On Error Resume Next
    vResult = Application.WorksheetFunction.VLookup(vValue, rng, 1, False)
    On Error GoTo 0
    InList = Not (IsEmpty(vResult))
    End Function</pre>


  12. #12
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA for VLoop (XP02)

    Steve,

    A great piece of code.

    Many thanks,
    John

Posting Permissions

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