Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Houston, Texas, USA
    Posts
    432
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBlookup - problem (excel 97)

    I have a group of data (GL Account Numbers) that are on a sperate sheet
    that is already opened. I want to take the description that I get on my
    first page and store that to a variable name then switch sheets and find
    that description. Once I have found it I want to store off the account
    numbers and then place them on the first sheet. I think it is VBLOOKUP.

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBlookup - problem (excel 97)

    Well, it sounds like you mean Vlookup to me, but I'm not sure whether you're talking formula or vba.

    to do this by formula, then assuming you've got something like the following on sheet COA:

    col a col b

    300501 ffoe cost
    300502 vehicles cost
    300503 plant cost
    300511 ffoe depn
    300512 vehicles depn
    300513 plant depn

    and then on another sheet you've got a list of gl accounts only in col a, the following in column b should do the trick:

    =vlookup(a1,COA!a:b,2,false)

    to do this in code I'd start along the lines of

    sheet2.cellx.value = application.WorksheetFunction.VLookup(arg1,arg2,ar g3,arg4)

    where the arguments tie in to the above example:
    what you want to look for,
    the range you want to search,
    the column of that range to return results from and
    an optional flag that indicates both whether an approximate match is to be made and whether the data is sorted. in your case, if you are dragging descriptions from a chart of accounts as I suspect then the data will be sorted but the more important factor here is that you need an exact match, so should specify FALSE. (ommitted = true)

    note that a lot of problems occur when trying to search for the text string 300500 in a list of numbers that contain the number 300500 or vice versa. If this is the case no match will be made, so make sure that the formats are the same.

    Hope thats what you wanted?

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: VBlookup - problem (excel 97)

    Dreyes

    Happy Holiday to you.

    OK its not <<< VBLOOKUP >>> but VLookup, for Vertical Lookup.

    And yes you can assign the value to look for, the description, in the database to look in, the Other "sperate" worksheet, and return the column out of the database, Account Numbers.

    But I should tell you that you want to make sure that the description match for example "Account Payable" and "Account<font color=red>s </font color=red> Payable" will not return the same results.

    Good Luck, HTH

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

Posting Permissions

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