I am trying to create a range where it can be used in a vlookup formula
through VBA.
What I have so far:

Dim irange as range
Set irange = activesheet.range("A1:B10") 'range is in mybook.xls,
sheet1

the following formula will be populated in a separate workbook via
VBA:
I've tried:
range("D4").formula="=vlookup(rc3" & irange & "2,false)"
range("D4").formula="=vlookup(rc3,'[mybook.xls]sheet1'!" & irange &
",false)"
range("D4").formula="=vlookup(rc3,'[mybook.xls]" & irange & "2,false)"

For all the formulas above, I get a run-time 13 error, type mismatch.


Any help will be very much appreciated...
Thanks in advance....


--
kwiklearner
------------------------------------------------------------------------
kwiklearner's Profile: http://www.excelforum.com/member.php...o&userid=31909
View this thread: http://www.excelforum.com/showthread...hreadid=562910

The formula property is looking for a string. You should dim your irange as
a string and just make it equal to "A1:B10".
So basically you should have:
"=vlookup(rc3," & irange & ",2,false"
Also, I'm not sure what "rc3" is. Is that a named ranged in your sheet? If
that's not a valid range name, you'll need to edit that too.

-Simon

"kwiklearner" wrote:


I am trying to create a range where it can be used in a vlookup formula
through VBA.
What I have so far:

Dim irange as range
Set irange = activesheet.range("A1:B10") 'range is in mybook.xls,
sheet1

the following formula will be populated in a separate workbook via
VBA:
I've tried:
range("D4").formula="=vlookup(rc3" & irange & "2,false)"
range("D4").formula="=vlookup(rc3,'[mybook.xls]sheet1'!" & irange &
",false)"
range("D4").formula="=vlookup(rc3,'[mybook.xls]" & irange & "2,false)"

For all the formulas above, I get a run-time 13 error, type mismatch.


Any help will be very much appreciated...
Thanks in advance....


--
kwiklearner
------------------------------------------------------------------------
kwiklearner's Profile: http://www.excelforum.com/member.php...o&userid=31909
View this thread: http://www.excelforum.com/showthread...hreadid=562910

Since you're using .formula, you don't want to use R1C1 reference style.

You could either change to .formular1c1 (and fix the formula) or change the
formula to use A1 reference style (and fix the formula).

Range("D4").Formula = "=vlookup(rc3" & iRange & "2,false)"
could become
Range("D4").Formula _
= "=vlookup(C4," & iRange.Address(external:=True) & ",2,false)"
or
Range("d4").FormulaR1C1 _
= "=vlookup(rc3," & iRange.Address(ReferenceStyle:=xlR1C1, external:=True) _
& ",2,false)"


As aside, it's probably better to copy directly from your code and paste into
your message. It may prevent a typo and save you time getting a response--not
saying that your code had typos this time.


kwiklearner wrote:

I am trying to create a range where it can be used in a vlookup formula
through VBA.
What I have so far:

Dim irange as range
Set irange = activesheet.range("A1:B10") 'range is in mybook.xls,
sheet1

the following formula will be populated in a separate workbook via
VBA:
I've tried:
range("D4").formula="=vlookup(rc3" & irange & "2,false)"
range("D4").formula="=vlookup(rc3,'[mybook.xls]sheet1'!" & irange &
",false)"
range("D4").formula="=vlookup(rc3,'[mybook.xls]" & irange & "2,false)"

For all the formulas above, I get a run-time 13 error, type mismatch.

Any help will be very much appreciated...
Thanks in advance....

--
kwiklearner
------------------------------------------------------------------------
kwiklearner's Profile: http://www.excelforum.com/member.php...o&userid=31909
View this thread: http://www.excelforum.com/showthread...hreadid=562910

--

Dave Peterson