Nrthugu

I want to produce a mini report of several items matching a key. In my loop I get the keys returned but can't fathom out how I can access the data that I need in the report that is held in other columns.

I put in some msgbox's to trap the data and an escape mechanism to get out of the loop. These I have commented out below as well as the data lines that don't work. "cbdata" is a workbook named range covering B5:T4019. The report is being compiled on a different sheet (activesheet). For some unknown reason on looping through without outputing any data "r" gets updated to some spurious numbers like 2421 (first loop) this appears to be linked somehow to the data in "cbdata". The first entry is actually in row 2388 so it doesn't really correlate to an indexed row in the range. However, I think first of all I need to find out what I can do to get the corresponding row returned for each of my passes. "ky" returns all the entries in columns(19) but I'm only interested in those that match "ledcdeyr" which in this instance is "2012017" that bit works returning all the entries matching in the loop.
Having got the key information agreeing, how might I relate this to the row number so that I can extract the other data from that row.

(cr is vbcrlf)(r should be the row number of the receiving report)

Any pointers would be greatly appreciated.

Code:

r = r + 1    ' row 38 when entering process



For Each ky In Range("cbdata").Columns(19).Cells



'ans = MsgBox(ky & cr & r, vbOKCancel)

'If ans = vbCancel Then Exit Sub



If ky = ledcdeyr Then



ans = MsgBox(ky & cr & r, vbOKCancel)

If ans = vbCancel Then Exit Sub



 Cells(r, 2) = Range("cbdata").Cells(ky, 1)

'Cells(r, 3) = Range("cbdata").Columns(2).Cells

'Cells(r, 4) = Range("cbdata").Columns(3).Cells

'Cells(r, 5) = Range("cbdata").Columns(4).Cells

'Cells(r, 6) = Range("cbdata").Columns(5).Cells



ans = MsgBox(r, vbOKCancel + vbQuestion, title)

If ans = vbCancel Then Exit Sub



End If



r = r + 1

Next     

I am not entirely sure I follow but the Range object during the loop is ky. The row for that cell be retrieved with .Row property

ky.Row

Somewhat random example with a conditional test:

Option Explicit

Public Sub Test()

    Dim ky As Range, counter As Long

    Dim loopRange As Range

    Set loopRange = ThisWorkbook.Worksheets("Sheet1").Range("cbdata").Columns(19)

    For Each ky In loopRange.Cells

        counter = counter + 1

        If ky = 1 Then

            Debug.Print ky.Row, counter

            Debug.Print loopRange(counter).Address

        End If

    Next

End Sub

I ran the undernoted slightly amended code on my project but it produced some spurious results.
I had already tried the ky.row but when it didn't give me the information, I just thought that it wasn't the answer.

Public Sub Test()

    Dim ky As Range, counter As Long

    Dim loopRange As Range

    'Set loopRange = ThisWorkbook.Worksheets("Sheet1").Range("cbdata").Columns(19)

    Set loopRange = Range("cbdata").Columns(19) ' workbook range name

    For Each ky In loopRange.Cells

        counter = counter + 1

            'ans = MsgBox(counter & vbCrLf & ky & vbCrLf & ky.Row, vbOKCancel)

            'If ans = vbCancel Then Exit Sub

        If ky = 2012017 Then

            Debug.Print ky.Row, counter

            Debug.Print loopRange(counter).Address

        End If

    Next

End Sub

The Debug results from running the above code for the first 4 records were:

 2388          2384 

$CNK$5:$CNK$4091

 2408          2404 

$COE$5:$COE$4091

 2444          2440 

$CPO$5:$CPO$4091

 2450          2446 

$CPU$5:$CPU$4091

The numbers on the left produced by ky.row are correct. The numbers on the right relate somehow to the counter which here should be 1, 2, 3, 4. This is the same situation that occurred with my "r" and hence didn't produce the information where I expected to see it. Which caused me to think that ky.row was not working. Also my range "cbdata" is B5:T4091 The rows are correct but the "CNK" etc - I don't know where that has come from.
I thought I'd just feed back to you where I'm at and your reply certainly made me look further, as I seemed to be going round in circles.
If you have any idea how the counter would be acting so spuriously then perhaps you could let me know. Having used your code on its own the that clears up any issue with there not being a problem with any other part of my code. Thanks again.