Macro to copy date from cell above gives problem sorting

[wysocki]

I have a macro that processes a row and if the date cell is blank I want to copy the date from the cell above it. The code works fine, but then if I sort that column, the copied cells appear at the bottom of the list, evidently because they are not actually date format. I’ve tried a lot of solutions (chatgpt gave failing examples 9 times!) so how can it be done? here’s a section of what I have now:

oRange = oSheet.getCellRangeByName("A3:Z" & lastRow)
For i = 0 To lastRow - 3
  oCell = oRange.getCellByPosition(0, i) 'date column
  If oCell.getString() = "" Then
    ' Replace empty with value from above
     oCellAbove = oRange.getCellByPosition(0, i - 1)
     oCell.setString(oCellAbove.getString())
     If oCell.Type = com.sun.star.table.CellContentType.TEXT Then
        'Convert text to date
        On Error Resume Next
        oCell.String = CDate(Trim(oCell.String)) ' Converts date formats
        On Error GoTo 0
    End If
  End If
Next i

[Villeroy]

If you set the string of a cell, it gets a string actually. Your code does not convert text to date. This is AI bullshit.

[fpy]

@wysocki

I’ve tried a lot of solutions

like what ?

@Villeroy

it gets a string actually

and if you copy a formula, what would it get ?

[Villeroy]

@fpy

and if you copy a formula, what would it get ?

Both, setValue and setFormula writes the correct unformatted value, even if there is no formula.
I prefer FormulaLocal which is what you see in the formula bar. If the string represents a date or percent value you get a date or percent value.

ac = ThisComponent.getCurrentSelection()
adr = ac.getCellAddress()
sh = ac.getSpreadsheet()
pre = sh.getCellByPosition(adr.Column, adr.Row -1)
ac.FormulaLocal = pre.FormulaLocal

[fpy]

@Villeroy

correct unformatted

wishful ?

enjoy your code with bs.ods

[Villeroy]

Your A2 is preformatted as text. I would always insert a new row before doing such an operation.

[fpy]

@Villeroy

always

and forget to tell the OP about it …

might well be what is formulated as

@wysocki

failing examples 9 times!

[Villeroy]

Try this instead of AI generated code:

• Select the range from the first gap until the end of the list. Can be more than one column with gaps.
• Copy the selection
• Keep the selection, type =A1 where “A1” means the cell above the input cell, confirm with ALT+Enter
• Paste-special [Ctrl+Shift+V], paste “All” with option “Skip empty cells”.

• Now you have all the original values and all gaps filled with relative references to the above cell.
• Now you can use copy&paste-special a second time to convert all formulas to values. Turn off “skip empty cells”.

[Lupp]

My suggestions are exemplified in the attached file.
disask117463_fillGapsInDateColumn.ods (20.1 KB)
===
[edit 2025-02-04]
This answer was accepted as solution, but the approach used by @karolus is clearly preferable.
I didn’t think of the fillSeries() method.
It makes things simpler, and for large ranges much faster.
My reworked version is contained in the new attachment:
disask117463_fillGapsInDateColumn_enh.ods (41.6 KB)
[/edit]

[karolus]

Hallo
with python:

def fill_empty_gaps(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    _range = doc.CurrentSelection
    sheet = _range.Spreadsheet
    for empty in _range.queryEmptyCells():    
        cursor = sheet.createCursorByRange(empty)
        cursor.gotoOffset(0,-1)
        cursor.collapseToSize(1, cursor.Rows.Count + 1)
        cursor.fillSeries(0,0,0,0,0)

or basic:

sub fill_empty_gaps()
    doc = ThisComponent
    range = doc.CurrentSelection
    sheet = range.Spreadsheet
    for each empty in range.queryEmptyCells()    
        cursor = sheet.createCursorByRange(empty)
        cursor.gotoOffset(0, -1)
        cursor.collapseToSize(1, cursor.Rows.Count + 1)
        cursor.fillSeries(0,0,0,0,0)
    next
end sub
```</s>

[fpy]

@karolus

or basic:

sub fill_empty_gaps()

same remark ?

@Villeroy

This is AI bullshit.

[karolus]

@fpy

Oh my bad…I totally missed to test the basic-stuff

[Villeroy]

For each empty uses a Basic keyword as variable name.

[karolus]

@Villeroy

a Basic keyword

and this f…ing Basic-IDE cannot highlight »keywords« in a different color.