Array Run time error

[JBudd]

I would appreciate knowing what I am doing wrong - could someone please point me in the right direction?
I get a Run-time error 9 on the below code.
if I replace x variable to a fix number in the line Cells(col, cy + 7) = Mycycle(x) to Cells(col, cy + 7) = Mycycle(1) it works but the cycles dont change of coarse. In the end there will be 30 to 40 cycles from a dat file with only a blank cell between the cycles

if there is a better way to do this type of array please let me know.
Thank you for any help

John

Sub findcycle()
'
'

Dim Mycycle(8) As String


Mycycle(1) = "cycle1"
Mycycle(2) = "cycle2"
Mycycle(3) = "cycle3"
Mycycle(4) = "cycle4"
Mycycle(5) = "cycle5"
Mycycle(6) = "cycle6"
Mycycle(7) = "cycle7"
Mycycle(8) = "cycle8"
Dim mycount
Dim col
Dim row
Dim cy
Dim x
mycount = 0
cy = 13
x = 1

Columns("A:A").Select
mycount = Range("A65536").End(xlUp).row
Cells(15, 17).Select
'sorts data

For col = 18 To mycount
If Cells(col, cy) <= "" Then
Cells(col, cy + 7) = Mycycle(x)
End If
If Cells(col, cy) = "" Then
If Cells(col + 8, cy) = "" Then GoTo estop
End If
x = x + 1
Next col
estop:
Erase Mycycle() ' deletes the varible contents, free some memory
End Sub

 

[ukmikeb]

Hi

Welcome to the forum.

I managed to get that statement to work ok with the x BUT you are missing an "End If" in this section of code-

If Cells(col, cy) = "" Then
    If Cells(col + 8, cy) = "" Then
        GoTo estop
    End If
x = x + 1

hth

Mike

 

[pbornemeier]

You have dimensioned Mycycle(8)

Mycount is set by the row of the last cell in column A. If that row is greater than 25 (18+8-1) then when a blank is encountered in the Cells(col, cy) cell, you attempt to stoire a value Mycycle(9) which causes the error.

Please describe/show (use Excel Jeanie - see link in my sig) your data and explain what you are trying to do with it.

What is the row variable for?
Why are you using the col variable to increment the row in the Cells statements?
Why do you use <="" instead of =""?

 

[JBudd]

You were correct the error was from a blanks cell
thank you !

the code I submitting was just sample to figure out the error issue
there are 30-40 cycles at each cycle break there is a blank cell, for a total of 3500-4000 rows of data per column that varies due to the test, the only standard is the data starting at row 18.

What is the row variable for? there are 4 columns of data in the original dat file at every cycle break there are 1 blanks rows and 3 rows of repeated header information that not needed.

Why are you using the col variable to increment the row in the Cells statements? I don't see where I am doing that, cells(column,row) is the order for that command.

Why do you use <="" instead of =""?
It was a mistake on my part for the example but i think I was using the that command in the original macro to find and select the 4 rows that were not need so I could delete them and move the cells up and place a cycle marker at each place so later in the macro or at that point I can could do calculation on the data between one cycle to another.

If you know a more efficient way to load the array or another method I am open to suggestions.

below is example of the data remember the org there are 3500 to 4000 rows and cycle 2 could have a random number of data and same for all the cycles varying through the test.

<table border="0" cellpadding="0" cellspacing="0" width="320"><col style="width: 48pt;" span="5" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" height="20" width="64">ra</td> <td style="width: 48pt;" width="64">ra</td> <td style="width: 48pt;" width="64">ra</td> <td style="width: 48pt;" width="64">ra</td> <td style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">xcz</td> <td>xcz</td> <td>xcz</td> <td>xcz</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.207095</td> <td align="right">0.207095</td> <td align="right">0.207095</td> <td align="right">0.207095</td> <td>cycle1</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.224245</td> <td align="right">0.224245</td> <td align="right">0.224245</td> <td align="right">0.224245</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.208751</td> <td align="right">0.208751</td> <td align="right">0.208751</td> <td align="right">0.208751</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.251249</td> <td align="right">0.251249</td> <td align="right">0.251249</td> <td align="right">0.251249</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.169152</td> <td align="right">0.169152</td> <td align="right">0.169152</td> <td align="right">0.169152</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.262502</td> <td align="right">0.262502</td> <td align="right">0.262502</td> <td align="right">0.262502</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.206326</td> <td align="right">0.206326</td> <td align="right">0.206326</td> <td align="right">0.206326</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.24877</td> <td align="right">0.24877</td> <td align="right">0.24877</td> <td align="right">0.24877</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.228267</td> <td align="right">0.228267</td> <td align="right">0.228267</td> <td align="right">0.228267</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.265784</td> <td align="right">0.265784</td> <td align="right">0.265784</td> <td align="right">0.265784</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">ra</td> <td>ra</td> <td>ra</td> <td>ra</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">xcz</td> <td>xcz</td> <td>xcz</td> <td>xcz</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.220005</td> <td align="right">0.220005</td> <td align="right">0.220005</td> <td align="right">0.220005</td> <td>cycle2</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.224912</td> <td align="right">0.224912</td> <td align="right">0.224912</td> <td align="right">0.224912</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.236771</td> <td align="right">0.236771</td> <td align="right">0.236771</td> <td align="right">0.236771</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.234567</td> <td align="right">0.234567</td> <td align="right">0.234567</td> <td align="right">0.234567</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.199641</td> <td align="right">0.199641</td> <td align="right">0.199641</td> <td align="right">0.199641</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.199497</td> <td align="right">0.199497</td> <td align="right">0.199497</td> <td align="right">0.199497</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.173898</td> <td align="right">0.173898</td> <td align="right">0.173898</td> <td align="right">0.173898</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.156442</td> <td align="right">0.156442</td> <td align="right">0.156442</td> <td align="right">0.156442</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.135241</td> <td align="right">0.135241</td> <td align="right">0.135241</td> <td align="right">0.135241</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.125396</td> <td align="right">0.125396</td> <td align="right">0.125396</td> <td align="right">0.125396</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.070457</td> <td align="right">0.070457</td> <td align="right">0.070457</td> <td align="right">0.070457</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.076075</td> <td align="right">0.076075</td> <td align="right">0.076075</td> <td align="right">0.076075</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.102439</td> <td align="right">0.102439</td> <td align="right">0.102439</td> <td align="right">0.102439</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.109429</td> <td align="right">0.109429</td> <td align="right">0.109429</td> <td align="right">0.109429</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.078674</td> <td align="right">0.078674</td> <td align="right">0.078674</td> <td align="right">0.078674</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.047242</td> <td align="right">0.047242</td> <td align="right">0.047242</td> <td align="right">0.047242</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.117405</td> <td align="right">0.117405</td> <td align="right">0.117405</td> <td align="right">0.117405</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.172975</td> <td align="right">0.172975</td> <td align="right">0.172975</td> <td align="right">0.172975</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.160476</td> <td align="right">0.160476</td> <td align="right">0.160476</td> <td align="right">0.160476</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.202005</td> <td align="right">0.202005</td> <td align="right">0.202005</td> <td align="right">0.202005</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.150195</td> <td align="right">0.150195</td> <td align="right">0.150195</td> <td align="right">0.150195</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.257076</td> <td align="right">0.257076</td> <td align="right">0.257076</td> <td align="right">0.257076</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">ra</td> <td>ra</td> <td>ra</td> <td>ra</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">xcz</td> <td>xcz</td> <td>xcz</td> <td>xcz</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.220005</td> <td align="right">0.220005</td> <td align="right">0.220005</td> <td align="right">0.220005</td> <td>cycle3</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.334317</td> <td align="right">0.334317</td> <td align="right">0.334317</td> <td align="right">0.334317</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.314153</td> <td align="right">0.314153</td> <td align="right">0.314153</td> <td align="right">0.314153</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" align="right" height="20">0.318123</td> <td align="right">0.318123</td> <td align="right">0.318123</td> <td align="right">0.318123</td> <td>
</td> </tr> </tbody></table>

 

[pbornemeier]

This post discusses loading a 1D and 2D range into an array:
http://www.mrexcel.com/forum/showthread.php?t=432798

The format for the cells command is Cells(RowIndex,ColumnIndex)

This code will load each numeric block of data into a different array:

Sub LoadEachBlockOfNumericDataIntoAnArray()

    Dim aryAddresses As Variant
    Dim cycles As Variant
    Dim lx As Long
    
    Range("A1").SpecialCells(xlCellTypeConstants, xlNumbers).Select
    aryAddresses = Split(Selection.Address, ",")
    
    ReDim cycles(UBound(aryAddresses) + 1)
    
    For lx = LBound(aryAddresses) To UBound(aryAddresses)
        cycles(lx + 1) = Range(aryAddresses(lx)).Value
    Next
    
    Stop
    
    'You can examine the data loaded into the arrays by looking in the Locals window (View Locals)
    'Now do something with the data
    
End Sub