MACRO to search for a number and then delete row where that number appears

English
#1

I’m trying to create a simple tool in LibreCalc to help learn a language. I have used the rand.nv function to generate a random number within a range, followed by VLOOKUP to find that value in another sheet. The word in English is then shown, and I have a command button (macro) to show the translation. A second command button (macro) clears the translation and shows a new word.

The problem I have here is that because the selection is random, some words are coming up multiple times, and others not at all. It would be nice to use a macro to delete that row so the word will not be shown again (until I manually add a list of new words) but i’m struggling with that.

Can anyone help?

David

This is the relevant info relating to my document.

In sheet ‘vocabulary’ I have a list of numbers in ‘column A’ generated using the following formula
=ROW()-1

In sheet ‘working’ I have used the following formula to generate a random number in cell A1
=RANDBETWEEN.NV(1,(COUNTA($VOCABULARY.A:$VOCABULARY.A)))

Need a macro to search for $working.A1 in $vocabulary.A, and delete that whole row.

#2

Nobody needs a macro to do that.

#3

Apart from me.
Hence why I asked the question…

(if you don’t have anything constructive to say, don’t waste your time replying)

#4

What do you intend to tell a newcomer by that?

2 Likes
#5

That it is fairly easy to do that with built-in commands (keystrokes) and:

THE VOLUNTEERS ON THIS SITE ARE NO HUMAN MACRO RECORDERS

#6

Using built in commands would involve going to $vocabulary, finding the word, and then deleting the row. Easy for sure, but that would defeat the purpose of this document (which is for learning/memory). It would be like playing poker with all the cards face up.

#7

True. But they also shouldn’t tell just “Do it without any macro!” and then not give any hint concerning the considarable means.
A next-to professional user in the know can simply step out from posting to a question.
Somebody only losely in contact with somebody else (mis)using Excel may be lead to the idea of needing a macro easily.
And there are reasons to do something of the kind with macros - also when using Calc.
That “somebody” may also be somebody knowing that existing solutions to the task are supposed to be spread over the world by the thousands.
Hailing the efficiency of the professionals beyond reason lead us to where we are. Glad with it??
Knocking new users on the head can also have the main effect of making them leave opensource or community software behind. Zero percent LibO isn’t what I want to support.
“Somebody”, a young one in specific can get help to better understand what he (f/m) is trying. In this case she (m/f) should be told that the deletion of rows in such a case is a bad idea anyway. But everybody need to train muscles before he can create wonderful databases for everything.

#8

If you already have this all working and developed it yourself you should

  1. Show what you have by attaching a shortened file.
  2. Ask if more experienced users have suggestions insofar.
  3. Reconsider the idea of deleting any rows. Why?

I just spent some time to consider the situation and would suggest you don’t delete anything, but count for every word how often it already was translated correctly in your quizz sheet. Then you can sort the words by ascending frequency, and pick randomly from the list preferring words not yet often solved.

The sorting you need isn’t actually difficult. And if you want to continue with programming task for Calc, you need anyway to study the basics of the API and how to handle cell ranges.
For today I attach a just started document showing a few details about how I see the task. No row deletion included though.
vocabularyQuizz.ods (30.5 KB)

#9

Then you need a mixed order of the numbers (or the words) but NOT random numbers.
Write a macro what can mix (randomly) the numbers or the words in a cell range and then show the words with a normal cycle from 1-st to n-th based on the row number.
.
https://forum.openoffice.org/en/forum/viewtopic.php?t=58153
.
https://forum.openoffice.org/en/forum/viewtopic.php?t=50301

#10

Thanks for the help.
I’ve attached my file. It’s not particularly elegant, but it works.

I thought that deleting the rows of already seen words would be easiest, and would have the advantage of not showing those words again. I envisage the vocabulary list as containing around 100 - 150 words, so the list will constantly change depending on the topic(s) I’m currently studying. In other words, I’ll be deleting and adding new words on a regular basis. But anything to limit the number of times a word is shown would be advantageous.

I have some other ideas, but those would probably be best done with a database.

#11

MY VOCABULARY.ods (15.9 KB)

#12

I would still suggest to reconsider (and abandon) the idea of deleting rows from an original set of data.
To create a new module for every recorded macro, and then to name all the macros “Macro1” also isn’t state of the art.
Based on what I saw I can’t lead you to a solution I would accept as recommendable.
Thus I simply wrote a little macro linked to a button, to actually delete “accomplished” rows against my own advice. You find it in the new attachment. No further support by me concerning this topic.
VOCABULARY86406Re.ods (16.3 KB)

#13

Hello,
i prepared a file, working without any macro, currently for 100 words.
Just click on “jump to translation”. If you need to hold the ctrl-key for following that hyperlink, you can adjust that in menue Tools/Options…/Security/Options…
Enter a lower case x at the targetcell to mark that word as “done”, it will not appear again, until you delete the x in column D.
Then click on new Text for the next word.
Translation.ods (17.1 KB)

#14

Here we go again: Apache OpenOffice Community Forum - [Embedded HSQL] Simple Vocabulary Trainer - (View topic)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.