Object variable not set

[iFunction]

could someone please explain what this means, I get it all the time and have not been able to discover how it gets fixed. I found this code online as I am trying to find a way to loop through 5 cells on a spreadsheet which is really easy in Excel, not so here it seems:

Dim Doc, Sheet, Descript, Cell, CellRange as object
Dim i as integer
Doc = ThisComponent
Sheet = Doc.Sheets(0)
For i = 2 to 6
    CellRange = Sheet.getCellRangeByPosition(2,i,4,i)
    Descript = CellRange.createSearchDescriptor()
    Descript.SearchString = "54321"
    Descript.SearchWords = True 'If true, the search will match only complete words
    Cell = CellRange.findFirst(Descript)
    If Not IsNull(Cell) then
        Print "Row = " + (Cell.CellAddress.Row + 1)
    End if
next i

Please help I’ve hit a brick wall now with this so my only recourse left is to flood the forums till I get a reply.

Thanks in advance.

[JohnSUN]

Do you call it as user-define function from formula in cell? Just verify that Doc.getSheets() not null and not empty. Calc try to start calculate formulas before all services of spreadsheet are loding. So .Sheet(0) not present on first recalculation

[karolus]

please stop this rude Crossposting Object Variable not set (View topic) • Apache OpenOffice Community Forum

in conjunction with several other threads wth similar questions@same time.

[PYS]

Hi - Not an answer (see @JohnSUN’s comment) but I’m surprised by this use of FindFirst. In that case I’d use something like:

Dim Doc, Sheet, Descript, Cell, CellRange as object

Doc = ThisComponent
Sheet = Doc.Sheets(0)

CellRange = Sheet.getCellRangeByPosition(2,2,4,6)
Descript = CellRange.createSearchDescriptor()
Descript.SearchString = "54321"
Descript.SearchWords = True 

Cell = CellRange.findFirst(Descript)
while Not IsNull(Cell) 
	Print "Row = " + (Cell.CellAddress.Row + 1)
	Cell = CellRange.findNext(Cell, Descript)
wend

Note: You also know that it is better to declare a long variable to manage row numbers (but this is of course not annoying for this example).

Regards

[iFunction]

Yes to be honest, I had hit a brick wall with this and was just trying anything to get an understanding of making a search or comparison function. This application is for entering data to track fuel and tyres in a pit lane during events, so the UI had to be super simple so as not to distract engineers and technicians, it is also customer facing, so had to be a little on the smoke and mirrors side of things. The thing is, they all loved it and realized this was much more acurate than using a quill and parchment and now the engineers have asked for a summary page so they can view the data during the event. So I need to loop through 5 rows of 5 possible sets of tyres (If it rains they will change to wets and then when it drys again, they will go back to the previous set, it’s a cost thing) so I am going to try a more simple approach of a simple loop and if statement, but I still have to find the correct syntax I had something like this in mind:

Sub Summary(iKilometers)                            ' iKilometers is the Kilometers of the last run pre calculated
    Dim gsTyres As String                       '
    Dim tSearch As string                       ' Tyre set being changed to 
    Dim iTyreLife As Integer
    Dim j As Integer
    
    Doc = ThisComponent
    runSheet = Doc.Sheets(0)
    sumSheet = Doc.Sheets(1)
    
    'Summary sheet uses a box of 3 colums by 5 rows the columns are are tyre set, time, and tyre life(miles)
    For j = 1 to 5
         If tyreSet = tSearch then
              iTempKm = sumSheet.getCellByPosition(3, j).Value
              iTyreLife = iTempKm + iKilometers
              sumSheet.getCellByPosition(2, j).String = TIME()             ' Time Stamp, date is dealt with        
              sumSheet.getCellByPosition(3, j).Value = iTyreLife     
              Break out of loop somehow                                             ' This is another bit of syntax I simply can't find
         Else
              NewRow()                                                                      ' returns i as new row value in current sheet
              sumSheet.getCellByPosition(1, i).String = gsTyres           ' global value for tyres currently on the car
              sumSheet.getCellByPosition(2, i).String = TIME()
              sumSheet.getCellByPosition(3, i).Value = iKilometers
         End If
    Next i 

End Sub

Actually I have just written this now, and it is nearly there, it just needs a couple of bits of syntax and any comments about improvement to this will be taken on board, I am as I mentioned currently still just learning to program, and good programming practices are always noted.

Kind regards
iFunct

[iFunction]

apologies for the bad formatting, unfortunately the comments have overshot sending the code the wrong colour.