Hi. I am a newbie here and first time on the forum.
What I have to do is select a number of empty vertical cells, this can range from 2 to 30 cells, go to format, merge and place a 1 in the now single merged cell then go on to the next series of cells. I have been looking at macros as these seem to be the best way to do it but I can't see how I can use them for my purpose.
Can anyone help me please.
I am using OpenOffice 4.1.14 and windows 10.
Thank you in advance.
[Dropped] Using a macro to format cells and enter a value
[Dropped] Using a macro to format cells and enter a value
Last edited by MrProgrammer on Thu Apr 06, 2023 3:31 pm, edited 1 time in total.
Reason: Dropped: No attachment provided when requested -- MrProgrammer, forum moderator
Reason: Dropped: No attachment provided when requested -- MrProgrammer, forum moderator
OpenOffice 4.1.14 on Windows 10
Re: Using a macro to format cells and enter a value.
What determines the number of cells that should be merged? That is, how can you tell whether it is 2 or 30?
Why are you merging the cells? Merged cells often cause problems with calculations. Would it work to put a 1 in all of the cells?
Can you upload and example file? To upload a file, click Post Reply and look for the Attachments tab just below the box where you type a response.
Why are you merging the cells? Merged cells often cause problems with calculations. Would it work to put a 1 in all of the cells?
Can you upload and example file? To upload a file, click Post Reply and look for the Attachments tab just below the box where you type a response.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Using a macro to format cells and enter a value
Have you tried the macro recorder?
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
Re: Using a macro to format cells and enter a value
The best way to format cells is the usage of the Cell Styles.I have been looking at macros as these seem to be the best way to do it but I can't see how I can use them for my purpose.
(The Concatenate feature is not a part of the Cell Styles.)
Please upload your ODF type sample file here.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Using a macro to format cells and enter a value
Since you (the OriginalQuestioner) call yourself a newbie, perhaps I may be allowed a comment and a question in return.
The comment: In my experience, merging is almost always a mistake and only complicates the maintenance and use of the affected sheets. At most, I occasionally make an exception for headings that affect two or three columns in a stable context.
The question: What do you want to achieve with the help of the merged cells that would not be possible without merging?
The comment: In my experience, merging is almost always a mistake and only complicates the maintenance and use of the affected sheets. At most, I occasionally make an exception for headings that affect two or three columns in a stable context.
The question: What do you want to achieve with the help of the merged cells that would not be possible without merging?
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Using a macro to format cells and enter a value
Hi. Thank you to all those that have responded to my query. You obviously require more information so I will explain what I am doing.
I have a gas fired central heating boiler that is controlled by a single wireless room thermostat,a Digistat. I also have a number of room thermostats that I salvaged from a job I was working on a few years ago. These were hard wired into the electrical system and controlled electric wall heaters. I have electronics as a hobby and thought it would make an interesting project to convert one of the room thermostats into wireless. There is also a practical side. My wife is seriously asthmatic and any small but sudden changes in the air temperature can be enough to trigger an asthma attack. With only one Digistat in the house, there are going to be places where the temperature is slightly different so by adding more controllers then it would remove any cold spots. I could go and buy a couple of new Digistats, assuming that they are still available, but they are over £150 each and the boiler is 15 years old, and although it still works okay, it doesn't make financial sense to spend that sort of money on something that may only have a couple of years of life left.
So, in order to make them wireless, I needed to capture the code that the Digistat sends out when it is switched on and off. I found and tried 3 or 4 methods on the internet but only one worked and that required connecting a receiver to the sound card of a PC and, using some sound editing software, I was able to run the On-Off sequence and record the code signal. This shows as an alternating, square waveform with sampling points at every 2 micro seconds. I saved the recording to a text file and then copied it onto a spreadsheet to work on. The complete recording is 20.8 seconds long and contains 1,000,000 sampling points. I identified where the Switch On sequence started and ended and copied and pasted that into another Worksheet in a new Workbook and did the same for the Switch Off sequence. That now gives me 138,600 sample points. I then used the If Function to determine whether the signal is positive or negative, 1 or 0. So I now have 2 spreadsheets each with 5 columns. Column A is the number of sampling points,B marks the positions of the Start and Stop signal, C is the timing, D the signal strength and E whether the value in column D is positive or negative, a 1 or a 0. The square waves vary in length from 1 sampling point to 30 or maybe more, I haven't counted them, so I need to consolidate them into just 1 sampling point, a 1 or a 0. I used the Data > Filter > Standard Filter to hide all the 0's leaving just the 1's. That is 79,774 rows of data. So in column F, I have been manually selecting the cells until I reach a hidden cell, going to Format > Merge and placing a 1 in the now single cell and this is taking me forever, that is when I thought a Macro might help.
Now, I have just had a brainwave. What, exactly, am I doing? I am comparing the value of say E2 with a known value, 1. Then while I am selecting the cells in the adjacent column E that contain 1, I am saying While E2 to E25 hold the value of 1, store the value 1 in the merged cells in column F, and so on down the line. So, I thought, how would I handle it in Visual Basic? I would store the resulting value 1 in a one dimensional array. So, now we are back to Macros again or just programming. I need to enter a formula that reads something like:
= IF ( E1 = 0 (WHILE (E1 = 0) add (0 * 0) to "ArrayName()") ELSIF (E1 = 1 (WHILE (E1 = 1) add (1*1) to "ArrayName()").
The formula is then pulled down column F and when the Array is read it should produce a line of 1's and 0's which is what I am after. Does this sound like the way forward and how do I put it into a spreadsheet?
Thank you again for your help
Bob May
I have a gas fired central heating boiler that is controlled by a single wireless room thermostat,a Digistat. I also have a number of room thermostats that I salvaged from a job I was working on a few years ago. These were hard wired into the electrical system and controlled electric wall heaters. I have electronics as a hobby and thought it would make an interesting project to convert one of the room thermostats into wireless. There is also a practical side. My wife is seriously asthmatic and any small but sudden changes in the air temperature can be enough to trigger an asthma attack. With only one Digistat in the house, there are going to be places where the temperature is slightly different so by adding more controllers then it would remove any cold spots. I could go and buy a couple of new Digistats, assuming that they are still available, but they are over £150 each and the boiler is 15 years old, and although it still works okay, it doesn't make financial sense to spend that sort of money on something that may only have a couple of years of life left.
So, in order to make them wireless, I needed to capture the code that the Digistat sends out when it is switched on and off. I found and tried 3 or 4 methods on the internet but only one worked and that required connecting a receiver to the sound card of a PC and, using some sound editing software, I was able to run the On-Off sequence and record the code signal. This shows as an alternating, square waveform with sampling points at every 2 micro seconds. I saved the recording to a text file and then copied it onto a spreadsheet to work on. The complete recording is 20.8 seconds long and contains 1,000,000 sampling points. I identified where the Switch On sequence started and ended and copied and pasted that into another Worksheet in a new Workbook and did the same for the Switch Off sequence. That now gives me 138,600 sample points. I then used the If Function to determine whether the signal is positive or negative, 1 or 0. So I now have 2 spreadsheets each with 5 columns. Column A is the number of sampling points,B marks the positions of the Start and Stop signal, C is the timing, D the signal strength and E whether the value in column D is positive or negative, a 1 or a 0. The square waves vary in length from 1 sampling point to 30 or maybe more, I haven't counted them, so I need to consolidate them into just 1 sampling point, a 1 or a 0. I used the Data > Filter > Standard Filter to hide all the 0's leaving just the 1's. That is 79,774 rows of data. So in column F, I have been manually selecting the cells until I reach a hidden cell, going to Format > Merge and placing a 1 in the now single cell and this is taking me forever, that is when I thought a Macro might help.
Now, I have just had a brainwave. What, exactly, am I doing? I am comparing the value of say E2 with a known value, 1. Then while I am selecting the cells in the adjacent column E that contain 1, I am saying While E2 to E25 hold the value of 1, store the value 1 in the merged cells in column F, and so on down the line. So, I thought, how would I handle it in Visual Basic? I would store the resulting value 1 in a one dimensional array. So, now we are back to Macros again or just programming. I need to enter a formula that reads something like:
= IF ( E1 = 0 (WHILE (E1 = 0) add (0 * 0) to "ArrayName()") ELSIF (E1 = 1 (WHILE (E1 = 1) add (1*1) to "ArrayName()").
The formula is then pulled down column F and when the Array is read it should produce a line of 1's and 0's which is what I am after. Does this sound like the way forward and how do I put it into a spreadsheet?
Thank you again for your help
Bob May
OpenOffice 4.1.14 on Windows 10