VBA Array Read Row numbers by looping

[ThomasLafferty]

How do I use this code snipet to read the row numbers which this return into an array? Right now, it just puts them into the debug window.

[COLOR=green]'Get row number of each booking[/color]
For MoveCell = 1 To Target.Rows.Count
    Cells(MoveCell, 1).Select
    Set Target = Selection
    If Target.Value = "MOVE:" Then
        IntBookingRowNum = Target.Row
    [!]'Would like to read into an array here[/!]
    Debug.Print IntBookingRowNum
    End If
Next MoveCell

The goal is to locate a cell in column A of Sheets(1) which contains the text "MOVE:".


Here's a sample of the output from the debug window:

9 
34 
57 
79 
101 
124

The 9 represents that the text "MOVE:" was found in the 9th row, and the next occurence is in row 34. I would like to loop through rows 9:33 (actually A9:J33) and move values to sheet 2 data based on conditions in the A column. I will be using a select case structure to test for 10 different conditions like this:

Select Case ActiveCell.Value
     Case "Booking"
          Cells(Target.Row, 2).Select
          [COLOR=green]'read value into some variable
          'set appropriate value in second sheet = variable[/color]
     Case IsNumber = True 'probably not right syntax
          Cells(Target.Row,9)
          [COLOR=green]'do like above[/color]
     Case blah blah blah etc.

End Select

Here's the catch: I need the array (I think) so that I know what block of cells to analyze.

The first time through, I will be reading from A9:J33 (one less than the row in which next "MOVE:" occurs). The next iteration needs to read from A34:J56 etc. I would prefer to use the cells method since it lets me program flexibly.

Input? Ideas? Am I crazy (probably)?

Tom


Live once die twice; live twice die once.

 

[firefytr]

Populate your array like so ..

    Dim i As Long, arrRow() As Long, cnt As Long
    ReDim arrRow(0)
    For i = 1 To Target.Rows.Count
        If Cells(i, 1).Value = "MOVE:" Then
            ReDim Preserve arrRow(0 To cnt)
            arrRow(cnt) = i
            cnt = cnt + 1
        End If
    Next i

I have one question though, why not use AutoFilter then loop through the filtered cells??

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication.
- Leonardo da Vinci

 

[ThomasLafferty]

Hi Zack! I thought about using autofiltering, but the data I have been sent is a mess! The data comes from another company who is pulling it from some proprietary database into lotus. A clerk then gets her hands on it and retypes the data in Excel, but does not apparently know anything about flatfile db structure. In order for it to make any sense, she should have field names at the tops of the data columns and row identifiers to the left of each record. She put field names down the first column, and then only elected to put the field name in if it contained data. Also, she doesn't use a consistent order or spelling for her field names except for the field called MOVE:, so I will have to use pattern matching and maybe even a pseudo spell check.

Your code will, I'm sure, give me what I need, but here's a dumb question:

How do I refer to the values in the array?

My plan is use the total occurrences of MOVE as total number of loops to make. Looking at what you gave me:

    Dim i As Long, arrRow() As Long, cnt As Long
    ReDim arrRow(0)
    For i = 1 To Target.Rows.Count
        If Cells(i, 1).Value = "MOVE:" Then
            ReDim Preserve arrRow(0 To cnt)
            arrRow(cnt) = i
            cnt = cnt + 1    [!]'this is total array items/MOVEs?[/!]
        End If
    Next i

My guess is that I need two loops:

For something = 1 to totalmoves + 1 'assume first array index is 0?
     Do while Target.Row< arrRow(something)
          'examine cells and do stuff
     Loop
next something

How do I use this? I know, I know -- I'm wooden-headed.


Tom

Live once die twice; live twice die once.

 

[firefytr]

Hi Tom, no, definitely not wooden-headed at all.

If you're wanting to check those rows of other cells, you could maybe use this after you set your array up...

For i = lbound(arrRow) to ubound(arrRow)
    Msgbox Cells(arrRow(i), "A").Value
    '.. or whatever you wanted to do here
Next i
[/code[


.. although I'm not sure why you'd need the array if you're already looping through each cell to start with to [i]set[/i] the array.  See what I'm saying?  It's slightly redundant.  If that works for you, just use that first loop and keep everything inside that iteration.

Maybe ..

[code]    Dim i As Long, arrRow() As Long, cnt As Long
    ReDim arrRow(0)
    For i = 1 To Target.Rows.Count
        If Cells(i, 1).Value = "MOVE:" Then
            'Do your work here..
        End If
    Next i

As for what the cnt variable means after the array is set, it's actually one above the total amount of items in the array.

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication.
- Leonardo da Vinci

 

[vladk]

Hi,

In case if you will be able to use filtering, this is some code that I came up with:

Private Sub CommandButton1_Click()
Dim objFind As Range
Dim lngLastRow As Long
Dim strFirstAddress As String
Dim lngMyMoveRows() As Long
Dim i As Long
Dim MyAutoFilter As AutoFilter
Dim lngUBound As Long

lngLastRow = ThisWorkbook.ActiveSheet.Range("A65536").End(xlUp).Row
Range("A1:A" & CStr(lngLastRow)).AutoFilter Field:=1, Criteria1:="MOVE:"
Set MyAutoFilter = ActiveSheet.AutoFilter
lngUBound = MyAutoFilter.Range.SpecialCells(xlCellTypeVisible).Count - 2 'assume one-row header and zero-based array
ReDim lngMyMoveRows(lngUBound)

With ThisWorkbook.ActiveSheet.Range("A1:A" & CStr(lngLastRow))
Set objFind = .Find("MOVE:", LookIn:=xlValues, LookAt:=xlWhole)

If Not objFind Is Nothing Then
strFirstAddress = objFind.Address
Do
lngMyMoveRows(i) = objFind.Row
i = i + 1
Set objFind = .FindNext(objFind)
Loop While Not objFind Is Nothing And objFind.Address <> strFirstAddress
End If
End With

Range("A1:A" & CStr(lngLastRow)).AutoFilter

End Sub

 

[ThomasLafferty]

Thanks! I will play with it and will let you know what I figure out. As I mentioned, the data is a mess.

Tom

Live once die twice; live twice die once.

 

[ThomasLafferty]

Hi Zack! Don't mean to be a pest, but could you comment each line of the below and let me know what's happening? I would be grateful...

    Dim i As Long, arrRow() As Long, cnt As Long
    ReDim arrRow(0)
    For i = 1 To Target.Rows.Count
        If Cells(i, 1).Value = "MOVE:" Then
            ReDim Preserve arrRow(0 To cnt)
            arrRow(cnt) = i
            cnt = cnt + i
        End If
    Next i

Live once die twice; live twice die once.

 

[firefytr]

No problem Tom,

    'Dimension variables..
    Dim i As Long, arrRow() As Long, cnt As Long

    'Set the array to a zero-based array
    ReDim arrRow(0)

    'Begin our iteration through the desired rows
    For i = 1 To Target.Rows.Count

        'Check the value of the cell for our condition
        If Cells(i, 1).Value = "MOVE:" Then
            
            'If the condition meets true...

            'Re-dimension the array, preserving the values, to the total amount of our count variable
            ReDim Preserve arrRow(0 To cnt)
            
            'Set our array variable
            arrRow(cnt) = i
            
            'Increase our array count by one.  Can't use i as it would leave blanks in our array
            cnt = cnt + i
        
        End If
    
    Next i

Hope that helps a little bit Tom. What's great about the autofilter method I mentioned (and vladk posted) is that we can reduce looping through everything and testing for a criterion by only looping through the visible cells which are filtered by the condition itself. I understand how your data structure may not be setup for this though.

HTH

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication.
- Leonardo da Vinci

 

[ThomasLafferty]

Got it! Thanks for the clear explanation. Ever notice that Microsoft help is not always helpful? Your post, however, makes sense.

Thanks again,

Tom

Live once die twice; live twice die once.