PJ_in_FL wrote: ↑14 Feb 2024, 04:28
It seemed the simple equation of "=CONCAT("= ",A2," + ",B2," + ",C2)" (without the quotes) gets the result of "= 10 + 11 + 20", ....
..At first I thought you wanted to also include the result in the column, and the only ways I could see to do that were to use VBA:
1) Create a custom format for each cell ..
....
2) Create a custom comment for each cell,
Hi PJ
It’s nice to get the **chance to extend the discussion a while and add usefully to the subject after the original poster is away happy with a solution, JIMHO, making the thread part of a more a useful rich data base.
Also, personally, I think it helps learning and understanding when you see things done in different ways.
(**In most other forum and similar places the Thread would have been closed, deleted, and us chastised or worse.
I don’t have the
CONCAT( , or might have on a Excel 2016 I have not got around to using much yet. I probably would not use it for that reason of not being backward compatible. Just a personal preference, that’s all. I like a stable Office up to about 2013, after that I like to avoid it for a lot of reasons, too many to mention, including the disadvantages of non backward compatibility.
_._
Those custom format and commenting were interesting offerings from you. I am not so familiar with custom formatting and comments.
I messed up initially with the first one, when I looked at them. I foolishly ran the macro first and then put a formula in D1, and copied it down. Of course that messes up as then you copy the custom formatting as well, - and that is bad, because you need , just as you said …
a custom format for each cell, ….
Any variation of this modification, for example in your first macro would get over that problem, ( if you had not already put the formulas in, in which case you need no modification)
Code: Select all
' NOTE: working formula must be in cell before running this macro
For Each c In Selection
Let c = "=" & CStr(c.Offset(0, -3).Value) & "+" & CStr(c.Offset(0, -2).Value) & "+" & CStr(c.Offset(0, -1).Value)
I don’t doubt you know that, I’m am just adding it for the future benefit of anyone passing that may be as slow as me at getting the point, - unlikely perhaps, but theoretically possible, Lol.
In other words the point I am making is that your first macro assumes the formulas are already there, before it is run,
or
that you put them in each cell in a way other than dragging down. That could be done as I showed above at each loop,
or
, in one go like this for example,
Let Selection = "=SUM(A1:C1)"
. which was one of
snb’s earlier first offerings
_.__
…PJ_in_FL wrote: ↑14 Feb 2024, 04:28
my methods are not as concise as SNB, but I haven't a clue how to decode that bit of programming anyway. Reminds me of one liner APL code and it's esoteric symbols
…
….well I am glad you said that and not me, Lol, :) ,
… esoteric, obfuscated and something else that is pretty true I think but its not liked if I mention it.
They are not usually so difficult to decipher
,.. .well, as long a you know the answer, and are very familiar with his "way" of doing it. Sometimes I work through and decipherer them, occasionally it's worth it.
Regarding
these couple of offerings, I did take a look at the first one
here , (
and here ) ,
, and the second one
here
They were more of less than I expected this time around, and so I went off instead in a digressive tangent into the much more detailed very fundamental looking at the more basic coding that they are.
The TLDR is
The first one is this sort of thing
Range("D1:D5") = "=A1+B1+C1" , which is putting the formula in
, the second is this sort of idea
Range("D1:D5") = Evaluate("A1:A5 + B1:B5 + C1:C5") , which is putting the result value in
I would say the originals are a bit too confusing to be helpful, and I would be a bit wary if you don’t already know of the posting "style", but that is just my opinion , whatever turns anyone on, - it takes all sorts to make a less boring world, and we need that to help the AI and Chat bots taking over, Lol!
_.________
I had another think about it since I finally realised exactly what was wanted. This is VBA again, - I realise the OP did not want that and he is long since fully happy, but I thought it might add another useful alternative for the greater Thread discusion
My 2 Cents coding would be something like
this . That is again just a personal preference, as I like to have a lot of extra stuff there, but not necessarily see it all the time. The idea to my 2 cents coding is similar to that of me liking a lot of those
'comments over to the right in a code window, where you would only see most of them if you used the horizontal scroll bar to see them: I don’t necessarily want to see them all the time, as they would annoy me as much as they do most people, but I like a lot of info there in case I do need it later for future reference.
My 2 cents coding constructs a string with everything in it, but the extra info is deliberately separated from the main info by a large space and I would deliberately keep the column only narrow enough to see the main info. I do quite like the comment idea of yours as well, although it is approaching the frilly feature side of things I am less keen on. I will have to check that out though and see for example what basic formatting it has in the text.
I don’t like personally custom formatting much, but it is nice to see an example like you did, as it’s the format required that confuses me, so having a few examples is useful.
_.___
PJ_in_FL wrote: ↑14 Feb 2024, 04:28
..... betting a deceased equine …I was BEATING ..... I swore afterwards they were deceased.
In the extra text that I mentioned that I put in for later reference, I sometimes have some off topic stuff that can help later as a welcome diversion, Perhaps for example, a video link maybe to a favourite song, or even one of those nature videos. Equine can come up there, but usually live ones, - I have heard of some weird Bestiality and necrophilia, but I never took any interest, - I figured that might be just beating a dead horse…..
Alan