[Solved] Loop thru Cell Range Syntax

The Application Programming Interface and the OASIS Open Document Format

[Solved] Loop thru Cell Range Syntax

Postby Tobin » Thu Mar 05, 2009 2:18 am

Howdy,
Looking for help with syntax OR I'm open to any suggestions.
My goal is to:
1) loop thru a number of consecutive cell ranges to find a range that contains the matching text
2) use the found cell range to indicate which range matched

Attached is my "ForNextLoopExample.ods" I'm using to try to figure this out. I tried to write it in a way that would make it easy for someone else to figure out what I'm trying to do.

Attached text.
ForNextLoopExample.zip
(9.99 KiB) Downloaded 889 times


Can anyone help, Please?

Thanks
Last edited by Tobin on Wed Mar 11, 2009 3:41 am, edited 1 time in total.
Tobin Sparks
OOo 3.0, XP
Houston, Texas USA
OOo 3.0.X on Ms Windows XP
Tobin
 
Posts: 35
Joined: Sun Feb 08, 2009 3:31 am
Location: Houston, Texas USA

Re: Loop thru Cell Range Syntax

Postby FJCC » Thu Mar 05, 2009 5:41 am

Here is some code that takes advantage of the built in search function. It searches for the string "54321" in the ranges C3:E3 through C7:E7 of the leftmost sheet. This code will only find the first occrence of the string within each range, but I think that meets your needs. It prints the row number as identified in the cell name. That is, it increments the row index by one before printing it.

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
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7738
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Loop thru Cell Range Syntax

Postby Tobin » Thu Mar 05, 2009 8:36 pm

Howdy,

FJCC Thank you very much for your reply. This is very interesting code. I was not aware of the built in search function. Unfortunately I DO need to match each cell in the default range with a corresponding cell in the tested (searched) range. All cell text in the entire default range must be equal to all cell text in the entire tested range.

Because of looking at <getCellRangeByPosition> function, I noticed the cell text I'm trying to compare/match is stored in DataArray(). I'm thinking I need to compare these, but can't seem to get the syntax right.

Attached is a screenshot of the DataArray() displaying the text and my latest work.

Do you agree?
Can you help?

Thanks
Attachments
ForNextLoopExample.zip
(26.98 KiB) Downloaded 474 times
Tobin Sparks
OOo 3.0, XP
Houston, Texas USA
OOo 3.0.X on Ms Windows XP
Tobin
 
Posts: 35
Joined: Sun Feb 08, 2009 3:31 am
Location: Houston, Texas USA

Re: Loop thru Cell Range Syntax

Postby FJCC » Thu Mar 05, 2009 9:42 pm

Working with the data array would work, but I think it can be done more simply. I can't work on this right now and may not be able to until tomorrow morning. I'd like to understand your goal. Do you want to look for each cell string in the default range in each of the test ranges, but each string will appear only once in a given range?
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7738
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Loop thru Cell Range Syntax

Postby Tobin » Thu Mar 05, 2009 10:41 pm

Thanks FJCC for your effort on this. This macro is an experiment and is one function in a larger macro. When this macro is figured out (I'm feeling more confident it will be) it will be added to a larger macro.
Concerning the Calc Sheet (ForNextLoopExample)
Description:
The (Default) first cell range [C3:E3] at the top of the group represents a range to be built by the user.
The (Tested) following cell ranges [C4:E4 & C5:E5 & C6:E6] represent possible matches to the top range. The Tested ranges will be added to the coded for the search.
The Goal of this macro is to find the (Tested) range that completely matches to the top (Default) and report which (Tested) range completely matched.

In the Example Calc Sheet (ForNextLoopExample) the last tested cell range is equal to the default range. I want to detect that and report it.

I hope that helps instead of making it more confusing ;)

<Working with the data array would work> I'm still trying to figure it out this way, but, can"t seem to get the syntax correct.

Thanks again for your effort
Tobin Sparks
OOo 3.0, XP
Houston, Texas USA
OOo 3.0.X on Ms Windows XP
Tobin
 
Posts: 35
Joined: Sun Feb 08, 2009 3:31 am
Location: Houston, Texas USA

Re: Loop thru Cell Range Syntax

Postby Tobin » Fri Mar 06, 2009 3:29 am

Howdy,
Well - It took me a while, but -

Sub GetData()

Dim C3_E3, CellArray, cell1, cell2, cell3
'vForms = ThisComponent.CurrentController.ActiveSheet.DrawPage.Forms
'Form = vForms.getByName("Standard")
Sheet = ThisComponent.Sheets(0)

C3_E3 = Sheet.getCellRangeByPosition(2, 3, 5, 3) ' get default cell range

CellArray = C3_E3.DataArray(0)
cell1 = CellArray(0)
cell2 = CellArray(1)
cell3 = CellArray(2)

MsgBox( "Range Text = " + cell1 + cell2 + cell3, 64, "Get DataArray"

End Sub

Now I'm so exhausted It'll take a while to figure out what to do with it :D .

I'm still open to any other suggestions.

Thanks
Tobin Sparks
OOo 3.0, XP
Houston, Texas USA
OOo 3.0.X on Ms Windows XP
Tobin
 
Posts: 35
Joined: Sun Feb 08, 2009 3:31 am
Location: Houston, Texas USA

Re: Loop thru Cell Range Syntax

Postby FJCC » Fri Mar 06, 2009 5:25 am

It's great to see you work that out. Those data arrays are not intuitive, at least for non-programmers like me. While you were posting that, I was working on the following. It finds only the first occurrence of each string within the cell range being tested. If all three strings in the default range are found within the test range, it prints the absolute name of the test range. It has to be edited for cell ranges that are not 3 columns long. I think it is clear how to do that, but let me know if it needs more explanation.
Code: Select all   Expand viewCollapse view
Sub SearchMatch
Dim Doc, Sheet, Descript, Cell, DefaultRange, CellRange as object
Dim i, Col as integer
Dim Flag as Boolean
Doc = ThisComponent
Sheet = Doc.Sheets(0)
DefaultRange = Sheet.getCellRangeByPosition(2,2,4,2)
For i = 3 to 5
CellRange = Sheet.getCellRangeByPosition(2,i,4,i)
Descript = CellRange.createSearchDescriptor()
Col = 0
Flag = True
Do While Flag AND Col <= 2
   Descript.SearchString = DefaultRange.getCellByPosition(Col,0).String   'Gets the desired Cell.String. Within DefaultRange the cells are indexed from 0,0
   Descript.SearchWords = True 'If true, the search will match only complete words
   Cell = CellRange.findFirst(Descript)
   If IsNull(Cell) then
      Flag = False      'Flag gets set to False if there is no match.
   End if
   If Flag and Col = 2 then    'If Flag is still true when Col = 2 then all three strings have matched.
      Print "Match at", CellRange.AbsoluteName
   End If
   Col = Col + 1
loop
next i
End Sub
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7738
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

[Solved] Re: Loop thru Cell Range Syntax

Postby Tobin » Sat Mar 07, 2009 6:41 pm

Howdy,
Sorry it took so long to get back. I finally had a chance to examine the search code you gave me. I had to start to rewrite it (dumb it down) until I finally understood. Very nice. I've attached my latest example work <CompareCellRangeExample>.ods. I noticed the way the code searches for a match in the first column cell by cell. Once the code finds a match in the first column, the imbeded "Do While" loops and increments to the next column to detect a match there or not. I'm going to use your code in my macro for a couple of reasons. The most important one is how easy it is to adapt to different range sizes and the number of ranges to search.

Thank you very much, you've been very helpful :D
Attachments
CompareCellRangeExample.zip
(11.18 KiB) Downloaded 761 times
Tobin Sparks
OOo 3.0, XP
Houston, Texas USA
OOo 3.0.X on Ms Windows XP
Tobin
 
Posts: 35
Joined: Sun Feb 08, 2009 3:31 am
Location: Houston, Texas USA

[Solved] Loop thru Cell Range Syntax

Postby Tobin » Wed Mar 11, 2009 3:40 am

OOPS - I thought I had already put the [Solved] in the subject box.

Thanks Again
Tobin Sparks
OOo 3.0, XP
Houston, Texas USA
OOo 3.0.X on Ms Windows XP
Tobin
 
Posts: 35
Joined: Sun Feb 08, 2009 3:31 am
Location: Houston, Texas USA


Return to UNO API and ODF

Who is online

Users browsing this forum: No registered users and 0 guests