Macro to add annotation like as VLOOKUP

Hello . . .

I enter a number in a table and would like to have the description in the comment automatically (like via VLOOKUP).
If it is easier, the value of the right neighboring cell can be inserted (which is then hidden).

This can be solved with a macro. . . I just don’t know how.

What range do you want to get a description from? Is this the same range for all cells in the spreadsheet? How do you enter a number? From the keyboard or choose from the drop-down list?

What is wrong with using VLOOKUP() in your situation?

How do you link the number to the comment? Are they in a separate table? If so, this is a typical use case for a lookup function.

Do you need to have empty comment cells that fill when the number turns up? Use conditionals, e.g. =IFERROR(VLOOKUP(...);"") to return an empty string when the lookup will fail (because of a missing key value).

What range do you want to get a description from? Is this the same range for all cells in the spreadsheet?

Yes. (Or better from the spreadsheet with accounting number and accounting description; G / L account number vs.
G / L account description; in german: Sachkontonummer vs. Sachkontobeschreibung)

How do you enter a number? From the keyboard or choose from the drop-down list?

From the Keyboard (accounting number vs. accounting description)

What is wrong with using VLOOKUP() in your situation?

The VLOOKUP result is too long

Do you need empty comment cells that are filled when the number is displayed?

yes (I know IFERROR) I think, VLOOKUP can not to bee use to fill annotation

In general, the task is not very difficult. For each cell that has just changed, try to find the necessary texts in the reference table. If the cell already had a comment (oCell.getAnnotation().getString()<>""), then replace its text (...setString()). If not, take comments on the whole sheet (oSheet..getAnnotations()) and add a comment for the current cell (...insertNew(oCell.getCellAddress(),Text)). It takes time to prepare a working demonstration.

Please check if this solution suits you:

oCell.getSpreadsheet.getAnnotations().InsertNew(oCell.getCellAddress(), getCommentText(oCell.getString()))

Demo - InsertComment.ods

Instructions Step-By-Step on YouTube - https://youtu.be/jDbIOM6fIDM

Your example after editing - YourExample.ods

CopyMacro.gif

That looks good.
I want the macro work to range E3:J6. In this area all cells with number or not and if a number then add a comment.
www.tapachgaertner.de/tmp/Example.ods

Come back here after about an hour, I will add instructions to my answer

That works well, very well!
Many thanks

As a newbie, I can’t click the “Like” button for your solution

You can click V - “answer correct” :slight_smile:

AnswerCorrect.png

If changed the values in table ‘Sachkonten’ the Comments will not be actualisied.
It is possble to start the makro for all cells? (similar to pressing [f9])