[Solved] Macro with formula returned empty data cells?

[dennlv]

Using record macro, i managed to mimic having to repeatedly enter formula in column c to look up column a for same word and then concatenate column b data together if column a has the same word.

However running the macro only return column c empty?

The formula on c2 is something like =if(a2=a1;c1&";"b2;b2). Will upload the .ods and macro when i get the chance later.

Any help would be appreciated. Thxs

[FJCC]

If I understand you correctly, you do not need to write a macro. To fill the formula down the column, first enter your formula in C2. Then click on C2 and it will be surrounded by a black border with a small square in the lower right corner. Double click on that square and the formula will be filled down as far as there are data in column B. Alternatively, you can click and hold on that small square and drag down to fill in the formula.

[dennlv]

No i need a macro to do this as it will be repetitive. Issue with macro not recording the formula which resulted in empty cell when run.

[Zizi64]

Issue with macro not recording the formula which resulted in empty cell when run.

The macro recorder has a limited capability. You must WRITE your macro code - based on the functions and procedures of the API - (instead of recording it), is you want work efficiently with the macros.

API = Application Programming Interface. You can use the API functions from all of available and supported programming languages.

[dennlv]

Thxs Zizi64, i have tried looking at the codes but i might not have the capability as someone without programming knowledge. Would you be able to help point how i can insert the formula into the macro i shared for me to try start somewhere?

[Villeroy]

http://www.pitonyak.org/oo.php
[Tutorial] Introduction into object inspection with MRI

A ready made solution for pseudo-databases on sheets: [Calc, Python] Expand/shrink list ranges

[dennlv]

Thanks.
I seems to progress by inserting the string of formula myself but there is the next issue of inserting the formula [=IF(I2<>I3;"last";"")] without [] as "quotes" is a allowed in building macro. Any tips to cross this next hurdle?

[Zizi64]

All of the cells can store a numeric value AND a string AND a formula in same time.

When you put a formula into a cell (manually or by a macro) it will be calculated, and the result will be a numeric value or a string. When the result is a numeric value, the string - stored by the cell - will be the formatted textual view of the numeric result.
You can modify all of the types of the cell content (string, value, formula) by the API functions.

viewtopic.php?f=20&t=92611
https://wiki.openoffice.org/wiki/HU/Doc ... and_Ranges
https://ask.libreoffice.org/en/question ... cro-basic/
https://ask.libreoffice.org/en/question ... a-formula/

[MrProgrammer]

i managed to mimic having to repeatedly enter formula in column C …

No i need a macro to do this as it will be repetitive.

There may be better ways to accomplish this task than with an evil macro. You should explain your ultimate goal, not the step you have decided upon. Why do you need to populate column C repeatedly? Do you need to populate other columns too? Are you doing this in multiple sheets? Or in multiple files?
The XY Problem
"The problem occurs when people get stuck on what they believe is the solution and are unable step back and explain the issue in full."

Issue with macro not recording the formula which resulted in empty cell when run.

This is known issue 111471: Calc, record macro, cells with function written manually not recorded
Use Insert → Function instead of typing the formula directly into the cell. Personally I think it's easier to replicate the formula with Edit → Fill → Down instead of Copy/Paste. I have added the "Issue" icon to your post.

The macro recorder has a limited capability.

Yes, however entering something in a cell and filling it down is very simple with the user interface and a task that the macro recorder should handle. It doesn't due to the issue above. The issue can be circumvented with Insert → Function or by omitting the initial equal sign in the formula then performing a simple edit on the recording. This is much easier than spending a couple of weeks to learn how to write macros using the UNO application programming interface.

I seems to progress by inserting the string of formula myself but there is the next issue of inserting the formula [=IF(I2<>I3;"last";"")] without [] as "quotes" is a allowed in building macro. Any tips to cross this next hurdle?

I don't understand this post. Did you enter the [] to circumvent the issue above? If not, why did you use [] since this is not valid formula syntax? If so, use Tools → Macros → Organize macros → OpenOffice Basic → Edit, then remove the [] and save the edited macro.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[dennlv]

Wow Mrprogramer, your insert crtl+F2 during recording did it for me. Sorry about not being clear as most of my time, i was typing away using hp. Yes, i can now use this macro on files with the same formatted worksheet! Looks like macro here is better than excel! And the prompt and quality replies here is where one dare to invest time in!

Well done guys and Thank you very much!