[Solved] Replicate "non-repeating" row labels

Discuss the spreadsheet application
Post Reply
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

[Solved] Replicate "non-repeating" row labels

Post by eeigor »

 Edit: Split from Paste cell to more than one non-contiguous cell since this is a new subject that the OP did not ask about 
There are some tasks...
And sometimes data is stored with "non-repeating" row labels. And you need to fill in the blanks to process the data in the pivot table.
And keeping them "correct" means storing redundant data.

I have two macros for this case: SelectEmptyCells() and FillEmptyCells(). Exclusively for filling with values ​​in the rows above.
And here is how the value assignment works

Code: Select all

'   Assigns a value (formula, number, or text) to cells in a rectangular range.
Sub AssignValueToRange(oRange As Object, value)
	' The .Formula property should cover all input needs, even numbers.
	oRange.getCellByPosition(0, 0).Formula = value  'the lead cell
	With com.sun.star.sheet.FillDirection
		oRange.fillAuto(.TO_BOTTOM, 1)  '0
		oRange.fillAuto(.TO_RIGHT, 1)  '1
	End With
End Sub
Fills in all the blanks from left to right and top to bottom in one go.

UPD:
And if you need to enter a different formula?
Question: How do I get the value in cell A3 programmatically so that I can enter it into the rest of the blank cells?

In Excel, this task is solved simply, as requested by the author of the topic (with multiple insert).
Attachments
Снимок экрана от 2021-03-22 20-37-10.png
Снимок экрана от 2021-03-22 20-37-49.png
Снимок экрана от 2021-03-22 20-37-49.png (7 KiB) Viewed 1440 times
Снимок экрана от 2021-03-22 20-38-27.png
Last edited by Hagar Delest on Mon May 17, 2021 6:22 pm, edited 16 times in total.
Reason: tagged solved.
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Issue] Paste cell to more than one non-contiguous cell

Post by Villeroy »

I have written a set of macros to fill the gaps: https://sourceforge.net/projects/ooomac ... on%201.01/ In the zip you find a demo document and a macro library. Routine "fillBlanks_Down" should do the trick. It takes a single selection and fills all blank ranges with values from above
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: [Issue] Paste cell to more than one non-contiguous cell

Post by eeigor »

@Villeroy, but is it possible to read the value that the user enters into a cell (not an InputBox), so that you can then use it in a procedure?
The situation is as in the screenshot above.

UPD:
@Villeroy, thanks for not closing the code. I watch with interest everything that you offer.
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Issue] Paste cell to more than one non-contiguous cell

Post by Villeroy »

This trick is even better than a macro: viewtopic.php?f=9&t=100434&p=483351#p483354
MrProgrammer wrote:Select A3:F14; Edit → Copy; With A3:F14 selected, type formula =A2 into A3 hold the ALT key and press Enter; Edit → Paste Special → Paste all → Skip empty cells → OK
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
eeigor
Posts: 214
Joined: Sun Apr 12, 2020 10:56 pm

Re: [Issue] Paste cell to more than one non-contiguous cell

Post by eeigor »

MrProgrammer wrote:Select A3:F14; Edit → Copy; With A3:F14 selected, type formula =A2 into A3 hold the ALT key and press Enter; Edit → Paste Special → Paste all → Skip empty cells → OK
I did this trick ... To be honest, I didn't immediately realize how I got the desired result.
@Villeroy, that's right :!:

After replacing the entire selected range (with real data along with empty cells) with one formula, we get, it seems, the same result (?) Is the data overwritten?
Everything that is in red is empty in the buffer (see the post above).
We insert data from the buffer with option "Skip empty cells" set, and "empty" cells, but with formulas now are recalculated...
Who invented this?

In a sense, this is also an answer to the author of the topic.

UPD: fixed some errors in screenshots below, file is attached
Before and after pasting from the clipboard, skipping empty cells. Initially empty cells on the sheet are formatted in red.
Attachments
Example03 (multiple insert).ods
(25.31 KiB) Downloaded 91 times
Снимок экрана от 2021-03-24 06-36-17.png
Снимок экрана от 2021-03-24 06-37-18.png
Ubuntu 18.04 LTS • LibreOffice 7.5.3.2 Community
Post Reply