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.

 

[Lil2606]

Posted on ExcelForum as well Link:https://www.excelforum.com/excel-pr...st-row-to-reference-in-vba-2.html#post5097618

 

[James006]

Hello,

Understand you can find the Range ROFR ...

What is it exactly that you want to Select ... once you have found this empty range ...???

 

[Lil2606]

Another cell in this same row, which's column is "Something Else" in this example.. In my real code I don't select it, in fact my active cell has a very important job so I can't change it, this was just for the sake of the example.

 

[James006]

OK ...

To get to another cell in the same row ... most probably you will need to use ROFR.Offset (0, number of columns )

number of columns is a positive or negative number to reach your Target cell ...

Hope this will help

 

[Lil2606]

Hahahaha forest by the trees isn't it? THANK YOU 2 days I was sitting on that!

 

[James006]

Everything is ... Relative ... ha ha ha

Glad you could fix your problem ....

Thanks for your Thanks ...

 

[Lil2606]

Ohh I was so happy about that, but I forgot, I have the same issue in another part of my code..

I have this:

Sheet4.ListObjects(ActiveCell.ListObject.Name).ListRows.Add (ActiveCell.Row)

which again.. returns the runtime error since I don't have 99 rows

 

[James006]

What exactly this instruction is supposed to produce ...???

 

[Lil2606]

It adds a row to the listobject which has the active cell in it, and depending on the last bracket it adds it to where I want it, so..

Lets say my active cell is A99 which is in my 3rd table Table3, this code translates to:

[COLOR=#333333]Sheet4.ListObjects(Table3).ListRows.Add (99)[/COLOR]

so the 99 is not right.. it should be 3.. cause its my 3rd listrow in the table..

if it would be

[COLOR=#333333]Sheet4.ListObjects(Table3).ListRows.Add (3)[/COLOR]

then this row would be an empty row in the "3rd position" so this would be my new 3rd row in the table.