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.

I don't understand MsgBox rowIndex(2, 2). Why do you want the cell one row down and in column B from the current rowIndex? — user4039065
– user4039065, Commented Apr 22, 2018 at 1:20
@Jeeped I am currently trying to understand what is actually rowIndex gets by writing bunch of stuff. That's just wrong. My problem is not knowing how to access the cells on that row with for each loop. — Ahmet Taha Aymeir
– Ahmet Taha Aymeir, Commented Apr 22, 2018 at 1:22
For filtercount = 1 To 1? Also, (firstpass) should be just filterpass — AJD
– AJD, Commented Apr 22, 2018 at 5:19

user4039065 · Accepted Answer · 2018-04-22 01:33:08Z

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

answered Apr 22, 2018 at 1:33

user4039065

Sign up to request clarification or add additional context in comments.

4 Comments

Ahmet Taha Aymeir Over a year ago

I think now I understand. The use of the with confused me. Thank you. I hope I didn't understand the difference between rowIndex(1,2) and rowIndex.Cells(1,2).

user4039065 Over a year ago

There's no difference. The former is shorthand for the latter by omitting .cells which is the default property.

Ahmet Taha Aymeir Over a year ago

It worked perfectly. Trying to code for scheduling optimization. I am new to the VBA syntax. Thank you so much for your help.

user4039065 Over a year ago

btw, the Worksheets("Tripdata").Range("A1").AutoFilter could just be .Range("A1").AutoFilter since it is inside the With ... End With block as well.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related