How to write data into NamedRange

Hi. I can read data from these cells, but utterly stumped how to write new data into them. Please see following snippet:

Sub Test

REM	Get this document ("Pay sheet") 
	Dim	Paysheet
	Paysheet = ThisComponent

REM	Get "Time Sheet" sheet and various ranges
	Dim	Timesheet
	Timesheet = Paysheet.sheets.getByName("Time Sheet")

	Dim	CarerTimes as Object
	CarerTimes = Timesheet.getCellRangeByName("CarerTimes")

	Dim	CarerName
	Dim	CarerStatus
	Dim	CarerNrmlHrs

REM	Loop counter
	Dim	nTimes
	nTimes = CarerTimes.RangeAddress.EndRow - CarerTimes.RangeAddress.StartRow
	Dim j
	For j = 0 to nTimes
REM	Can read these array values, but utterly stumped how to put new data into them

			CarerName = CarerTimes.DataArray(j)(0)
			CarerStatus = CarerTimes.DataArray(j)(2)
			CarerNrmlHrs = CarerTimes.DataArray(j)(4)
			MsgBox	CarerName+"  "+CarerStatus+ "  "+CarerNrmlHrs
	Next j
End Sub

[erAck: edited to codify using ``` see This is the guide - How to use the Ask site? - #6 by erAck ]

The method .getDataArray returns array of arrays, concretely: Array( array(values from row1), array(values from row2), etc.)
And for method .setDataArray you must use the same format array of arrays.

Sub getSetData
	dim oDoc as object, oSheet as object, oRange as object, data()
	oSheet=oDoc.Sheets.getByName("Time Sheet") 'your sheet
	oRange=oSheet.getCellRangeByName("A1:B2") 'your range (in your case CarerTimes)
	data=oRange.getDataArray() 'get data; the format is: Array( array(values from row1), array(values from row2), array(data for row3) etc. )
	'install the extension XRay or MRI to see the values from an array
	'xray data(0) 'array with data from 1st row
	'xray data(1) 'array with data from 2nd row
	'mri data(2) 'array with data from 3rd row
	rem set new data
	dim data2()
	data2=Array( array(15, 25), array(35,40) ) 'Array( array(data for row1), array( data for row2), ... )
End Sub
1 Like

Cheers! That helped heaps :grinning:

1 Like