Formula array

  • Thread starter Thread starter miek
  • Start date Start date
M

miek

How do I enter in the following as a formula array?
{IF(MAX(COUNTIF(A3:A12,A3:A12))>1,""Dup's"",""No Dup's"")}
This does not work:
ActiveCell.Value = "{IF(MAX(COUNTIF(A3:A12,A3:A12))>1,""Dup's"",""No
Dup's"")}"
 
Hi,

Try this

ActiveCell.FormulaArray = "=IF(MAX(COUNTIF(A3:A12,A3:A12))>1,""Dup's"",""No
Dup 's"")"

Mike
 
This always returns a "False" value. I have the following:
on active worksheet
A1=1
A2=2
A3=a
A4=b
A5=1

Sub Macro1()
loc_var = ActiveCell.FormulaArray =
"=IF(MAX(COUNTIF(A1:A5,A1:A5))>1,""Dup's"",""No Dup 's"")"
ActiveCell.Value = loc_var
End Sub
 
Sub arrayformula()'Tested
'=IF(MAX(COUNTIF(A1:A5,A1:A5))>1,"Dup's","No Dup 's")

ActiveCell.FormulaArray = _
"=IF(MAX(COUNTIF(A1:A5,A1:A5))>1,""Dup's"",""No Dup 's"")"
End Sub
 
Thanks Don that did the trick.
I don’t understand why activecell.formulaArray can't be assigned to a var
though
Thanks again, much appreciated
 
Is it possible to have the parameters of the countif be varibles?
ie I have the following (does not reflect correct answer)

locvar = 5
ActiveCell.FormulaArray = _
"=IF(MAX(COUNTIF(A1:A & locvar, A1:A & locvar))>1,""Dup's"",""No Dup's"")"

Thanks again
 
When you interrupt your string you must do it this way. " & myvar & "

Sub arrayformula()
'=IF(MAX(COUNTIF(A1:A5,A1:A5))>1,"Dup's","No Dup 's")
'ActiveCell.FormulaArray = _
'"=IF(MAX(COUNTIF(A1:A5,A1:A5))>1,""Dup's"",""No Dup 's"")"

Dim myvar As Long
myvar = 5
ActiveCell.FormulaArray = _
"=IF(MAX(COUNTIF(A1:A" & myvar & ",A1:A" _
& myvar & "))>1,""Dup's"",""No Dup 's"")"
End Sub
 
Back
Top