VBA Vlookup Range

[whygh]

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

[mstraf]

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

[Laurel]

HAHAHAHAHAHAHAHAHAHAHAHAHAHA!!!!!!!!! I laughed out loud!

L

[whygh]

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.

[Laurel]

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.

[brouse]

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:

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,[TT_TIME.XLS]SHEET1!R1C1:R" & cstr(rowcount) & "C8,8,FALSE)"

[Laurel]

But could you use:

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

??

[brouse]

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.

[Laurel]

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

[brouse]

I was assuming that "MyRange" was a range object in Excel, especially since the original example said "Set myrange = ..."

[Laurel]

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!

[brouse]

Right...

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