Using median if in VBA. Excel. 3 conditions stop running?
 

Hi,

I am new to the forum, but I have been tackling this problem all day but it's not going any where.....

I am using a median combined with if in VBA using formula array.

code:

I find that if you only use 2 conditions, it runs, but not with 3.......

However, in the cell above, I am using exactly the same format with 4 conditions, and it is working. The only difference is that the other cell gets its value from worksheet that is in the same workbook, whereas THIS one gets range from another workbook, but that is not the problem here?

Is anyone able to assist?

If I got rid of one condition, it works, but not with 2 more....Does this have to do with the size of the array?


Thank you

Using median if in VBA. Excel. 3 conditions stop running?
 

Could be?? Try using a defined name for your range
var1='[New CCM TIA SPH Macro test 51 .xlsm]SPH Clinic Intake'!C18
then test by =var1 in the worksheet
then test by
if var1="ed"

On Jan 17, 6:38*pm, Lifeseeker
wrote:
Hi,

I am new to the forum, but I have been tackling this problem all day but
it's not going any where.....

I am using a median combined with if in VBA using formula array.

code:

Code:
--------------------

* Set wbPath = Workbooks.Open("H:\All Projects\New CCM TIA SPH Macro test 51 .xlsm", Password:="test")

* ThisWorkbook.Activate
* Range("F4").FormulaArray = _
* "=MEDIAN(if(('[New CCM TIA SPH Macro test 51 .xlsm]SPH Clinic Intake'!C18 = ""ED"")*('[New CCM TIA SPH Macro test 51 .xlsm]SPH Clinic Intake'!C15 ='New CCM Reporting Clinic-Centre'!R4C2)*('[New CCM TIA SPH Macro test 51 .xlsm]SPH Clinic Intake'!C24 =""Yes""),'[New CCM TIA SPH Macro test 51 .xlsm]SPH Clinic Intake'!C19))"

--------------------

I find that if you only use 2 conditions, it runs, but not with
3.......

However, in the cell above, I am using exactly the same format with 4
conditions, and it is working. The only difference is that the other
cell gets its value from worksheet that is in the same workbook, whereas
THIS one gets range from another workbook, but that is not the problem
here?

Is anyone able to assist?

If I got rid of one condition, it works, but not with 2 more....Does
this have to do with the size of the array?

Thank you

--
Lifeseeker