0

I need to find the number of data points within a single column and place the value in a given cell on an OpenOffice Calc Spreadsheet. Here is my code so far I'd appreciate if someone can tell me where I'm going wrong or what is missing. Thanks.

Const DATA_COL = 1 'Column number for data Const DATA_ROW_START = 2 'Start row for data

'This function finds the number of data points in the data column DATA_COL 'The aim of this function is to determine the number of data points in Column A 'You need to make use of the constants DATA_ROW_START (= 2) and DATA_COL (= 1) 'in this function. Specifically, the function uses the information that the first data point is in row DATA_ROW_START (= 2) and in the data are in column DATA_COL (= 1).

Function FindNumberOfDataPoints() As Long

Dim DATA_COL as Double
Dim DATA_ROW_START as Double
Dim count as Double
Dim row as Integer

count = 0
row = DATA_ROW_START 

While ActiveSheet.Cells(DATA_ROW_START,DATA_COL)<>"" 
        row = row + 1
        count = count + 1

Wend

If ActiveSheet.Cells(DATA_ROW_START,DATA_COL)> 0 Then
    row = row + 1

End If


count = ActiveSheet.Cells(row,DATA_COL)

ActiveSheet.Range("C11") = count 'Place count value in cell C11

End Function

dirty mike
  • 1
  • 1
  • It looks like your code is modeled off VBA. OpenOffice Basic has different syntax. Try searching on the OpenOffice forums for some code snippets. – Lyrl Oct 06 '15 at 17:20

1 Answers1

0

You can easily find the used area, for example, this finds the last used row

Function getLastUsedRow(oSheet) as Integer
  Dim oCell
  Dim oCursor
  Dim oAddress
  oCell = oSheet.GetCellbyPosition( 0, 0 )
  oCursor = oSheet.createCursorByRange(oCell)
  oCursor.GotoEndOfUsedArea(True)
  oAddress = oCursor.RangeAddress
  GetLastUsedRow = oAddress.EndRow
End Function

You just need to look at the column rather than the row.

Andrew
  • 816
  • 7
  • 15