• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to write EVALUATE with FormulaArray

Debraj

Debraj

Excel Ninja
Hi Excelian,


Can someone please help me to write any EVALUATE function.. with Ctrl + Shift + Enter..


i.e..

[pre]
Code: 
Sub EvaluateWithCSE()
Set SD = ThisWorkbook.Sheets("SalesData")
lr = Cells(1, 1).CurrentRegion.Rows.Count
lc = Cells(1, 1).CurrentRegion.Columns.Count
'----
'Want to write below loop in OneGo..
For i = 2 To lr
Cells(i, lc + 1) = Evaluate("COUNTIF($B$2:$B$" & lr & ",$B" & i & ")")
Next i
'Something Like below
'Range(Cells(i, lc + 1),cells(lr,lc+1)) = Evaluate("COUNTIF($B$2:$B$" & lr & ",$B$2:$B$" & lr & ")"")
'----
End Sub[/pre]

Applied Formula

[code]=COUNTIF($B$2:$B$36,B2)


Expected Formula

[C2:C36].FormulaArray = "=COUNTIF($B$2:$B$36,$B$2:$B$36)"[/code]


Thanks in advance..


Regards,

Deb
 
Hi Deb ,


I am confused by your question ; the EVALUATE VBA function actually evaluates an expression ; do you want to have the result of the evaluation , which will be values , or do you want formulae in the output cells ?


Secondly , do you want the array formula in the cells , taken individually , or do you want the output range to be considered as one array , and the formula entered in them ?


For example , suppose we have the following data in cells H2:H12 :

[pre]
Code: 
3
2
6
7
9
5
3
4
3
2
9
[b]CASE 1


Now , in the range I2:I12 , I can select the entire range , and enter the following formula , using CTRL SHIFT ENTER :


=COUNTIF($H$2:$H$12,$H$2:$H$12)


This enters the same formula in all the cells , but the values are correctly inserted depending on the counts. The range I2:I12 is treated as an array , and no individual cell in this range can be modifed or cleared.


[b]CASE 2


In the cell J2 , I can enter the following formula , as a normal formula :


=COUNTIF($H$2:$H$12,$H$2:$H$12)


and I can copy this down till J12 ; again , the formulae in all the cells will be identical , but the values will be correctly displayed , and they will be identical to those displayed in I2:I12. But the individual cells J2:J12 can be modified or cleared , since they are not part of an array.


CASE 3[/b]


But if I enter the formula as an array formula ( using CTRL SHIFT ENTER ) in J2 , and copy it down till J12 , I will get the same value repeated 11 times , which is obviously not desirable. In this case too , the individual cells J2:J12 can be modified or cleared , since they are not part of an array.


What exactly do you want ?


If you want Case 1[/b] , then the following statement does it :

[pre][code]Range("I2:I12").FormulaArray = "=COUNTIF($H$2:$H$" & lr & ",$H$2:$H$" & lr & ")"
where lr = 12


If you want Case 2[/b] , then the following statement does it :

Range("I2:I12").Formula = "=COUNTIF($H$2:$H$" & lr & ",$H$2:$H$" & lr & ")"
[/pre]
where lr = 12


In either case , if you want the actual values in the cells I2:I12 , then follow the above statement with this :

Range("I2:I12").Value = Range("I2:I12").Value[/code][/pre]
Narayan
 
Hi Narayan,


1st of all Sorry for late response.. You know.. "Friday Fever" & then "Weekend" Pressure in MNC's.. So missed to checked.. :(


Let come to the point..


From you above suggestion I would like to take..

[pre]
Code: 
Range("I2:I" & lr).FormulaArray = "=COUNTIF($H$2:$H$" & lr & ",$H$2:$H$" & lr & ")"
Range("I2:I12").Value = Range("I2:I12").Value

However I just want to learn.. Hoe to use EVALUATE function.. with Array Formula..

Above is little bit confusing.. as I am asking for MULTIPLE OUTPUT

Lets try One more example..

[pre][code]Column1	        Column2	Column3	Column4
10-Dec-11	RAJ	ROY	35
11-Dec-09	RAJ	XXX	18
12-Jan-11	XXX	XXX	21
02-Jun-11	XXX	ROY	28
27-Dec-11	XXX	XXX	29
07-Feb-12	XXX	ROY	26
27-Nov-09	RAJ	XXX	41
17-Apr-11	XXX	XXX	17
03-May-10	RAJ	XXX	46
21-Jan-12	XXX	XXX	24
09-Nov-09	XXX	ROY	13
09-Dec-11	RAJ	ROY	48
17-Dec-09	XXX	XXX	45
05-Feb-10	RAJ	XXX	28
24-Mar-11	RAJ	XXX	18
27-Jan-11	XXX	XXX	21
15-Jun-11	RAJ	ROY	18
05-Sep-11	XXX	XXX	40
17-Nov-10	XXX	XXX	10
26-Nov-11	XXX	XXX	38
20-Feb-12	XXX	XXX	46[/pre]

In F2, I am using Formula Array '{=MAX(IF(YEAR(A2:A22)=2010,IF(B2:B22="RAJ",IF(C2:C22="ROY",D2:D22,0),0),0))}`


I just want to Use the same as UDF..

[code]Function Deb(R, A, J)
Deb = Evaluate("MAX(IF(YEAR(A2:A22)=" & R & ",IF(B2:B22=""" & A & """, ,IF(C2:C22=""" & J & """,D2:D22,0),0),0))") + 1
'=MAX(IF(YEAR(A2:A22)=2010,IF(B2:B22="RAJ",IF(C2:C22="ROY",D2:D22,0),0),0))
End Function
[/pre]
Where I am missing something!!!


I tried both

=deb(2010,"RAJ","ROY")[/code] and

{=deb(2010,"RAJ","ROY")}[/code] but both are not working..


https://dl.dropboxusercontent.com/u/78831150/Excel/EVALUATE%20with%20CSE.xlsb


Regards,

Deb
 
Hi Deb ,


Try this :

[pre]
Code: 
Function Deb(R As Integer, A As String, J As String)
Deb = Evaluate("=MAX(IF(YEAR(A2:A22)=" & R & ",IF(B2:B22=" & """" & A & """" & ",IF(C2:C22=" & """" & J & """" & ",D2:D22))))")
End Function
[/pre]
The second and third parameters to the function may be left undeclared or declared as string , but within the formula , they need to be quoted to be evaluated as strings.


Narayan
 
Thanks Savior..


I know, I am missing something.. thanks for Guidance.. :)


So Can I assume, "EVALUATE" is just a Prgramatic Concat function.. It never gives an ARRAY OUTPUT..

Can you please re-download the file..


Run "NEXT LEVEL" for few times..

I am unable to decide.. what this SUBROUTINE is actually doing..


Regards,

Deb
 
Hi Deb ,


No , EVALUATE does its job correctly ; the problem is with COUNTIF ; try this statement , and see the result :


Range("E2:E" & lr) = Evaluate("=($A$2:$A$" & lr & ")*($D$2:$D$" & lr & ")")


Narayan
 
No 'EVALUATE' function is much more than that. See here:

http://www.ozgrid.com/forum/showthread.php?t=52372


I guess it is COUNTIF behavior which isn't giving you the results the way you desire. COUNTIF returns a value of type double and not a variant so therefore you are not getting Arrayed Results. Just add the following line to your code:


Code: 
Debug.Print VarType(Evaluate("=COUNTIF($B$2:$B$" & lr & ",$B$2:$B$" & lr & ")"))


It will give value as 5 which is double if you check VarType function.


When you enter the COUNTIF function in the full range as Array it returns evaluated result as double. So if you want to use COUNTIF function without looping through then Narayan's method is best approach.

[pre]Sub NextLevel()
Dim lr As Long: lr = 22
With Range("E2:E" & lr)
.FormulaArray = "=COUNTIF($B$2:$B$" & lr & ",$B$2:$B$" & lr & ")"
.Value = .Value
End With
End Sub
[/pre]

That said, it will never appear as working as you have used RANDBETWEEN which volatile and at step .value = .value sheet will calculate which will change values.
 
Thanks Shri & Narayan..

Atleast for showing in which way.. I need to go.. and what exactly I am missing.. :)


Re-Download the file..

* As you all knows RANDBETWEEN is just for testing purpose.. so leave that part..

* and after Value Paste.. I tried to convert COUNTIF to CVAR.. failed..

* Then I take favor from MMULT..

Code: 
Range("E2:E" & lr) = Evaluate("=MMULT(COUNTIF($B$2:$B$" & lr & ",$B$2:$B$" & lr & "),{1})")


and guess what.. I got Matrix Multiplication Knowledge of SHRIVALLABHA & NARAYAN..

Something like SUPERPOWER.. :)

Thanks buddies..


https://dl.dropboxusercontent.com/u/78831150/Excel/EVALUATE%20with%20CSE.xlsb


Regards,

Deb
 
Hi Deb ,


Thanks for sharing your discovery ; when ever COUNTIF is to be used for returning an array , MMULT and {1} can be used to get the result array. Wonderful.


Narayan
 
And something I'd forgotten. Should work the same as MMULT.


Code: 
Range("E2:E" & lr) = Evaluate("INDEX(COUNTIF($B$2:$B$" & lr & ",$B$2:$B$" & lr & "),0,0)")
 
@Debraj Roy (aka SirDB7)

Hi!

Welcome to the club. If I were you I'd consider an honor being introduced by shrivallabha.

Regards!

PS: I know Justerini & Brooks, they're Carlsberg's cousins, but DB... are their foreign relatives?
 
Hi Narayan & Shrivallabha,


Thanks for the appreciation and glad that you like it..

@ Shri.. thanks for the NickName.. :)


@ SirJB7.. yes!.. Just running for next 1KClub .. ;)

and yes you are right.. DB is JB's foreign relative..


Regards,

Deb
 
@Debraj Roy

Hi!

I didn't know it, so I'm afraid it seems as I should attend to next test match All Blacks vs. Los Pumas...

Regards!
 
You must log in or register to reply here.
Back
Top