listobject row number

[NicolaasH]

Hi,

I would like to run through a column of a list (ListObject) in my Excel sheet to check the value which is in it, in order to find out what the highest number is in the list. How can I point to a certain cell in a list and read its value?

I now have something like this (unknown code marked by <<< >>>):

Function CheckMaxCellIndex(CheckWorksheet As String, CheckColumn As Integer, MaxCell As Integer) As Integer
Dim LastRowOfList As Integer, CheckRow As Integer

Set objListRowSearch = ThisWorkbook.Worksheets(CheckWorksheet).ListObjects(1)
LastRowOfList = objListRowSearch.ListRows.Count

'<<<
For CheckRow = 1 To LastRowOfList
    MaxCellCheck = objListRowSearch.Cells(CheckRow, CheckColumn)
'>>>
    If MaxCellCheck > MaxCell Then MaxCell = MaxCellCheck
Next CheckRow

CheckMaxCellIndex = MaxCell

End Function

Thanks for your help!

 

[WalkerEvans]

NicolaasH,

Wouldn't it be simpler to go the last cell in the columm and

Execute.Autosum(Max)

rather than creating a custom function?



----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[NicolaasH]

The thing is that I have to extract the maximum index from several lists in different worksheets. The lists vary in length, which changes all the time as well. I then use the value in a series of consecutive methods. Thus I'd rather keep it in code.
I also want to write a method that will need the same kind of code (reference to a row in the list), thus I was hoping to catch two flies at once when asking just this one question ;-)

 

[WalkerEvans]

What I gave you is code. My short answer presumed that you already knew how to find the last valid cell in a column (ie, the one containing actual data). If that were true all you would need to do would be to append my snippet of code.

However, it seems I presumed in error. Take a look at faq707-2115; if that takes care of your "find the last cell" problem you can then see if that plus my one-liner will get you a satisfactory answer.

As for as running this code against many different worksheets ... well, go ahead and run it. As long as you put the routine in a public file (ie, a .bas file) it can be run against any worksheet you can access. If, after you have checked out the FAQ you still can't get things to work, post back and we can take another run at this.



----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[NicolaasH]

Thanks Walker. I think I can get this to work on the little problem I presented. Although I'm still not sure of the syntax.
However in the other procedure I mentioned, I really need to pass through the list line by line. Of course I could do this with absolute references to the sheet, but I'd rather do it with relative references to the list (in case the position of the list changes).
For this I will need to define the first cell of the List. From there on its a simple For loop to the end of the List, which is easily found with LastRowOfList = objListRowSearch.ListRows.Count.

So then, how do I find the (absolute) position of the first cell in the List Object?
Thx. Nicolaas

 

[SkipVought]

Hi,

'this assumes that [b]ListObject[/b] is a range on a sheet
iMax = Application.Max([ListObject])
'this finds the offset withing the range
iOff = Application.Match(iMax, [ListObject], 0)
[code]


Skip,
[sub]
[glasses] When a diminutive clarvoyant had disappeared from detention, headlines read...
[b]Small Medium at Large[/b][tongue][/sub]

 

[NicolaasH]

Just like to let you know I solved it as followed:

Function CheckMaxCellIndex(objList As ListObject, NumberOfRows As Integer, CheckColumn As Integer, MaxCell As Integer) As Integer
Dim CheckRow As Integer

For CheckRow = 3 To NumberOfRows + 1
     MaxCellCheck = objList.Range(CheckRow, CheckColumn)
        If MaxCellCheck > MaxCell Then MaxCell = MaxCellCheck
Next CheckRow

CheckMaxCellIndex = MaxCell

End Function