How to write if function for more than one row on website table HTML

Question

DataGridReservations is a table. I am running a search by row.

My VBA works for one row. Is Is there a way to loop this VBA code to search each row in the table? Maybe by increasing the Item(2) in xobj1 = .... by 1 for each loop?

Dim xobj1 As Object
Set xobj1 = IE.Document.getElementById("DataGridReservations").getElementsByTagName("tr").Item(2)
Set xobj1 = xobj1.getElementsByTagName("td")
    If Range("B1").Text = xobj1.Item(9).innertext Then
    Range("H" & (ActiveCell.Row)) = xobj1.Item(3).innertext
    Else
    Range("H" & (ActiveCell.Row)) = "0"
    End If

REWRITE AFTER COMMENTS

I'm now getting an object does not support property or method

Dim xobj1 As Object
Dim xobj2 As Object
Dim colRows As Object
Set colRows = IE.Document.getElementById("DataGridReservations").getElementsByTagName("tr")
    For i = 0 To colRows.Length - 1
        Set xobj1 = colRows.Item(i)
        Set xobj2 = colRows.getElementsByTagName("td")
        If Range("B1").Text = xobj2.Item(9).innertext Then
        Range("H" & (ActiveCell.Row)) = xobj2.Item(3).innertext
        Else
        Range("H" & (ActiveCell.Row)) = "0"
        End If
    Next

You can loop through the collection returned by the call to `getElementsByTagName("tr")` (ignoring the first instance). Eg. see: http://stackoverflow.com/questions/15555513/get-all-td-from-tr-using-vba-code — Tim Williams, Nov 27 '13 at 05:50
Right, I was just thinking maybe there was a way to write the 2 as a formula like x+1 where x is the active item number. and just loop through the list until I ran out of tr elements in the table. (1.e the first loop would set item(2), the 2nd loop would set item(3), etc.) — TPJ87, Nov 27 '13 at 06:59
To reorganize, I also wrote this question http://stackoverflow.com/q/20255214/3023445 — TPJ87, Nov 27 '13 at 23:37

Michael · Accepted Answer · 2013-11-27T21:20:59.837

1

If you want to loop through the rows with a counter, you can do it like this:

Set colRows = IE.Document.getElementById("DataGridReservations").getElementsByTagName("tr")
For i = 0 to colRows.Length -1
    Set xobj1 = colRows.Item(i)
    'Your code
Next

The index starts at '0'. Therefore the counter runs to '.Length -1'

Edited, following revised question:

Dim xobj1 As Object
Dim xobj2 As Object
Dim colRows As Object

Set colRows = IE.Document.getElementById("DataGridReservations").getElementsByTagName("tr")
    For i = 0 To colRows.Length - 1
        Set xobj1 = colRows.Item(i)
        Set xobj2 = xobj1.getElementsByTagName("td")
        If Range("B1").Text = xobj2.Item(9).innertext Then
        Range("H" & (ActiveCell.Row)) = xobj2.Item(3).innertext
        Else
        Range("H" & (ActiveCell.Row)) = "0"
        End If
    Next

edited Nov 27 '13 at 21:20

answered Nov 27 '13 at 09:03

Michael

538
5
8

1

inside of your for...next could i Set xobj1 = colRows.Item(i) ? – TPJ87 Nov 27 '13 at 18:48
Yes, of course. Or _colRows(i)_. I should have written that :) – Michael Nov 27 '13 at 18:55
hmmm. object does not support property or method. see question for my rewrite – TPJ87 Nov 27 '13 at 19:33
1

Try: Set xobj2 = xobj1.getElementsByTagName("td") – Michael Nov 27 '13 at 19:37
wait, theres a way to check which line the errors in? NVM figured out how to do that... that's cool! the errors in set colRows = ..... – TPJ87 Nov 27 '13 at 19:50
would it be the '.length' ? since my rows aren't actually rows but rather tags.or is '.length' how you call the number of tr tags for the collection of elements retrieved with 'getelementsbytagname()' – TPJ87 Nov 27 '13 at 20:29
1

let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/42065/discussion-between-jazzyjoop-and-tpj87) – Michael Nov 27 '13 at 20:32
Hey @JazzyJoop. sorry about that. I found the error its in the line with If Range("B1").Text = xobj2.Item(9).innertext Then – TPJ87 Nov 27 '13 at 21:56
AHHHHHH!!!!! FIGURED IT OUT! you were right all along. the first tr had a column span set in it so technically there was only one item in the first tr but my code had it looking for item(9) which didnt exist. i changed i = 0 to i = 2 since the 3rd row starts the data... THankyou!!! – TPJ87 Nov 28 '13 at 01:58

How to write if function for more than one row on website table HTML

1 Answers1