5

I have a macro which runs almost how I want it to:

The macro populates strings based on the contents of a combination of cells.

The inputs are ranges which I convert to arrays. Column E3 has values in cells starting at row 3 and below.

With ActiveWorkbook.Worksheets("Sheet2")
Set GclR = .Range("E3", .Range("E3").End(xlDown))
End With

GclArr = GclR.Value

The arrays vary in size from non-existent to half a dozen rows in length. My code loops through the contents and finds each value and places it into it's respective position in a string.

Where it trips up is when when I have less than 2 values in the array. Excel hangs and crashes.

I figure this is due to the For Loop:

If GclArr(1, 1) <> 0 Then
    If Gcl <> 0 Then
        For R = 1 To UBound(GclArr, 1)
        GclStr = GclStr & GclArr(R, 1) & " " & Gcl & " "
        Next R
    End If
End If

If R = 1 and UBound(GclArr, 1) also = 1, it seems the For Loop gets confused. (It works perfectly if UBound is 2 or greater).

I used an If statement to handle the case when the array is empty, but can't think of a way to get this to work for an array with a single value.

What other function can I use to achieve the same result, but with the ability to handle single values in arrays?

Improve this question
6
  • For R = LBound(GclArr)? Commented Aug 10, 2016 at 7:20
  • How is your range data placed in the array? If you are using GclAry = SomeRange.Value and the range is one cell, GclAry will not be an array at all, but a single value Commented Aug 10, 2016 at 8:44
  • @dee Thank you for your suggestion. I have tried this now, but it has not changed the result. Still fails when the array consists of a single value or less. Commented Aug 10, 2016 at 23:52
  • @chris neilsen I have a function which establishes used cells in a column then sends the values of that range to an array. I understand that when the range is only a single value it is practically not an array. But I was hoping that vba code would still be able to work with this situation. Commented Aug 10, 2016 at 23:58
  • @EP it will help if you post that code (edit it into your Q) As others have said, a For loop does not have a problem with a single element array. As it stands your post has insufficient information to diagnose the problem Commented Aug 11, 2016 at 0:56

5 Answers 5

Reset to default
4

The root of your issue is the line Set GclR = .Range("E3", .Range("E3").End(xlDown)). When there is only a value in E3 and no other values below, your code will return a range down to the bottom of the sheet. As you said you will then be searching 1,000,000+ empty cells.

To fix it you have two options:

  1. If there is no other data in column E below your intended data, find the range from the bottom up.

    Set GclR = .Range("E3", .Cells(.Rows.Count, 5).End(xlUp))

  2. If you must use .End(xlDown) test for a blank cell first

    If IsBlank(.Range("E4")) Then
    Set GclR = .Range("E3")
Else
    Set GclR = .Range("E3", .Range("E3").End(xlDown))
EndIf

In both cases you may return a single cell range to GclR. When that happens CclR.Value will not be an array. To handle this use

If GclR.Cells.Count = 1 Then
    ReDim GclArr(1 to 1, 1 to 1)
    GclArr(1, 1) = GclR.Value
Else
    GclArr = GclR.Value
EndIf

You can then use your original For loop code

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you for your suggestion. Option 2 with method to handle single values is similar to my workaround below. I'll leave my code as is, since it is working as intended now. Your help has been appreciated.
@ep The solution i posted is quite different to yours, it avoids creating the inflated array at all, yours simply detects the bloat and works around it
1

The for loop has no issue if there is only iteration (see the example)

Sub ForLoopTest()

Dim iItem As Long

For iItem = 1 To 1
        MsgBox (iItem)
        Next iItem

End Sub

Have you considered that VBA array indexes starts by default at 0. So in that case the value of UBound(GclArr, 1) is 0. To eliminate this kind of issues the LBound(GclArr, 1) To UBound(GclArr, 1) syntax can be used.

If GclArr(1, 1) <> 0 Then
If Gcl <> 0 Then
    For R = LBound(GclArr, 1) To UBound(GclArr, 1)
    GclStr = GclStr & GclArr(R, 1) & " " & Gcl & " "
    Next R
End If
End If

The Option Base 1 statement at the start of a module can modify this behavior. In that case the index of the first element of an array will be 1.

Improve this answer

2 Comments

valid answer. ++ Just update your answer's code to have Lbound to Ubound loop example.
@Csetényi Thanks, but as per above, LBound has the same result as my current code - excel crashes.
1 
Private Sub sb_ForTest()
Dim i&
    For i = 1 To 1
        Debug.Print i ' I have everything working
    Next
End Sub

Re: cyboashu

works perfectly for just one row in the array. But as OP said, he gets dynamic bounds for his array. So this answer will fail there. Also, it's a good idea to add some context about your code. Only code in your answer is bit less helpful than an answer with some code + explanation. :)

OP wrote "If R = 1 and UBound(GclArr, 1) also = 1, it seems the For Loop gets confused. (It works perfectely if UBound is 2 or greater)."

This statement is incorrect and I hastened to correct him :) Sorry for didn't quoted him earlier in the head of my message...

Moreover, he has presented it like a problem "For Loop for Array with Only One Element" in the question title :)

I wrote about For...Loop functionality only, regardless the real bounds passed to it by other code. .

Improve this answer

1 Comment

works perfectly for just one row in the array. But as OP said, he gets dynamic bounds for his array. So this answer will fail there. Also, it's a good idea to add some context about your code. Only code in your answer is bit less helpful than an answer with some code + explanation. :)
1

So I checked what the value of UBound is when I only have one value in the range. It is the cell limit of 1048574. This is probably the result of my range definition function:

Set GclR = .Range("E3", .Range("E3").End(xlDown))

and

GclArr = GclR.Value

Given the above, I believe Excel is not actually crashing, but taking a very long time to pointlessly process such a large array.

Based on that, I made the following workaround using an additional If statement:

If Gcl <> 0 Then
    If GclArr(1, 1) <> 0 Then
        If GclArr(2, 1) = 0 Then
        GclStr = GclStr & GclArr(1, 1) & " " & Gcl & " "
        Else
        For R = 1 To UBound(GclArr, 1)
        GclStr = GclStr & GclArr(R, 1) & " " & Gcl & " "
        Next R
        End If
    End If
End If

The If statement checks what the value of the second row in the very large array is, and if it is zero (implying there is no data after the first row), avoids the For Loop and instead, uses a specific function to deal with the value in the first row of the array.

Having so many If statements isn't very elegant, but it works perfectly.

I would still like to use something more elegant if it exists.

Improve this answer

Comments

0

You can avoid a loop and do a direct join (after defning your range and looking up), i.e.

Sub Recut()
Dim Gcl As Long
Dim GclStr  As String
Dim rng1 As Range

Gcl = 3

With Sheets("Sheet2")
Set rng1 = .Range(.[e3], .Cells(Rows.Count, "E").End(xlUp))
End With

If rng1.Cells.Count > 1 Then GclStr = Join(Application.Transpose(rng1), " " & Gcl & " ")
End Sub
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.