Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Assign formula array in vba
Hello, I loop thru my datasheet where each row's columns A,B and C have to
lookup a unique match of columns A,B and C in another workbook called "Z" within a sheet named "Detail". For each match, I need to place the value from cell F in the other worksheet into cell H of my datasheet. I do this by using the following code within a loop . The error (UNABLE TO SET THE FORMULA ARRAY PROPERTY OF THE RANGE CLASS) happens if I try to substitute a value for the varable- valueLook(datatype Variant) such as valueLook = "AlphaBetaGamma" instead of using cell addresses. THIS WORKS BUT I CAN'T SUBSTITUTE A VALUE "AlphaBetaGamma" FOR VARIABLE valueLook valueLook = ActiveCell.Address & "&" & ActiveCell.Offset(0, 1).Address & _ "&" & ActiveCell.Offset(0, 2).Address I USE THIS VARIABLE IN THE FOLLOWING FORMULA Range(ActiveCell.Offset(0, 7).Address).FormulaArray = "=INDEX('[Z.xls]Detail'!$F$4:$F$600,MATCH(" & valueLook & ",'[Z.xls]Detail'!$A$4:$A$500 &'[Z.xls]Detail'!$B$4:$B$500&'[Z.xls]Detail'!$C$4:$C$500,0))" The basis of my question is how to assign manual values instead of cell addresses for my lookup in FormulaArrays using the INDEX/MATCH combination within VBA. valueLook = "abc" in the above code would generate the error Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assign Values to array | Excel Discussion (Misc queries) | |||
Can't assign to Array .GetRows in Excel 97 | Excel Programming | |||
How do I assign values to an array? | Excel Programming | |||
Possible to assign an array to a SeriesCollection | Excel Programming | |||
how to assign ranges on different sheets to an array | Excel Programming |