OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Going daft with exasperation!

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
GSD4ME
General User
General User


Joined: 16 Feb 2011
Posts: 13

PostPosted: Sun Jul 22, 2012 2:02 pm    Post subject: Going daft with exasperation! Reply with quote

HELP!!!!

It should be simple but I cannot get it to work nor find the answer!

I have a row of values on a spreadsheet, with blanks in between them
(I don't know how many values, don't know how many blanks in between each value as they are created 'offline' and supplied)

I want to enter a MATCH/or similar formula that will find the 'next' value greater than 0.0
So, for example

row = <blank><blank><blank>123.55<blank>99.50<blank><blank>76<blank>7<blank><blank>85.0
SO in my Macro I want to start at column 1 (0 in macro terms) and do a search for the next value in the row that is "greater than or equal to 0.0".
I then go to that cell, get the value out from it, do something with it, then repeat the search and so on until the end of data.
I have tried (on the spreadsheet rather than the macro side) the formulae MATCH, LOOKUP, VLOOKUP, HLOOKUP but cannot seem to get the correct formula or syntax to fulfil my requirements. Once I have found what I want I can then program the basic macro to do what I want.
Can anyone shed any light on my problem - all the online searches for MATCH etc use strings or cell references which I don't want - I want to use numeric values.
It should be simple but perhaps my brain is overloaded today.
Many thanks
Back to top
View user's profile Send private message
karolus
OOo Advocate
OOo Advocate


Joined: 22 Jun 2011
Posts: 210

PostPosted: Sun Jul 22, 2012 11:26 pm    Post subject: Reply with quote

Hallo

as Calc-formula:
=INDEX(A1:Z1;SMALL(IF(A1:Z1;COLUMN(A1:Z1);"");COLUMN(OFFSET($A$1;0;0;1,COUNT(A1:Z1)))))

entered as Matrix-formula (ctrl+shift+enter) or [x]Matrix-option in Formulawizard

Karo
Back to top
View user's profile Send private message
patel
Power User
Power User


Joined: 14 Apr 2012
Posts: 54
Location: Italy

PostPosted: Sun Jul 22, 2012 11:36 pm    Post subject: Reply with quote

Code:
sub aaa
Dim oCell As Object, oCursor As Object, aAddress As Variant
Doc = ThisComponent
Sheet = Doc.Sheets.getByIndex(0)
oCell = Sheet.GetCellbyPosition( 0, 0 )
oCursor = Sheet.createCursorByRange(oCell)
oCursor.GotoEndOfUsedArea(True)
aAddress = oCursor.RangeAddress
LastUsedColumn = aAddress.EndColumn
oRow=0
for j=0 to LastUsedColumn
  Cell = Sheet.getCellByPosition(j,oRow)
  if cell.value > 0 then
' >>>>> insert your code
'  cell.value = 1 
  endif
next
End sub

_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group