I'm trying to assign an Index/Match formula to a variable in VBA.
This array formula works when in cells in a worksheet:
=INDEX('WORKSHEET FROM'!X$7:X$219,MATCH(1,--(B6=''WORKSHEET FROM'!F$7:F$219)*--('WORKSHEET FROM'!X$7:X$219<>""),0),1)
But I need to get the result into a variable in VBA so I can assign the value into a range of cells rather than the formula via loop.
I've tried this:
Code
Dim wsFrm As Worksheet
Dim wsTo As Worksheet
Dim cntRwImpCom as Long
Dim Cntr as Long
Dim srchRes as String
srchRes = Evaluate(Application.WorksheetFunction.Index(wsFrm.Range("X7:X" & cntRwImpCom), Application.WorksheetFunction.Match _
(1, --(wsTo.Range("B" & Cntr) = wsFrm.Range("F7:F" & cntRwImpCom) * --(wsFrm.Range("X7:X" & cntRwImpCom) <> "")), 0), 1))
This always results in srchRes = ""
wsFrm = 'WORKSHEET NAME'
wsTo = 'WORKSHEET TO' (Range.("B6") is in WORKSHEET TO)
cntRwImpCom = Number of Rows in WORKSHEET FROM
Cntr = Number of Rows in WORKSHEET TO for the loop and the row # in WORKSHEET TO
Display More
Many thanks