Range Not Working With Vlookup (has my Excel install been corrupted)???

[jswhip]

I am using a range in a vlookup formula. The problem is that when excel interprets the formula, the range name acts as if it's a string. Here's the code:

Dim pdq As Range
Set pdq = ActiveWorkbook.Sheets("Parameters").Range("$A:$P")

Cells(2, FinalCol).FormulaR1C1 = "=vlookup(RC1,pdq,16,false)"

The problem is that the formula doesn't convert RC1 to the right cell reference, nor does Excel convert "pdq" to the range. If I look at the cell formula, it looks exactly as what is between the quotes, but result is "#NAME?".

Any clue as to what is happening? Did I inadvertently trigger a setting? I'm using Excel 2007 with Windows 7.

 

[Smitty]

You need to separate pdq with quotes, otherwise VBA can only see it as a string:

Cells(2, FinalCol).FormulaR1C1 = "=vlookup(RC1," & pdq & ",16,false)"

HTH,

 

[jswhip]

Thanks Smitty.

There's something amiss - in order for Excel to resolve the RC1 reference, I need to put R[0]C1 in the formula.

Also, tried the solution you offered, and I get a type mismatch. So rather than using a named range, I used the explicit column references and it worked. I.E. - Cells(2, FinalCol).FormulaR1C1 = "=vlookup(RC1,parameters!C1:C16,16,false)" This also fixed the problem with Excel not recognizing RC1.

Did I incorrectly define the name?

 

[circledchicken]

Hi,

Since your pdq is defined as a range object (not a string), you need to use the Address property.

So Smitty's version, but something like this:

Cells(2, FinalCol).FormulaR1C1 = _
"=VLOOKUP(RC1," & pdq.Address(0, 0, xlR1C1) & ", 16, FALSE)"

Adjusting the Address parameters as required for absolute referencing etc.

Alternatively, define pdq as a string assigning it the Address of the required range, and use it in the formula as per Smitty's version.

 

[jswhip]

Thanks circledchicken.

Is there a better way of accomplishing this?

A little background - this is the first step in trying to reduce the number of characters in an array formula due to the 255 character limit (thus the use of ranges). I have the formula that I want to use, but rather than make wholesale changes, I thought it would be wise to use baby steps.

If I go the route you suggested, I'm back at square one with trying to reduce the number of characters.

 

[circledchicken]

There's a workaround to the limit described here that you might want to try:
Daily Dose of Excel » Blog Archive » Entering Long Array Formulas in VBA

However, a better solution overall might be just to reduce the size of your formula. Break it down into smaller parts, maybe using helper cell(s) / column(s) if necessary.

 

[jswhip]

I was looking at that very site earlier today. I get the gist of what they are trying to accomplish, but my formula is incredibly complex.

I also ran into the same trouble mentioned about where the replace option wouldn't work - it would either leave the string in the formula, or put it in quotes - it wouldn't do the actual replacing.

In experimenting further, I think the problem is somewhat related to using R1C1 references with A1. Can I set a range using R1C1 references?

 

[Smitty]

Since your pdq is defined as a range object (not a string), you need to use the Address property.

Sorry, should have pointed that out, but I've been on a conference call.

 

[circledchicken]

I don't think its a good idea in general to have a single (very complex) formula.
Is there no way you can break it down into smaller parts?

Can you post the formula, describing as well what it is trying to accomplish?

 

[jswhip]

I don't think its a good idea in general to have a single (very complex) formula.
Is there no way you can break it down into smaller parts?

Can you post the formula, describing as well what it is trying to accomplish?

I will post the final formula tomorrow along with an explanation of what I'm trying to accomplish. One hundred percent agree on keeping things simple, but due to the dynamic nature of the data and use of statistical calcs, the formula gets real ugly.