AOB
Well-known Member
- Joined
- Dec 15, 2010
- Messages
- 660
- Office Version
- 365
- 2016
- 2013
- Platform
- Windows
Hi guys,
So I've managed (with much valued help from steve the fish and others!!) to construct an INDEX / MATCH formula which works in a sheet (previous thread here...)
However, I want to evaluate the formula in VBA (partly because I need to use variables as the various criteria and partly because I need to be able to adequately handle the output where the formula returns nothing)
This is the formula as it looks in the sheet (this works)
This is my attempt at the VBA-constructed formula :
However, when I try to evaluate this within VBA, I get a ZLS :
But if I copy and paste strFormula into the sheet, it calculates fine?
Any suggestions?
Thanks!
AOB
So I've managed (with much valued help from steve the fish and others!!) to construct an INDEX / MATCH formula which works in a sheet (previous thread here...)
However, I want to evaluate the formula in VBA (partly because I need to use variables as the various criteria and partly because I need to be able to adequately handle the output where the formula returns nothing)
This is the formula as it looks in the sheet (this works)
Code:
{=IFERROR(INDEX(rngLookup,
MATCH(1,
(OFFSET(rngLookup,0,0,,1)="ABCD")*
(ISNUMBER(SEARCH("Customer",OFFSET(rngLookup,0,1,,1))))*
(OFFSET(rngLookup,0,2,,1)="EUR")*
(OFFSET(rngLookup,0,4,,1)="D")
,0)
,6),"")}
This is my attempt at the VBA-constructed formula :
Code:
strFormula = "IFERROR(INDEX(rngLookup," & _
"MATCH(1," & _
"(OFFSET(rngLookup,0,0,,1)=" & Chr(34) & strCriteria1 & Chr(34) & ")*" & _
"(ISNUMBER(SEARCH(" & Chr(34) & strCriteria2 & Chr(34) & ",OFFSET(rngLookup,0,1,,1))))*" & _
"(OFFSET(rngLookup,0,2,,1)=" & Chr(34) & strCriteria3 & Chr(34) & ")*" & _
"(OFFSET(rngLookup,0,4,,1)=" & Chr(34) & strCriteria4 & Chr(34) & "),0)," & lngReturnCol & ")," & Chr(34) & Chr(34) & ")"
However, when I try to evaluate this within VBA, I get a ZLS :
Code:
varTemp = Evaluate(strFormula)
But if I copy and paste strFormula into the sheet, it calculates fine?
Any suggestions?
Thanks!
AOB