VBA IIf(myCell.Value = Array(myCell2(1).Value, myCell2(2).Value,myCell2(3).Value,myCell2(4).Value) ,

[EagleOne]

2003, 2007


Have a routine where I would like to ascertain if myCell,value = an array of myCell2 cells.

What I conceive the formula to be is:
VBA Message = IIf(myCell.Value = Array(myCell2(1).Value,
myCell2(2).Value,myCell2(3).Value,myCell2(4).Value) ,"OK","No Match)

Another way to phrase my question.

1) I have an array of myCell2 Formulas on Secondary W/S
2) Also I have an array of myCell Formulas Secondary W/S
3) TempFormula is a string representing the formula on the Master Worksheet
4) If myCell.formula matches any of the myCell2.Formula(s) then I want to:
5) Replace(TempFormula, myCell2.value,"")
6) How can I write an array(?) formula to have all of the array values
available for the replace test?

TIA EagleOne

 

[Rick Rothstein]

How is myCell2 Dim'med and how did you assign values to it?

 

[EagleOne]

Hi Rick,

MyCell, MyCell2, MyRange & Myrange2 are all Dim'd As Range

EagleOne