RE: VBA: Working with arrays

[BIGTONE559]

RE: VBA: Working with arrays

Greetings,

i had an error pop up because i had a For next loop for an array but the array only had one value.

for example

Dim MyArray as Variant
Dim i as integer 'array counter

MyArray=Range("TestRange")

for i=1 to ubound(MyArray)



i received an error at the "For i=1 to ubound(MyArray)" line. It's because my data only has one value. my question is what could i add as a test to prevent the code from error-ing out when only one item is present?



Thanks,

 

[Rick Rothstein]

Re: VBA: Working with arrays

Try using a structure something like this...

Dim MyArray As Variant
Dim i As [SIZE=3][COLOR=red][B]Long[/B][/COLOR][/SIZE] 'array counter
 
If Range("TestRange").Count = 1 Then
  ' only one cell selected, so use Range("TestRange").Value directly
Else
  MyArray = Range("TestRange")
  For i = 1 To UBound(MyArray)
    'etc.
  Next
End If

Note the change I made for your 'i' variable declaration. There is virtually no advantage to declaring a variable as Integer on today's computers... use Long instead.

 

[BIGTONE559]

Re: VBA: Working with arrays

Try using a structure something like this...

Dim MyArray As Variant
Dim i As [SIZE=3][COLOR=red][B]Long[/B][/COLOR][/SIZE] 'array counter
 
If Range("TestRange").Count = 1 Then
  ' only one cell selected, so use Range("TestRange").Value directly
Else
  MyArray = Range("TestRange")
  For i = 1 To UBound(MyArray)
    'etc.
  Next
End If

Note the change I made for your 'i' variable declaration. There is virtually no advantage to declaring a variable as Integer on today's computers... use Long instead.

Hello Rick,

Thanks for the response. I'm kinda bummed a little bit. i have nested for next loops and now it seems as though it's going to be much more complex. i'm working with 5 different arrays and the code is nested within them.



i.e.


for i

for j

for l

for m

for n

'code inserted here

next n

next m

next l

next j

next i

how would you advise going about it in this situation?

forgot to put the code wrappers around and the line spacing isn't correct

 

[Rick Rothstein]

Re: VBA: Working with arrays

At which level are you accessing the "array"?

By the way, problem arises from the type of array VB creates when assigning a range to a Variant variable. If the range contains more than one cell, then a two-dimensional array is created even if the range is a single column or a single row. A single cell, on the other hand, is dealt with quite often and I guess the programmers felt that forcing you to treat it as a two-dimensional array containing one element would be burdensome.... hence, a single cell is not an array.

 

[BIGTONE559]

Re: VBA: Working with arrays

At which level are you accessing the "array"?

By the way, problem arises from the type of array VB creates when assigning a range to a Variant variable. If the range contains more than one cell, then a two-dimensional array is created even if the range is a single column or a single row. A single cell, on the other hand, is dealt with quite often and I guess the programmers felt that forcing you to treat it as a two-dimensional array containing one element would be burdensome.... hence, a single cell is not an array.

Your schooling me rick i love it!

here's the gist of my code:

For i = 1 To UBound(myArray)
  
    
        If Range("MyCrew").Count = 1 Then
        GoTo SkipCrew
        End If
        
        For c = 1 To UBound(MyCArray) 'Crew
        

        
        For j = 1 To UBound(MyRArray)

        
            For p = 1 To UBound(MyJArray)
                
                For o = 1 To UBound(MyOArray) 'Block
                    
                     'Rate
                     'Job Code
                                        
                   
                    
                          'rate
                    
                           cntMemoHrs = 0
                            cntUnits = 0
                            
                             If destrow = 36 Then 'this code is added to change the sheet in the event data reaches the last row
                               destrow = 9
                               s = s + 1
                               End If
                               
                             For Each cell In Range("MyDB")
                             
                              If destrow = 36 Then 'this code is added to change the sheet in the event data reaches the last row
                               destrow = 9
                               s = s + 1
                               End If
                              
                                 If Cells(cell.Row, 2).Value = myArray(i, 1) And Cells(cell.Row, 9).Value = MyCArray(c, 1) And Cells(cell.Row, 3) = MyOArray(o, 1) And Cells(cell.Row, 4).Value = MyJArray(p, 1) And Cells(cell.Row, 8).Value = MyRArray(j, 1) Then
                                    If MyRArray(j, 1) = 13.89 Or MyRArray(j, 1) = 125 Or MyRArray(j, 1) > 30 Then
                                    
                                     Workbooks(nWbk).Worksheets(s).Cells(destrow, 6).Value = Cells(cell.Row, 3).Value 'adds the rate
                                     Workbooks(nWbk).Worksheets(s).Cells(destrow, 7).Value = Cells(cell.Row, 4).Value 'adds the rate
                                     Workbooks(nWbk).Worksheets(s).Cells(destrow, 3).Value = myArray(i, 1) 'adds the date
                                     Workbooks(nWbk).Worksheets(s).Cells(destrow, 4).Value = MyCArray(c, 1) 'adds the crew
                                     Workbooks(nWbk).Worksheets(s).Cells(destrow, 5).Value = Cells(cell.Row, 1).Value   'adds account number
                                     
                                         If Cells(cell.Row, 8).Value = 125 Then 'adds code if the value is 125
                                         Workbooks(nWbk).Worksheets(s).Cells(destrow, 9).Value = Cells(cell.Row, 6).Value
                                         Workbooks(nWbk).Worksheets(s).Cells(destrow, 10).Value = (Cells(cell.Row, 6).Value - 10) * -1
                                         Workbooks(nWbk).Worksheets(s).Cells(destrow, 11).Value = 1
                                         Else
                                         Workbooks(nWbk).Worksheets(s).Cells(destrow, 9).Value = Cells(cell.Row, 6).Value
                                         
                                         Workbooks(nWbk).Worksheets(s).Cells(destrow, 11).Value = Cells(cell.Row, 6).Value
                                         End If
                                    Workbooks(nWbk).Worksheets(s).Cells(destrow, 12).Value = Cells(cell.Row, 8).Value 'adds the rate
                                     
                                    destrow = destrow + 1
                                    Else
                                     cntUnits = cntUnits + Cells(cell.Row, 7).Value
                                        cntMemoHrs = cntMemoHrs + Cells(cell.Row, 6).Value
                                    End If
                                    
                                                                   
                                   
                                End If
                 
                        Next cell

                                         If destrow = 36 Then 'this code is added to change the sheet in the event data reaches the last row
                                        destrow = 9
                                        s = s + 1
                                        End If
                                 
                                 If cntUnits = 0 Then
                                 GoTo ResetMe
                                 
                                 Else
                                 
            

                                Workbooks(nWbk).Worksheets(s).Cells(destrow, 7).Value = MyJArray(p, 1)
                                Workbooks(nWbk).Worksheets(s).Cells(destrow, 6).Value = "'" & MyOArray(o, 1) '
                                Workbooks(nWbk).Worksheets(s).Cells(destrow, 3).Value = myArray(i, 1) 'adds the date
                                Workbooks(nWbk).Worksheets(s).Cells(destrow, 4).Value = MyCArray(c, 1) 'adds the crew
                                Workbooks(nWbk).Worksheets(s).Cells(destrow, 5).Value = "Hrs" 'adds account number
                                Workbooks(nWbk).Worksheets(s).Cells(destrow, 9).Value = "" 'depends on which units i'm recording
                                    If MyRArray(j, 1) >= 8 Then
                                    Workbooks(nWbk).Worksheets(s).Cells(destrow, 9).Value = cntUnits
                                    Workbooks(nWbk).Worksheets(s).Cells(destrow, 11).Value = cntUnits
                                    Else
                                    Workbooks(nWbk).Worksheets(s).Cells(destrow, 9).Value = cntMemoHrs
                                    Workbooks(nWbk).Worksheets(s).Cells(destrow, 10).Value = cntUnits
                                    Workbooks(nWbk).Worksheets(s).Cells(destrow, 11).Value = cntUnits
                                    End If
                                
                                Workbooks(nWbk).Worksheets(s).Cells(destrow, 12).Value = MyRArray(j, 1)
                                
                                destrow = destrow + 1
                                End If
                                
ResetMe:
 
 
                         
                            
                       Next o
                   
                    Next p
                       
               Next j
                     
               
                        
            Next c
     destrow = destrow + 1
 Next i