Results 1 to 7 of 7

Thread: Vlookup (2003)

  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Vlookup (2003)

    I have several rows and columns of pipe delimited numbers. These numbers need to be updated. I have two worksheets, one with the lookup data; ie, old data in column A sorted ascending and the new data in col B and the other worksheet is the one I am working with.

    I am using VBA to get each cell, break the actual data out, look the values up in the lookup sheet, replace the values, build a new string and write out the new string to another cell.

    My problem is with Vlookup format.
    This is just for the instance where there is only 1 value in a cell.

    newstring = WorksheetFunction.VLookup(cellval, "IDX!a2:b248", 2, False) gives me an "Application-defined or object-defined error. I am not sure where my error is in the statement.

    Block of code: (note there is hundreds of rows that can have hundreds of delimited values)

    For rwIdx = 3 To 7
    For colIdx = 3 To 5
    With Worksheets("Sheet1").Cells(rwIdx, colIdx)
    'get cell value
    cellval = .Value
    Select Case cellval
    'is cell empty?
    Case Is = ""
    'last col
    If colIdx = 5 Then
    Exit For
    End If
    Case Is <> ""
    celllen = Len(.Value)
    'got data & no pipes
    If InStr(1, celllen, "|", 0) = 0 Then
    newstring = WorksheetFunction.VLookup(cellval, "IDX!a2:b248", 2, False)

    End If

    End Select
    End With
    Next colIdx

    Next rwIdx

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

    Re: Vlookup (2003)

    The second argument of VLookup is a Range, not a string:

    newstring = WorksheetFunction.VLookup(cellval, Worksheets("IDX").Range("A2:B248"), 2, False)

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Vlookup (2003)

    Hi Hans,

    I had actually got to this variant through several posts about VLookup in VBA, seemed to be the flavor dujor for executing. I actually started with =VLOOKUP(cellval, IDX!A2:B248, 2, FALSE) which gives me a "expected: list separator or )" error at the : . I also get the same error if I call out the worksheetfunction. I just never have used a worksheet function in VB, so I do not know what the syntax error is for arg2 when I am specifying the range in VB. From examples I've seen, I should be ok with =VLOOKUP(cellval, IDX!A2:B248, 2, FALSE) .

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

    Re: Vlookup (2003)

    To refer to a range in VBA, you can use the Range function, if necessary combined with the Worksheets and Workbooks functions. Examples:

    Range("C5") refers to cell C5 on the currently active worksheet. This is the VBA equivalent of C5.
    Worksheets("Sheet2").Range("C5") refers to cell C5 on worksheet Sheet2 in the active workbook. This is the VBA equivalent of Sheet2!C5.
    Workbooks("MyFile.xls").Worksheets("Sheet2").Range ("C5") refers to cell C5 on worksheet Sheet2 in the workbook MyFile.xls. This is the VBA equivalent of [MyFile.xls]Sheet2!C5.

    You can not refer to a range simply as Sheet2!C5 or as "Sheet2!C5" in VBA.

  5. #5
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Vlookup (2003)

    Why does the following violate your second example?

    Worksheets("IDX").Range("A2:B248")

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

    Re: Vlookup (2003)

    It doesn't - the only difference is that the examples I gave all refer to a single cell; Worksheets("IDX").Range("A2:B248") refers to a range of multiple cells.

  7. #7
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Vlookup (2003)

    Thanks for your assistance Hans, I was a little dense on this problem. After I was reviewing the thread, I was mixing a couple of code variants. Holiday cheers and late work is my excuse. Seems the core problem I was having was that I needed to declare the cellval variable as a variant and not a string. This also takes care of my followup question, which I will give for anyone following this mess in the future , can I use a range object for ARG2 and the answer is yes: so the following works:

    Set IDXrange = Worksheets("IDX").Range("a2:b248")

    newstring = WorksheetFunction.VLookup(cellval, IDXrange, 2, False)

Posting Permissions

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