VBA Array for Excel

[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.

'Get row number of each booking
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
          'read value into some variable
          'set appropriate value in second sheet = variable
     Case IsNumber = True
          Cells(Target.Row,9)
          'do like above
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.

 

[xlbo]

pleas epost in the VBA forum (Forum707)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question