×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Adding a Horizontal Range to an Array Varaible in Excel 7.1

Adding a Horizontal Range to an Array Varaible in Excel 7.1

Adding a Horizontal Range to an Array Varaible in Excel 7.1

(OP)
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

[code]

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]

RE: Adding a Horizontal Range to an Array Varaible in Excel 7.1

CurrFormat Redim is in comment:
Range("A1", Range("A1").End(xlToRight)).Select
NumCols = Selection.Columns.Count
'ReDim Preserve CurrFormat(NumCols)

Most of your code can work without selecting, for instance Set rgFound = Range("A1").End(xlToRight).Find(CurrFileName). The code will be shorter, easier to debug and faster - no screen action.
Test if found ranges exist (If Not rgFound Is Nothing Then).

combo

RE: Adding a Horizontal Range to an Array Varaible in Excel 7.1

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


---- Andy

There is a great need for a sarcasm font.

RE: Adding a Horizontal Range to an Array Varaible in Excel 7.1

(OP)
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.

RE: Adding a Horizontal Range to an Array Varaible in Excel 7.1

"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:
ReDim Preserve CorrectFormat(Numrows)
...
CorrectFormat() = Selection.Value
...

but then you try to do this:
CorrectFormat(I, 1).Value


---- Andy

There is a great need for a sarcasm font.

RE: Adding a Horizontal Range to an Array Varaible in Excel 7.1

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

combo

RE: Adding a Horizontal Range to an Array Varaible in Excel 7.1

(OP)
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

RE: Adding a Horizontal Range to an Array Varaible in Excel 7.1

(OP)
After I added I = I + 1 before Next MyCell

RE: Adding a Horizontal Range to an Array Varaible in Excel 7.1

This would be a lot easier to see:

CODE

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)
    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 sad

---- Andy

There is a great need for a sarcasm font.

RE: Adding a Horizontal Range to an Array Varaible in Excel 7.1

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 For Each...

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close