VBA Vlookup Range


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 12 of 12

Thread: VBA Vlookup Range

  1. #1
    Join Date
    Mar 2005
    Posts
    110

    VBA Vlookup Range

    Hi,
    This week ,I find a job as a Vb programmer. I have never used VB. I met a question about " VBA Range."

    How can I replace " R1C1:R200C8" with Range, and Range need variant .
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,[TT_TIME.XLS]SHEET1!R1C1:R200C8,8,FALSE)"

    my code:

    set myrange=range(cells(1,1),cells(rowcount,8))

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,[TT_TIME.XLS]SHEET1!myrange,8,FALSE)

    It doesnot work

  2. #2
    Join Date
    Nov 2003
    Location
    Alameda, CA
    Posts
    1,737
    Quote Originally Posted by whygh
    Hi, This week ,I find a job as a Vb programmer. I have never used VB.
    well, today did start badly for me, but thank you for this line, never had so much fun in this forum.
    Marco

  3. #3
    Join Date
    Jun 2004
    Location
    Houston area
    Posts
    557
    HAHAHAHAHAHAHAHAHAHAHAHAHAHA!!!!!!!!! I laughed out loud!

    L
    A balanced diet is a cookie in each hand.

  4. #4
    Join Date
    Mar 2005
    Posts
    110
    Quote Originally Posted by Laurel
    HAHAHAHAHAHAHAHAHAHAHAHAHAHA!!!!!!!!! I laughed out loud!

    L

    Why did you laughas me? oh, I know, you guys donot know how to answer this question. so you just laugh. I should not seek help in here.

  5. #5
    Join Date
    Jun 2004
    Location
    Houston area
    Posts
    557
    NO NO NO, whygh! We just had a good laugh at your expense! We didn't mean to offend you!

    Here in the United States, jobs in IT/Programming are scarce and hard to get. It's VERY VERY difficult for someone with no VB programming experience to get a job as a VB programmer. THAT'S what we were laughing at!

    L

    P.S. I have no idea how to program with Excel but there are others in this forum that will be glad to help you.
    A balanced diet is a cookie in each hand.

  6. #6
    Join Date
    Jan 2004
    Location
    Alexandria, VA
    Posts
    392
    You definitely cannot use "myrange" inside the quotes.

    If you are simply trying to make the number of rows variable, you could try something like:
    Code:
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,[TT_TIME.XLS]SHEET1!R1C1:R" & cstr(rowcount) & "C8,8,FALSE)"
    Bob Rouse
    Dimension Data

  7. #7
    Join Date
    Jun 2004
    Location
    Houston area
    Posts
    557
    But could you use:

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,[TT_TIME.XLS]SHEET1!" & myrange & ",8,FALSE)"

    ??
    A balanced diet is a cookie in each hand.

  8. #8
    Join Date
    Jan 2004
    Location
    Alexandria, VA
    Posts
    392
    Quote Originally Posted by Laurel
    But could you use:

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,[TT_TIME.XLS]SHEET1!" & myrange & ",8,FALSE)"

    ??
    That depends on what the default property for the range is (not sure myself). It would have to be a text representation of the cell range.
    Bob Rouse
    Dimension Data

  9. #9
    Join Date
    Jun 2004
    Location
    Houston area
    Posts
    557
    Well, Myrange would have to be a string, would it not?

    Such as:

    Myrange="range(cells(1,1),cells(rowcount,8))"

    Again, I don't know how to "DO" Excel so I REALLY don't know what I'm talking about!

    BUT........to use the variable "Myrange" in a string, it would have to be a string as well. N'est pas?

    L
    A balanced diet is a cookie in each hand.

  10. #10
    Join Date
    Jan 2004
    Location
    Alexandria, VA
    Posts
    392
    I was assuming that "MyRange" was a range object in Excel, especially since the original example said "Set myrange = ..."
    Bob Rouse
    Dimension Data

  11. #11
    Join Date
    Jun 2004
    Location
    Houston area
    Posts
    557
    Me thinks thou art RIGHT........but to use it in a string it would have to be a string as well, would it not?

    Again, I don't know what I'm talking about BUT....

    "=VLOOKUP(RC1,[TT_TIME.XLS]SHEET1!myrange,8,FALSE)"

    looks like a string to me!
    A balanced diet is a cookie in each hand.

  12. #12
    Join Date
    Jan 2004
    Location
    Alexandria, VA
    Posts
    392
    Right...

    There was no reason to use the range object for what he (she?) was trying to do.
    Bob Rouse
    Dimension Data

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center