| View previous topic :: View next topic |
| Author |
Message |
GSD4ME
General User
Joined: 16 Feb 2011
Posts: 12
|
 Posted: Sun Jul 22, 2012 2:02 pm Post subject: Going daft with exasperation! |
 |
|
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 |
|
 |
karolus
OOo Advocate
Joined: 22 Jun 2011
Posts: 208
|
| Posted: Sun Jul 22, 2012 11:26 pm Post subject: |
|
|
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 |
|
|
patel
Power User
Joined: 14 Apr 2012
Posts: 54
Location: Italy
|
| Posted: Sun Jul 22, 2012 11:36 pm Post subject: |
|
|
| 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 |
|
|
|
