1

Hey everyone: I'm trying to make a function that digs through an array and adds values from range2 when the corresponding value from range1 equals criteria1.

I'm relatively new to VBA, so it's not the most elegant function in the world, but here's my code:

Function SingleArray(range1 As Range, range2 As Range, criteria1 As String)

Dim newrange() As Double
Dim d As Integer
Dim g As Integer
Dim i As Integer

g = Application.WorksheetFunction.CountIf(range1, criteria1)

ReDim newrange(g)

d = 1

For i = 0 To (range1.Count)
    If range1(i) = criteria1 Then
        newrange(d) = range2.Item(i).Value
        d = d + 1
        End If
    Next i


SingleArray = newrange

End Function

Here is my data sample:

range2  range1
-5000   Bob
-5000   Jim 
 200    Bob 
 500    Jim 
 5000   Bob 
 200    Bob 
 300    Bob 
 1000   Bob

When I set the criteria as "Bob," the array that is returned is as follows:

{0,-5000,200,5000,200,300,1000}

I'm genuinely at a loss for how that zero is making it in there. Any thoughts you can provide would be most welcome!

3
  • 2
    Range indexing is 1 based. You're copying it into a zero based array. Commented Feb 20, 2019 at 19:37
  • 1
    ReDim newrange(1 to g) would fix your problem. ReDim newrange(g) is equivalent to ReDim newrange(0 to g) Commented Feb 20, 2019 at 19:51
  • It's working now, thank you! Commented Mar 4, 2019 at 16:54

1 Answer 1

5

1-D arrays default to a zero-based index structure (e.g. 0, 1, 2, 3, ....). You are looping through the ranges with a one based index (e.g. 1, 2, 3, 4, ...).

When you declare ReDim newrange(5) you are actually creating an array with six elements, not five (e.g. 0, 1, 2, 3, 4, 5)

You can make all arrays on that code sheet default to a one based index by putting this compiler directive at the top of the code sheet.

Option Base 1

You can also change the way the array is declared on the fly by specifying the Lower Boundary and the Upper Boundary.

ReDim newrange(1 to g)
Sign up to request clarification or add additional context in comments.

3 Comments

Have an upvote - note that the explicit array bounds are much preferable than setting Option Base and keeping the implicit lower bound.
Agreed. I never use Option Base 1 myself and honestly don't see much use for it beyond obfuscating one code sheet among others in a project that will still default to Option Base 0. However, I know others that do use it and prefer it so there it is for those that prefer to complicate things unnecessarily.
Perfect, thank you! I appreciate your explanation; I learned something new today!

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.