- I want to use the numeric content of a cell which has been generated by a formula as the FIND value to search a horizontal row of numbers. However it always tells me it cannot find the cell even if I COPY and Paste special (number) the contents of the formula holding cell into FIND. How do I get round this?
Yes I have selected Values in the options, and selected the row and ticked the box.
Even if I just paste the copy into a separate cell (say A1) and then put =A1 into the FIND box it wont work.
If I just put straight numbers into the FIND box it works OK on the row of numbers.
Any ideas? - Is there a way of sort ascending this row of numbers?
I’d like to put both of these into a macro.
Help please!
andy
Please upload sample file here.
Welcome @andyjames !
If someone told you “I want to go to the Black Swamp to pick berries. No one goes there, so there are probably a lot of berries” what would you say to him in response?
- No, don’t go there! No one goes there because it is dangerous there - you can drown. Besides, they don’t go there because there are no good berries.
- You have chosen your path, so go. But don’t expect me to accompany you - I already have a lot of pressing things to do.
- I have a lot of pressing things to do right now, but I will put these things aside and go with you to the Black Swamp. I won’t pick berries, I’ll stand by to throw you a rope when you start to sink. Perhaps we will both drown, but ordinary humanity compels me to take part in this adventure.
- Let me show you another place where you can pick a lot of good berries without risk.
- Buy berries at the supermarket - it’s fast, safe and not much more expensive than risking your life to pick them in the Black Swamp.
Or would you not stop at one of the options, but tried them all?
Okay, I’ll try it all too.
-
You have chosen the wrong tool to create your project. The macro recorder will not be able to do everything that you have in mind. Five thousand lines of code for fifty procedures that perform elementary operations, this is an insane waste! Moreover, more than half of these procedures do not do what they should do.
-
You can keep trying. Perhaps someone will even help you from time to time, telling you exactly how to record this or that action using a macro recorder so that the script works correctly. But do not count on it - this is your project, this is your headache.
-
These five thousand lines can be rewritten to take 120 or 150 lines and work. But for this you need to forget about the existence of a macro recorder and start writing code manually. Take one procedure at a time, understand the actions that it performs, and rewrite. Or throw it away if you realize that the procedure is not needed at all. For example,
sub startnames2: copy the contents of the range $O$5:$Q$54 to the clipboard, select cell $L$13 and… And what’s next? Why $L$13? And why is all this necessary at all? -
You will not believe, but everything, absolutely everything that you are trying “to program” now, has already been written and published many times. You just need to find the necessary code fragments and combine them into your project. Most of the work was done for you by Andrew Pitonyak - he collected working code samples from a wide variety of discussions for several long years, equipped them with explanations, and put together one book. Just download this file, search the table of contents for a section that makes sense, read the explanations, and use the ready-made, repeatedly tested code.
-
From the sample spreadsheet you provided, and from the code contained in that file, it is difficult to understand exactly what the project is supposed to do. It seems that we are talking about a list of participants who accumulate some game points during the week and change their position in the leaderboard at the end of the week. But for some reason, the results, game points are filled in manually. Why not automatically? You asked the question “how to paste the contents of the clipboard into the Find&eplace form”, and in the explanation you said that you are trying to find the top five in this way. Have you ever thought that you can find the best values, for example, using the LARGE() function? Or you did not know about the existence of functions in the Statistics section?
-
By the way, how is the top five determined if, for example, seven participants simultaneously scored the same maximum number of points?
3 Likes
Thank you for taking the time to write this fascinating answer to my questions.
I have misled you by sending a hacked up version of a previous app - most of the macros are now redundant.
Any chance You could give me a simple answer to “option 2”, which was “is there a simple way of sorting a simple row (not column) of numbers into ascending order?”
andy james
Have you seen this part of the Sort window?
Hi,
I had never gone to “options” tab ,where this feature is on my version, before, so thanks for that.
Recognizing your dislike of macros, do I really have to create a macro individually sorting each of 50 lines or is there a better way? Unless I can go into the macro code, identify the set of lines doing the sort and copy/paste them 49 times!
As far as I could determine it will not sort, in one hit, multiple lines in a block into individual line ascending order.
Is there some kind of FOR…NEXT loop kind of code option to avoid lots of code lines? (I’m into simple Basic but not really into LibreOffice code).
What I will look at is adding similar to =IF(O5>BS5,O5,BS5) to substitute the new score from the entry table into the lowest previous score ie in the first cell of the sorted row, if it is larger than the lowest score.
It is the nearest I can get to visualize automating the process for the user - ie enter the scores in the table and hit first a macro button to show and publish the winners on the day, and then a second button to show the accumulated best scores for each player.
Your help is appreciated
andy
By the way, what version are you currently using?
Perhaps you misunderstood me - I’m not against macros (they make life a lot easier and allow you to do interesting things), I’m against using a macro recorder. Sometimes, in a very small number of the simplest cases, a macro recorder can produce a perfectly working script. It will be verbose and hard to read, but it will work.
Oh, don’t worry about it! This is not at all as difficult as it might seem at first glance. You know about the existence of arrays and the FOR…NEXT loop - that’s enough to solve your problem. How long will the string to be sorted be? Three cells or more? I ask because the last table did not contain enough data to understand what is at stake.
Hi JohnSUN,
I attach a pic of my sort page to clarify my comment. Version in use is 7.3.2.2(x64).
I also attach rev2 of the app which hopefully explains the scope of the task.
re loops, can I really put in:
for i=5 to 60
BMi … …
next i
says he optimistically!
andy
sains results 2023-test1rev2.ods (51.8 KB)
