This works with your sample file. The comments should make it self explaining. I hope.
rem helper to change the cell format ... shamelessly copied from the macro bibel.
Function FindCreateNumberFormatStyle (sFormat$, Optional oDoc, Optional locale)
Dim oDocument As Object
Dim aLocale as new com.sun.star.lang.Locale
Dim oFormats As Object
Dim formatNum As Long
oDocument = IIf(IsMissing(oDoc), ThisComponent, oDoc)
oFormats = oDocument.getNumberFormats()
'I could set the locale from values stored at
'http://www.ics.uci.edu/pub/ietf/http/related/iso639.txt
'http://www.chemie.fu-berlin.de/diverse/doc/ISO_3166.html
'I use a NULL locale and let it use what ever it likes.
'First, see if the number format exists
If ( Not IsMissing(locale)) Then
aLocale = locale
End If
formatNum = oFormats.queryKey (sFormat, aLocale, TRUE)
'MsgBox "Current Format number is" & formatNum
'If the number format does not exist then add it
If (formatNum = -1) Then
formatNum = oFormats.addNew(sFormat, aLocale)
If (formatNum = -1) Then formatNum = 0
'MsgBox "new Format number is " & formatNum
End If
FindCreateNumberFormatStyle = formatNum
End Function
rem magic subroutine
SUB DOSTUFF
needles = Array("audi","alfa", "bmw","citroen", "mercedes")
searchcol = "A" rem search in this column
startrow = 6 rem start search in this row
endrow = 17 rem end searching in this row
putcol=7 rem start colum to insert data (here: A=1, B=2, ... H=7, ...)
putrow=7 rem startrow to put data
formatcode = "#,##0.00 [$EUR];-#,##0.00 [$EURO]"
oSheet = ThisComponent.CurrentController.ActiveSheet
Dim val as double
For i = startrow To endrow
For j = LBound(needles) To UBound(needles)
Set oCell = oSheet.getCellRangeByName(searchcol & i)
if ( InStr(1, oCell.String, needles(j), 1) > 0 ) then
rem copy value and change cell format
val = oSheet.getCellRangeByName("E" & i ).Value
oSheet.getCellByPosition( putcol, putrow+0 ).Value= val
oSheet.getCellByPosition( putcol, putrow+0 ).NumberFormat = FindCreateNumberFormatStyle(formatcode, ThisComponent)
rem copy value and change cell format
val = oSheet.getCellRangeByName("D" & i ).Value
oSheet.getCellByPosition( putcol, putrow+2 ).Value= val
oSheet.getCellByPosition( putcol, putrow+2 ).NumberFormat = FindCreateNumberFormatStyle(formatcode, ThisComponent)
putcol=putcol+1
endif
Next
Next
End SUB
If you have any further question, just ask.
Hope that helps.
To show the community your question has been answered, click the ✓ next to the correct answer, and “upvote” by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!
Have a nice day and let’s (continue to) “Be excellent to each other!”
Ask / Getting Started:
https://wiki.documentfoundation.org/Ask/Getting_Started
