Search for a sequence of numbers from bottom of spreadsheet...

[userx]

This question may seem odd and is perhaps downright whimsical. I have searched for macros that could serve my purpose but have not found anything that would search in reverse numerical sequence from the bottom of a spreadsheet. I am using LibraOffice version 4 on Centos 6.5. My spreadsheet has 25 columns and many rows. The cells of each row/column are populated with a number from 1 to 50 and arranged in no particular order. A new row of numbers are frequently added at the bottom of the sheet. I need to write a macro that will:

1. Find the last row containing data.
2. For each column and value in a cell in the last row, search the previous rows for the next lower value until the entire sequence of numbers (1 through 50 and a max one number decremented per row) have been found. For example, if a cells value on the first column in the last row = 5 then search backwards row by row for the numbers 4,3,2,1,50,49.48…10,9,8,7 and finally 6. Perform this on each column of each row.
3. Keep track of the number of rows passed during the search and divide Nrows by 50 for the average rows a number decrements.
4. Rerun macro for each new row that is added.

I would really like to read some suggestions or example code on how this can be done Seems that it should be a for loop in reverse?

[Regina]

The task is not clear. If you do not have exact 50 rows, then you will have gaps or double numbers. What to do? Are all rows filled, or do empty cells exist? Why you need to add the new rows at the end? Add it at the beginning and you can use normal search functions. What do you want to do with the resulting average? Why use a macro and not a separate sheet for calculation?

[Avio]

I think you’re quickly approaching the threshold where it’s just simpler to use bash + grep + awk (or python if you prefer).

[als50]

This is probably OBE but if you are playing around and would still like something to get started…

get the last row and column:

Dim oCell As Object, oCursor As Object
   Dim endCol As Long, endRow As Long
  oCell = oSheet.getCellByPosition(0,0)		' set to the first cell in the sheet
  oCursor = oSheet.createCursorByRange(oCell)	' create a cursor
  oCursor.GoToEndOfUsedArea(True)			' move cursor to the end of the data
  endCol = oCursor.RangeAddress.EndColumn		' assign the index of the last column
  endRow = oCursor.RangeAddress.EndRow		' assign the index of the last row

get the value of a cell:

oSheet.getCellByPosition(column, row) ’ column & row are Long

the rest is just counters and looping. A for loop decrementing from the last row will work.