Good afternoon Experts
I have some experience in formulas but am struggling with the Evaluate Function.
I have this code that fills a column with a formula.Is there a way to make use of Evaluate Function and pass the values directly to the column.
Something like this perhaps?
Please Login or Register to view this content.
With Range("D2:D" & lastrow)
.Value = Evaluate("=IF(COUNTIF(A2:C" & lastrow & ",""X"")> 0,""T"",""F"")")
End With
But this does not work
Last edited by NeedForEXcell; 03-21-2021 at 10:38 AM.
If the sub you have already works and you want the calculated values rather than the formulas in the cells then you could add one line to convert them to values after calculation.
Does that do what you need?Please Login or Register to view this content.
BSB
Hallo BSB
Thank you for your quick response. I was hoping to bypass the formulation and get the values directly to the column without the .Formula & .value conversion. I want the Formula answer to be Evaluated and directly passed as values.
How aboutPlease Login or Register to view this content.
You could calculate the results in memory and stamp those to the cells, but this isn't going to be any quicker than the code above.
If anything it would be slower as you'll end up looping through each row in the range.
BSB
Oh wow. Yes, Fluff13, that changed version works.Thank you.
Just out of curiosity, is there a way to evaluate the countif row(range) of x > 0
Something like this perhaps?
Please Login or Register to view this content.
Last edited by NeedForEXcell; 03-21-2021 at 11:16 AM.
AFAIK you cannot use countif(s) with evaluate.
....Deleted...
Tried SUMPRODUCT to no avail...
Last edited by Sintek; 03-21-2021 at 11:57 AM.
Good Luck...
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
Also....Add a comment if you like!!!!
And remember...Mark Thread as Solved...
Excel Forum Rocks!!!
Yes I think you will, I know of no other way of doing that with evaluate.
That is odd. I just had a post disappear. Ghost in machine.
Check Columns A to K
Okay. Thank you Fluff13. Will go and add the 10 strings.
I am guessing post 2 Solution is not looking so bad then ha ha.Please Login or Register to view this content.
Last edited by NeedForEXcell; 03-21-2021 at 12:14 PM.
One last question if I may. In the attached green highlighted. I have placed COUNTIF Formula & Corresponding SUMPRODUCT Formula to produce the same result.
As one is not able to make use of COUNTIF (As per Fluff13) or SUMPRODUCT (As per sintek's attempt) with the Evaluate function, How would one populate these results with the Evaluate Value Function in VBA.
This is giving me sleepless nights ha ha.
Last edited by NeedForEXcell; 03-22-2021 at 04:44 AM.
I would just put the formula in & then convert to values.
@ NeedForEXcell...
I make use of post 2 option most times...
I guess even if what you were asking was doable, it would not make much change in efficiency...
And if one of our most seasoned coders cannot do it, then nobody can...
"one of our most seasoned coders"
Is that you sintek?
Okay, thank you for your input Gents(Not sure if you are - or ladies perhaps?)
Anyway, will mark as solved and chalk it up as a "not possible"
No, not at all...I am still learning new ways to do things everyday...That is why I love this forum so much...Was referring to Fluff13Is that you sintek?
Like sintek I also learn new ways to do things from other members here.
Often times the simplest way is the best, why re-invent the wheel?
Originally Posted by NeedForEXcell
Please Login or Register to view this content.
Oh my gosh! That works jindon
Have no idea what it does. Straight over my head.
I was looking into array slicing but could not understand it well.
Evaluate("if(countif(offset(" & x & ",row(1:" & .Rows.Count & ")-1,,1,2),""X""),""T"",""F"")")
The red part is that ref B2:C5
What about the blue part if you don't mind me asking. This is fantastic.
And look at that...once again...jindon to the rescue...and so we learn...All in a days workLike sintek I also learn new ways to do things from other members here.
Well that solves your initial requirement...Not sure about your duplicate count in 11...perhaps jindon has a fix for that too!
OFFSET(range, rows, columns, [height], [width] )
1 is for height (each row), 2 is for width (b:c)
HTH
| Yes --- Thank You |
Simply
Please Login or Register to view this content.
I must be doing something wrong. That produces values of 3 all down.Simply
Please Login or Register to view this content.
Evaluate("countif(" & .Address & "," & .Address & ")")
The red part is ref to A10:A17
Formula is ref to each individual cell in range
=COUNTIF(A$10:A$17,A10)
Last edited by NeedForEXcell; 03-22-2021 at 11:11 AM.
Try the attached.
It populates all with 2's
Untitled.png
You won't get the results you want without the Offset that jindon used originally.
Everyone who confuses correlation and causation ends up dead.
Perhaps version difference
try
Please Login or Register to view this content.
This produces errors
Please Login or Register to view this content.
Ignore me - I hadn't seen that what you wanted in the green section is very different from your original question.
Yes. jindon. that works. I am also using 2013 though.
May I ask why the 2 commas. Is that same as 1,1 row & col
That was
OFFSET(range, rows, columns, [height], [width] )
Number of row(s), column(s) to be offset from the base range,
Default value = 0
So, offsetting by Row(1:" & .Rows.Count & ")-1 means Row(0:.Rows.Count -1)
BTW, I'm testing on O365...
Understood. I honestly did not think this could be done. Now to measure the efficiency of Formula, Evaluate & Array loops.
I am certain I'll be back with another post soon ha ha. Thank you all for your interest and a big THANK you to you jindon.
365. Yes, guess that is version compatibility
Ahhh, comma at the end of Index function...
These are row/column reference within the range/array.
When these are 0, default value, it returns full value.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks