[Solved] Loop thru Cell Range Syntax

Tobin · Post by **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 1091 times

Can anyone help, Please?

Thanks

Post by **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

Tobin · Post by **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

Post by **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?

Tobin · Post by **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 · Post by **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

.

I'm still open to any other suggestions.

Thanks

Post by **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

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

Tobin · Post by **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

Tobin · Post by **Tobin** » Wed Mar 11, 2009 3:40 am

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

Thanks Again