Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post

    vLookup in VBA (2003 SP1)

    The help says I can use some functions from excel in vba. So I'm trying vLookup as I want to grab a value from another sheet. This fails with a "run-time error '1004' Unable to get the vlookup property of the worksheetfunction class." I noticed in help that I might need to use the range object, thus the second example (which I added the appropriate code around it) that fails.

    ActiveCell.FormulaR1C1 = Application.WorksheetFunction.VLookup("A" & iRownum, Sheets("Emp_No"), 2)
    -------------------------------------------------
    iRowCount = iRownum - 2 'Set the number of rows to work with before resetting the counter
    iRownum = 2
    Do While iRownum <= iRowCount
    Range("Q" & iRownum).Select
    ActiveCell.FormulaR1C1 = Application.WorksheetFunction.VLookup(Range("A" & iRownum), Sheets("Emp_No"), 2)
    Loop



    Any ideas?

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

    Re: vLookup in VBA (2003 SP1)

    VLookup has 4 arguments (of which the last is optional):
    1. <LI>The lookup value - this can be a literal value, or the value of a cell, or an expression.
      <LI>The lookup range - this must be a range on a worksheet, not a worksheet.
      <LI>The column to return the result from.
      <LI>The lookup type - TRUE (the default) for an approximate match, FALSE for an exact match.
    So you will have to specify a range as second argument instead of a worksheet.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post

    Re: vLookup in VBA (2003 SP1)

    Hmm. I remember that now. It's been a while since i've worked on this and as you already know, my VBA skill level stinks. That's a bummer. The 'emp_no' sheet holds a lookup that gets manually updated every time this is run. The data is exported from another system to a text file, manually loaded into this sheet before the vba is run. It would help if I just had the macro do the file loading and range naming, but I don't konw how to do that in VBA yet! I guess I can manually create a named range (incl a few extra rows just in case) and test and see if a range stays named as data is replaced. Thanks again!

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

    Re: vLookup in VBA (2003 SP1)

    If you always import into Emp_No, starting at A1, you can use Worksheets("Emp_No").Range("A1").CurrentRegion as range (2nd argument).

    You can try recording a macro of importing the text file, it'll give you an idea of the code needed.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post

    Re: vLookup in VBA (2003 SP1)

    Thanks, so that worked perfectly. Now I have another lookup that's giving me problems. I'm trying this and I get that 1004 error again.;I THINK it's because vLookup wants the lookup table key to be in column 1 and in this case it's not. The field I'm using to grab the data is, as you can see. The lookup table has a few columns before the one that's matching the source. It's in column C. The value to return to the main worksheet is 4 columns past that in column G. Do I need to use something other than vLookup?

    Worksheets("SALARY").Activate
    Range("R" & iRownum).Select
    ActiveCell.FormulaR1C1 = Application.WorksheetFunction.VLookup(Range("A" & iRownum), Worksheets("SUPV").Range("C1").CurrentRegion, 4)
    iRownum = iRownum + 1

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

    Re: vLookup in VBA (2003 SP1)

    There are several possibilities. For example, assuming that column C has no gaps:

    ... Application.WorksheetFunction.VLookup(Range("A" & iRownum), Worksheets("SUPV").Range(Worksheets("SUPV").Range( "C1"), Worksheets("SUPV").Range("C1").End(xlDown).Offset( 0, 4)), 4)

  7. #7
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post

    Re: vLookup in VBA (2003 SP1)

    Nice. Thanks again and again. Heck, I even understand what this does! However I would not have been able to come up with it without you.

  8. #8
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post

    Re: vLookup in VBA (2003 SP1)

    Hans. I'm back to this program because something stopped? working (probably never did <g>). The purpose of the code is to grab a supervisor number from the SUPV sheet and add it to the correct Employee on the SALARY sheet. The result is missing some supervisor numbers and I can't figure out why. There ARE some supervisor numbers on the SUPV sheet that are zero. It's missing some right after those, but no complete pattern yet. I'm working to find a pattern but nothing yet. Here's my code in case you or anyone can see a blatent problem.
    ---------------
    '----------------------------------------ADD Supv_No to source sheet---------------------------------
    vRawNum = "OK"
    iRowCount = iRownum - 1 ' Set the number of rows to work with before resetting the counter
    iRownum = 2 ' Use iRowCount from above for loop counter
    Do While iRownum <= iRowCount
    Worksheets("SUPV").Activate
    ' Format column C/"Code" to number w/o P
    Range("C" & iRownum).Select
    vRawNum = Right(Range("C" & iRownum).Value, 8)
    Selection.NumberFormat = "0"
    ActiveCell.FormulaR1C1 = vRawNum

    ' Format column G/"Personnel number of superior (Org. Manag" to number
    Range("G" & iRownum).Select
    Selection.NumberFormat = "0"
    ActiveCell.Value = ActiveCell.Value

    ' Do a lookup using Personnel number on the Emp_no sheet and bring back 'G' to last column
    Worksheets("SALARY").Activate
    Range("R" & iRownum).Select
    ActiveCell.FormulaR1C1 = Application.WorksheetFunction.VLookup(Range("A" & iRownum), Worksheets("SUPV").Range(Worksheets("SUPV").Range( "C1"), Worksheets("SUPV").Range("C1").End(xlDown).Offset( 0, 5)), 5)

    iRownum = iRownum + 1

    Loop

    End Sub

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

    Re: vLookup in VBA (2003 SP1)

    Without seeing the workbook it's hard to say. Does it work better if you add False as fourth argument to VLookup?

    ActiveCell.FormulaR1C1 = Application.WorksheetFunction.VLookup(Range("A" & iRownum), Worksheets("SUPV").Range(Worksheets("SUPV").Range( "C1"), Worksheets("SUPV").Range("C1").End(xlDown).Offset( 0, 5)), 5, False)

  10. #10
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post

    Re: vLookup in VBA (2003 SP1)

    I found a few things wrong with the latest data they ran through it. For one, not all the sheets were sorted correctly. If I understand your response and my research, False should take care of that. They guaranteed they would always be sorted by emp_no. I should have known <g>. So firstly, I was thinking of adding a sort. Do I not need to with False? I just read the help and I DO only want an exact match.

    So if that's not true... I sorted manually the way I wanted, recording a macro. Got this below. I'd rather just say sort the whole thing by A2 for each of the sheets. Can I tell the first range to do that rather than A1:P501?
    Range("A1:P501").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortTextAsNumbers

    There is another data problem in that all the source data sheets MUST have the same emloyee list (and sorted the same way as I mentioned). The main sheet doesn't have one person that was in the other three. Yuck. I don't have a clue on how to check for matching rows of people... and I think I need to since I clearly can't trust the source!

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

    Re: vLookup in VBA (2003 SP1)

    You can use

    Range("A1").CurrentRegion.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortTextAsNumbers

    This will sort the table with upper left corner A1 on column A, whatever the size of the table.

    See Duplicate And Unique Items In Lists for some ideas on how to compare lists - see the entries near the end.

  12. #12
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post

    Re: vLookup in VBA (2003 SP1)

    Thanks again! I'd still like to know if adding False as the fourth parameter of vLookup will make this unnecessary. Also, without it, am I in danger of bringing back an incorrect supervisor number?

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

    Re: vLookup in VBA (2003 SP1)

    If you add False as 4th parameter to VLookup, it will look for an exact match, and the lookup list won't need to be sorted. If no match can be found, an error will occur.

    If you omit the 4th argument, the list MUST be sorted, and VLookup will stop at the largest value from the list that is smaller than or equal to the value searched for, then returm the value from the appropriate column in that row.

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: vLookup in VBA (2003 SP1)

    Adding False as the last parameter does make it unnessary to sort the lookup table. If I understand what you are doing, leaving False off (or using True) could cause you to retrieve an incorrect Supervisor. If you leave False off or use True, the VLOOKUP will do an approximate match. If you lookup a supervisor number that is not in the table, it will return the largest value in the lookup column that is less than the value you are looking up. If I understand what you are doing correctly, that is NOT what you want.
    Legare Coleman

  15. #15
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Greeley, Colorado
    Posts
    188
    Thanks
    8
    Thanked 1 Time in 1 Post

    Re: vLookup in VBA (2003 SP1)

    Got it. Thanks. So I added False (and am not going to sort the sheets) and the line looks like this.
    ActiveCell.FormulaR1C1 = Application.WorksheetFunction.VLookup(Range("A" & iRownum), Worksheets("Emp_No").Range("A1").CurrentRegion, 2, False)
    The error message I get is "Run-time error '1004': Unable to get the VLookup property of the WorksheetFunction class"
    Any ideas?

Page 1 of 2 12 LastLast

Posting Permissions

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