Loop with the row range - How can I get a specific cell?

Question

With Worksheets("Tripdata")
    For filtercount = 1 To 1
        Worksheets("Tripdata").Range("A1").AutoFilter Field:=4, Criteria1:=stationidentify(filtercount, 1)
        For Each rowIndex In .UsedRange.Rows
            If (rowIndex.Hidden) Then
            'nothing
            Else
                If (firstpass) Then
                    firstpass = False
                Else
                    MsgBox rowIndex(2, 2)
                    test = test + 1
                    Dim currentstat As Double, finalstat As Double
                    currentstat = Worksheets("Tripdata").Cells(rowIndex, 3).Value
                    finalstat = Worksheets("Tripdata").Cells(rowIndex, 7).Value

The tripdata visual look - I wanna somehow get the names on the fourth and seventh cells

Hi, I am trying to get a specific cell with the loop above. I thought the rowIndex was the rownumber on the .Cells(row,column); but it seem like it is not. I am trying to loop through data with filters.
Can anyone help me with this ?
Thanks in advance.

Answer 1

You are dealing with a single row so any Cells(row, column) reference should have 1 if dealing directly with rowIndex or rowIndex.row if referencing a cell on the worksheet.

'put these outside the loop
Dim currentstat As Double, finalstat As Double
For Each rowIndex In .UsedRange.Rows
...
Else
    MsgBox rowIndex(1, 2)  'column B value within rowIndex
    MsgBox rowIndex.cells(1, 2)  'column B value within rowIndex
    test = test + 1
    currentstat = Worksheets("Tripdata").Cells(rowIndex.row, 3).Value
    finalstat = Worksheets("Tripdata").Cells(rowIndex.row, 7).Value
    'you're inside a With ... End With block so these are the same
    currentstat = .Cells(rowIndex.row, 3).Value  'column C value within rowIndex
    finalstat = .Cells(rowIndex.row, 7).Value  'column G value within rowIndex
    debug.print currentstat
    debug.print finalstat