Relative number of list-row to reference in VBA?

[Lil2606]

Hi all,

I have 3 tables on a sheet and I'm trying to reference a ListRow.

I'm using .find to find an empty cell in the 3 tables and the row number - the ListRow number of that cell is what I would need.. but I get the actual row number and then Subscript out of range.. could someone help me with this please?

Here is the code:

[/COLOR]Sub try()

Dim i As Long
Dim ROFR As Range    'R_ow O_f F_ormula R_ange


For i = 1 To Sheet1.ListObjects.Count


Set ROFR = Sheet1.ListObjects(i).ListColumns("Something").DataBodyRange.Find(What:="", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)


If Not ROFR Is Nothing Then


'Here is the issue:


Sheet1.ListObjects(i).ListRows(ROFR.Row).Range(1, Sheet1.ListObjects(i).ListColumns("Something Else").Range.Column).Select


Exit Sub


End If


Next



End Sub[COLOR=#333333]

Its a very basic set-up. I have 3 tables on Sheet4 the first table is 52rows + Headers in the 16th row. The second table is 24rows and starts in row70 with headers, the third table is 13rows and starts in row96 with its headers..

I need row 99 because it has the cell that I needed to find (ROFR) is in the 99th row, but if I write Sheet4.ListObjects(3).ListRow(ROFR.Row) I will get subscript out of range because ROFR.Row = 99 and my third table doesn't have 99 rows. It has 13rows (listrows). So I need the row number relative to the table not relative to the whole excel workbook.

 

[James006]

Not sure to fully understand ... :wink:

If you need to Insert below your ActiveCell :

Selection.ListObject.ListRows.Add AlwaysInsert:= True

Hope this will help

 

[Lil2606]

Brings the same subscript out of range :/

 

[James006]

Brings the same subscript out of range :/

Are you sure you have selected your ActiveCell ... .smile:

 

[Lil2606]

ActiveCell.Select
Selection.ListObject.ListRows.Add AlwaysInsert = True

Returns Subscript out of range

 

[Lil2606]

Got it!!

Sheet4.ListObjects(ActiveCell.ListObject.Name).ListRows.Add (ActiveCell.Row - Sheet4.ListObjects(ActiveCell.ListObject.Name).HeaderRowRange.Row + 1)

 

[James006]

Glad you could fix your problem ...