Adding a Horizontal Range to an Array Varaible in Excel 7.1

[BeckyMc]

I'm hoping I don't do this a lot. I'm in a small business again and the only vba person right now.

I can get a vertical array to populate and loop very nicely. Then there's an array from a horizontal range that isn't doing the same thing very well. Specifically I can not read from it in order to compare two arrays. I have tried Redim and Redim preserve but it doesn't seem to be helping much. Basically it looks like I'm loading the array variable but I can't access anything in it for comparison to the second array. I've also tried dimming a certain size and then redimming but that didn't seem to be any different.

The line that blows up right now is below. I can message box cell.value and see something there but if I try to loop to the value with I or anything else I get an error

Right now the error is subscript out of range but I also get object not available which I think are symptoms of the same issue.

'This is the line that's blowing up right now
CurrFormat(I) = Cell.Value

Sub CheckFileFormat()

Dim CorrectFormat() As Variant
Dim CurrFormat() As Variant
Dim CurrFileName As String
Dim Numrows As Integer
Dim NumCols As Integer
Dim I As Long
Dim x As Long
Dim Cell As Range

Dim rgFound As Range
Dir ("C:\Users\BMcEllistrem\Desktop\Imports\")

CurrFileName = Range("A2").Value

'If Range("F2").Value <> "" Then

    ActiveWorkbook.Sheets("ColumnHeadings").Activate
    Range("A1").End(xlToRight).Select
    
    Set rgFound = Selection.Find(CurrFileName)
    Range(rgFound, rgFound.End(xlDown)).Select
    Numrows = Selection.Rows.Count
    ReDim Preserve CorrectFormat(Numrows)
    
    CorrectFormat() = Selection.Value
    
    Workbooks.Open ("C:\Users\BMcEllistrem\Desktop\Imports\" & CurrFileName)
    Range("A1", Range("A1").End(xlToRight)).Select
    NumCols = Selection.Columns.Count
    'ReDim Preserve CurrFormat(NumCols)
    I = 1
    For Each Cell In Selection
    MsgBox Cell.Value
        'This is the line that's blowing up right now
        CurrFormat(I) = Cell.Value
        I = I + 1
        Debug.Print Cell.Value

    Next Cell
'This does well.  I can print and get rows and column counts.
    Debug.Print "Correct format is " & LBound(CorrectFormat) & " " & UBound(CorrectFormat);
    Debug.Print "Current format is " & LBound(CurrFormat) & " " & UBound(CurrFormat);
 
'This does not work well.  I can get one loop to work but not both so I can't get to compare the two columns.
'The currformat is in a horizontal line the correct format array is in a vertical line.
   For x = LBound(CurrFormat) To UBound(CurrFormat)
    Debug.Print CurrFormat(x)
    For I = LBound(CorrectFormat) To UBound(CorrectFormat)
        Debug.Print CorrectFormat(I)
   
'    If CorrectFormat(I, 1).Value = CurrFormat(1, I).Value Then
'     Debug.Print "No problems"
'    ElseIf CorrectFormat(I, 1) <> CurrFormat(1, I) Then
'        Debug.Print "Correct format is " & CorrectFormat(I).Value & " Current format is " & CurrFormat(I).Value
'    End If

    Next I
    Next x

    MsgBox "Done checking " & CurrFileName

End Sub
[code]

 

[combo]

CurrFormat Redim is in comment:
[tt]Range("A1", Range("A1").End(xlToRight)).Select
NumCols = Selection.Columns.Count
'ReDim Preserve CurrFormat(NumCols)[/tt]
Most of your code can work without selecting, for instance [tt]Set rgFound = Range("A1").End(xlToRight).Find(CurrFileName)[/tt]. The code will be shorter, easier to debug and faster - no screen action.
Test if found ranges exist ([tt]If Not rgFound Is Nothing Then[/tt]).


combo

 

[Andrzejek]

And also clicking Preview button before "Submit Post" would go a long way...


---- Andy

There is a great need for a sarcasm font.

 

[BeckyMc]

I was in a hurry. Great tips but I was actually concerned about being able to access the data in the horizontal array which is giving me errors.

I'm pretty sure the range exists because I can msgbox cell.value and get the correct value.
I guess I could use a range variable as an extra test to see if the range exists but I'm pretty sure it does.

I just can not loop it without a subscript out of range error.

 

[Andrzejek]

I can msgbox cell.value and get the correct value" that's fine, but do those values exist in the arrays?

Are the CurrFormat and CorrectFormat arrays one dimensional arrays? They look to me, since you do:[tt]
ReDim Preserve CorrectFormat(Numrows)
...
CorrectFormat() = Selection.Value
...
[/tt]
but then you try to do this:[tt]
CorrectFormat([red]I, 1[/red]).Value[/tt]


---- Andy

There is a great need for a sarcasm font.

 

[combo]

Selected range exists, but [tt]CurrFormat(I) = Cell.Value[/tt] raises error, after [tt]Dim CurrFormat() As Variant[/tt] the line [tt]'ReDim Preserve CurrFormat(NumCols)[/tt] is green, at least in the code you posted. You try to write in non-initialised array.

combo

 

[BeckyMc]

This basic code is working for now so I'll use this as the fix for my function. Thanks for the help!

Sub TestHorizontal()

Dim MyArray() As Variant
Dim MyRange As Range
Dim MyCell As Range
Dim I As Integer

I = 1
Set MyRange = Range("A1", Range("A1").End(xlToRight))
ReDim MyArray(MyRange.Columns.Count)

For Each MyCell In MyRange

'Debug.Print MyRange.Columns.Count ' This prints 10
'Debug.Print MyCell.Value 'This prints The
MyArray(I) = MyCell.Value 'This generates subscript out of range
Debug.Print MyArray(I)
Next MyCell

End Sub

 

[BeckyMc]

After I added I = I + 1 before Next MyCell

 

[Andrzejek]

This would be a lot easier to see:

Sub TestHorizontal()

Dim MyArray() As Variant
Dim MyRange As Range
Dim MyCell As Range
Dim I As Integer

I = 1
Set MyRange = Range("A1", Range("A1").End(xlToRight))
ReDim MyArray(MyRange.Columns.Count)

For Each MyCell In MyRange
[green]
   'Debug.Print MyRange.Columns.Count ' This prints 10
   'Debug.Print MyCell.Value 'This prints The[/green]
    MyArray(I) = MyCell.Value [green]'This generates subscript out of range[/green]
    Debug.Print MyArray(I)
    I = I + 1
Next MyCell

 End Sub

Your MyArray() starts with 0, so you may have not enough elements in your array

EDIT: You do have enough elements, but since you start populating your array at element (1), there is no place to put your last value because you run out of 'room' of you array. Element (0) is empty :-(

---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

FYI, when you ReDim your array the Lower Bound defaults to 0 (ZERO). So what that means is that if you start your element index at 1 (ONE), you have an unused array element.

Used to be, in the olden days, like back in the 1960s, somebody from the big air-conditioned computer room, would slap your wrist for wasting user time-share resources, and you would need to go fix your program and punch a new program Hollerith card deck.

Now a-days, programers need not be so concerned most of the time.

Concerning arrays vs no arrays in Excel VBA, I hardly ever used arrays to traverse Excel Rows and Columns. I just used nested [tt]For Each...[/tt]

For Each r In SomeRowRange
   For Each c In SomeColumnRange
      Debug.Print r.Row, c.Column, intersect(r.EntireRow, c.EntireColumn).Value, Cells(r.Row, c.Column).Value
   Next c
Next r

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein