Calc Macro loses cell format after data update

[davewall]

I have a large spreadsheet that I record solar panel data into - I have a single line .csv with a date then numeric fields that I read and split with comma delimiter. Then Find the same date in my sheet and put the other numeric data into the relevant columns. This works fine IF I ‘copy’ the data to the clipboard externally then ‘paste’ it into the sheet but I thought it would be better to read the .csv directly in the macro and put the data in that way.
The cells that receive the data are all formatted numeric with 3 decimal places however when I put the data (by value) into the cells they become text formatted (with 1 exception - for some strange reason).
I knocked up a test macro that works perfectly however the main macro has this stupid problem.
What am I doing wrong.

Test Macro:

Results - which are correct:

My Main macro - which I believe is almost identical in procedure to the Test Macro.:

Results - Which are definitely NOT right. as the formatting has gone.

Help ??.

[davewall]

The strange thing is that the LAST field 14.690 DOES come out correctly and doesn’t change the cell format to text. If only all the fields did that there wouldn’t be a problem.

[JohnSUN]

It’s not hard to understand. Set a breakpoint on line 17, right after SPLIT(). See? You described aFields as an array of Variants, and you expected it to be that way for the duration of the macro. But the SPLIT() function turned it into an array of Strings, into texts. And all subsequent code already worked not with numbers, but with strings.

Perhaps if you change the line
...Value = aFields(iNum)
to
...Value = Val(aFields(iNum))
the result will be better?

[karolus]

Hallo
»Val« is harmful ( with german localisation ):

msgbox( val(17.9))  '#--> 179  '
'## i would suggest:'
...FormulaLocal = aFields( iNum )
'# or better'
target = thisComponent.Sheets(0).getCellRangeByPosition( iNum, rowrow -1, iNum+7, rowrow-1)
target.FormulaArray = array(aFields))

[JohnSUN]

In addition, you can try this trick - it may also work correctly:
...getCellByPoition(iNum+1, rowrow - 1).setFormula(aFields(iNum))

[davewall]

That final post was the solution - much appreciated. There was also another problem but perhaps I should make it as a different post. I’ll describe it here anyway.:
IF the input data contains a complete date - ie: 16/08/2023 as the first field in the .csv - then the date is never found in column B - which is described as Date 31/12 even though the actual data in that column contains the full date: 16/08/2023. IF I reduce the .csv back to 16/08 then the macro works.
I’d have liked to retain the 16/08 display in the sheet but had the macro locate the correct line using the 16/08/2023 full date.
Any Ideas ??.

[davewall]

Regarding the second solution:

target = thisComponent.Sheets(0).getCellRangeByPosition( iNum, rowrow -1, iNum+7, rowrow-1)
target.FormulaArray = array(aFields))

I’m not really sure what this was trying to achieve but it placed the date 16/08 in columns 3 thru 6 and the remaining data in the following columns - strange. I tried the following solution which worked so I didn’t bother to query it at the time. Thanks for responding anyway.

[JohnSUN]

@davewall

IF the input data contains a complete date - ie: 16/08/2023 as the first field in the .csv

Just try this

Dim dateDouble As Double 
	dateDouble = DateValue(Right(dateString,4) & "-" & Mid(dateString,4,2) & "-" & Left(dateString,2))
 	rowrow = oFA.callFunction("MATCH",Array(dateDouble,oRange,0))

[davewall]

It gets an error as follows:

Inadmissible value or data type.
Data type mismatch.

On Line: dateDouble = DateValue(Right(dateString,4) & “/” & Mid(dateString,4,2)
& “/” & Left(dateString,2))

I changed your - to be / as that’s what the data is.

Bear in mind that both the .csv data and the data in the sheet are in the format

DD/MM/YYYY - eg: 16/08/2023

And the above appears to be turning it about to be: yyyy/mm/dd

which wouldn’t match the data in the sheet anyway.

Thanks for looking at it though & thanks for the previous fix.

David

[JohnSUN]

@davewall

Bear in mind that both the .csv data and the data in the sheet are in the format DD/MM/YYYY

Yes, it was precisely because of the DD/MM/YYYY format that conversion to YYYY-MM-DD was needed - the DateValue() function will correctly understand what date it is.

[davewall]

Well - I’ve gone back to not bothering with the yyyy portion & it works fine - much appreciated.