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.

 

[jswhip]

Here's the final desired formula:

{=IFERROR(IF(VLOOKUP($A2,Parameters!$A:$P,16,FALSE)="H",PERCENTILE(IF(NoDups!$A$2:$A$890=$A2,IF(NoDups!$L$2:$L$890="","",NoDups!$L$2:$L$890),""),1-VLOOKUP($A2,Parameters!$A:$P,3,FALSE)),PERCENTILE(IF(NoDups!$A$2:$A$890=$A3,IF(NoDups!$L$2:$L$890="","",NoDups!$L$2:$L$890),""),VLOOKUP($A3,Parameters!$A:$P,3,FALSE))),"")}

The logic is as follows:
1. Check to see if I need the top or bottom X percentile (i.e. - looking for "H"). The selection criteria and percent is designated by the "Parameters" tab.
2. The individual line items are on the "NoDups" tab. From here, summarize the data and calculate a variety of statistical measures.
3. Since null items can cause incorrect results (but only in conjunction with an if check in an array - Andrew Paulsen from this board provided the workaround), these need to be excluded from the calculation. e.g. - "IF(NoDups!$L$2:$L$890="","","
4. The "false" result from step 1 needs to be repeated, but for the bottom X percentile.
5. Return null if there is an error.

The VBA code I have is too long to accomplish, but here's the start (the false result of looking for H returning an "X" is just a fake calc as I was trying to debug):

Cells(2, FinalCol).FormulaArray = "=IFERROR(IF(VLOOKUP(RC1,Parameters!C1:C16,16,FALSE)=""H"",PERCENTILE(IF(NoDups!R2C1:R" & LastCellRow & "C1=RC1,IF(NoDups!R2C12:R" & LastCellRow & "C12="""","""",NoDups!R2C12:R" & LastCellRow & "C12),""""),1-VLOOKUP(RC1,Parameters!C1:C16,3,FALSE)),""X""),"""")"

I tried to to do the Replace method as noted in the Daily Dose of Excel website, but incurred odd results (as mentioned above, maybe as a result of combining different reference methodologies???). For example, the RC1 would end up as a string and not result in a cell reference.

Thanks.

 

[circledchicken]

Hi,

I haven't looked at your logic in detail, but to reduce the size of the formula, you can:

• Move some of the component parts to separate cells and just refer back to the cell with the result. For example, in cell Z2 (and copied down if you need) enter:
  =VLOOKUP($A2, Parameters!$A:$P, 16, FALSE) = "H"
  
  Then refer to the result in your main formula, like:
  =IFERROR(IF(Z2, PERCENTILE(...
  
  and you could do something similar with the other VLOOKUP parts.
  
  
• You could also use defined names to shorten your references. For example define the following as a concise and useful name:
  =IF(NoDups!$L$2:$L$890="","",NoDups!$L$2:$L$890)
  
  and then in your main formula replace that bit with the defined name. In the VBA version you can reference the defined name as part of the formula string (you don't need to extract the address as before).

Regarding the mixing of reference styles, you shouldn't need to do that. Just stick to the A1 style of your desired formula.

 

[jswhip]

Cool.

I like the idea of using named ranges to simplify the formula. I've created the named range (and can see it in Name Manager), but when I use it in array formula, it works as if it's a string.

ActiveWorkbook.Names.Add Name:="Parm", RefersTo:="Parameters!C1:C16"
Cells(2, FinalCol).FormulaArray = "=IFERROR(PERCENTILE(IF(NoDups!R2C1:R" & LastCellRow & "C1=RC1,NoDups!R2C12:R" & LastCellRow & "C6,""""),1-vlookup(RC1," & "Parm" & ",2,false)),"""")"

I've also tried the following which results in a null where the named range is:
Cells(2, FinalCol).FormulaArray = "=IFERROR(PERCENTILE(IF(NoDups!R2C1:R" & LastCellRow & "C1=RC1,NoDups!R2C12:R" & LastCellRow & "C6,""""),1-vlookup(RC1," & Parm & ",2,false)),"""")"

How do I get the named range to resolve to the contents of the named range?

 

[circledchicken]

You're missing an equals sign in your 'RefersTo' definition.
Also, make sure you include the dollar signs unless you want to create a relative reference (which I don't think you do in your case).

Here is simplified example that hopefully demonstrates how this works:

Sub example()


With ActiveWorkbook.Names
    .Add Name:="x", RefersTo:="[B]=[/B]Sheet1!$A$1"
    .Add Name:="y", RefersTo:="[B]=[/B]Sheet1!$A$2"
End With


With Sheet1
    .Range("x") = 1
    .Range("y") = 2
    .Range("A3").Formula = "=x+y"
End With


End Sub

Note, it might be easier to just create the defined names in Excel normally (without VBA).
Is there a specific reason you are constructing and entering your formula via VBA?

 

[jswhip]

Stupid mistake!

Adding the "=" sign fixed it. Also, the rationale for building this monstrosity is that I download an investment portfolio and need to calculate a variety of statistical measures. Right now it's a manual process, but if I can get this to work, it will save many hours of filling in formulas, formatting, etc.

For reference, here is the final result:

ActiveWorkbook.Names.Add Name:="Parm", RefersTo:="=Parameters!$A:$P"
ActiveWorkbook.Names.Add Name:="ND", RefersTo:="=NoDups!R2C1:R" & LastCellRow & "C1"

' WAC
FinalCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1
Cells(1, FinalCol).FormulaR1C1 = Cells(1, 5)
Cells(2, FinalCol).FormulaArray = "=IFERROR(PERCENTILE(IF(ND=RC1,NoDups!R2C12:R" & LastCellRow & "C6,""""),1-vlookup(RC1,Parm,2,false)),"""")"

' AOLS
FinalCol = FinalCol + 1
Cells(1, FinalCol).FormulaR1C1 = Cells(1, 6)
Cells(2, FinalCol).FormulaArray = "=IFERROR(IF(VLOOKUP(RC1,Parm,16,FALSE)=""H"",PERCENTILE(IF(ND=RC1,IF(NoDups!R2C12:R" & LastCellRow & "C12="""","""",NoDups!R2C12:R" & LastCellRow & "C12),""""),1-VLOOKUP(RC1,Parm,3,FALSE)),PERCENTILE(IF(ND=RC1,IF(NoDups!R2C12:R" & LastCellRow & "C12="""","""",NoDups!R2C12:R" & LastCellRow & "C12),""""),1-VLOOKUP(RC1,Parm,3,FALSE))),"""")"


MUCHO THANKS FOR WORKING THROUGH THIS!!!

 

[circledchicken]

You're welcome - thanks for the feedback.