[Solved] Autofill w/o formatting

Discuss the spreadsheet application

[Solved] Autofill w/o formatting

Postby wendi » Sun Dec 22, 2019 12:32 am

OO 4.1.6; Win10Pro64. Is there a way to autofill down a column, without changing the formatting of the cells being filled. In particular, I'm autofilling down a column, in which certain rows have a colored background. After autofill, the backgrounds are returned to white, and I need to go back and manually restore their background colors. Thanks.
Last edited by MrProgrammer on Fri Dec 27, 2019 11:16 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
OpenOffice 4.1.5 on Windows 10
wendi
 
Posts: 9
Joined: Wed May 23, 2018 10:23 pm

Re: Autofill w/o formatting

Postby Zizi64 » Sun Dec 22, 2019 12:52 am

Here are some workaround tips:
viewtopic.php?f=9&t=37717

Or you can write your own Autofill macro routines.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.0; AOO4.1.6
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.
User avatar
Zizi64
Volunteer
 
Posts: 8759
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Autofill w/o formatting

Postby wendi » Sun Dec 22, 2019 1:01 am

Thank you Zizi64. I guess the short answer is "not easily." Seems like it would be a useful feature to include at some point. W.
OpenOffice 4.1.5 on Windows 10
wendi
 
Posts: 9
Joined: Wed May 23, 2018 10:23 pm

Re: Autofill w/o formatting

Postby Zizi64 » Sun Dec 22, 2019 10:44 am

My tip (for the workaround):
Use the Cell styles instead of the direct (manual formatting).
It is easier to restore the property group of the cells modified by the Autofill function: You can set/restore the "content type/textcolor/background color/alignment/borders/protection" of a cell or a cellrange by one double click on the Style name on the Sidebar.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.0; AOO4.1.6
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.
User avatar
Zizi64
Volunteer
 
Posts: 8759
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Autofill w/o formatting

Postby Zizi64 » Sun Dec 22, 2019 1:17 pm

Here is a macro code, what will fill the selected cell range (a partial column) with an arithmetical serie. The start value is given in the first cell, the difference will be calculated based on the first value and the second value. You need enter these values before you select the desired cell range and launch the macro.
Tha macro will modify the cell values only but will not modify the formatting properties.


Code: Select all   Expand viewCollapse view
REM  *****  BASIC  *****

Option explicit

Sub FillColWithArithmeticalSerie

' This sub can fill a selected one dimensional cell range (a partial column)
' with an arithmetical serie, the start value and the difference determined from the first and the second value,
' what you need enter into the two cells before you launch this subroutine.
'
Dim oDoc as object
Dim oView as object
Dim oSheet as object
Dim oSel as object
Dim oAddr as object
Dim lStartCol as long
Dim lStartRow as long
Dim lEndCol as long
Dim lEndRow as long
Dim Difference as double
Dim Previous as double
Dim Actual as double
Dim j as long
Dim i as long
Dim oCell as object

   oDoc = ThisComponent
   oView = oDoc.getCurrentController()
   oSheet= oView.getActiveSheet()
   oSel = oDoc.getCurrentSelection()
   oAddr = oSel.getRangeAddress()   

   lStartCol = oAddr.StartColumn
   lStartRow = oAddr.StartRow
   lEndCol = oAddr.EndColumn
   lEndRow = oAddr.EndRow

   if lEndRow-lStartRow<=2 then
      Print "Error: too few cells selected!"
      Exit sub
   end if
   if (lEndRow-lStartRow)>65535 or (lEndCol-lStartCol)>=1   then
      Print "Error: Too many rows/columns selected!"
      Exit sub
   end if
   Previous = oSheet.getCellByPosition(lStartCol,lStartrow).value
   Difference = oSheet.getCellByPosition(lStartCol,lStartrow+1).value - oSheet.getCellByPosition(lStartCol,lStartrow).value

   j = lStartCol
   oCell = oSheet.getCellByPosition(j,lStartRow)
   oCell.value = Previous
   for i = lStartRow+1 to lEndRow   
      oCell = oSheet.getCellByPosition(j,i)
      Actual =  Previous + Difference
      oCell.Value = Actual
      Previous = Actual
   next 'i

End Sub
rem ----------------------------------------------------------------------
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.0; AOO4.1.6
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.
User avatar
Zizi64
Volunteer
 
Posts: 8759
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Autofill w/o formatting

Postby Zizi64 » Sun Dec 22, 2019 1:45 pm

And here is an ODF type sample file:
series_macro.ods
(12.33 KiB) Downloaded 3 times
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.0; AOO4.1.6
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.
User avatar
Zizi64
Volunteer
 
Posts: 8759
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Autofill w/o formatting

Postby wendi » Sun Dec 22, 2019 8:27 pm

Zizi64... Thanks again for all of the ideas. W.
OpenOffice 4.1.5 on Windows 10
wendi
 
Posts: 9
Joined: Wed May 23, 2018 10:23 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 7 guests