Something funky with Arrays - Can it be done?

[In Distress]

Evening all if you're in my part of the world - if not - good morning

I want to try and achieve something with an array and I'm not sure if it can be done. Let me set the scene.

I have 2 Public Integer Variables set by another Procedure with their values being between 0 and 110. The variable names are:
(PVI_TheStartingAgeIs)
(PVI_TheEndingAgeIs)

And I have an array called (SelectedLGAarray)

I created the array by using 2 other module level variables so the way I created it was as follows (I'll explain the Y & Z Variables soon):

Dim SelectedLGAArray As Variant, Y, Z

SelectedLGAArray = Range((LGARangeStart), (LGARangeEnd))

This array's elements run from ($A1179:$D1260). In Column 1 is a name like a State, Column 2 contains an age, Column 3 may or may not contain and integer and the same goes for Column 4.

Here's the tricky bit - explaining it's abit tricky as well, but I'll do my best.

This bit all involves using the SelectedLGAArray. What I want to do is find the rows that fall on and between the 2 public variables (PVI_TheStartingAgeIs) & (PVI_TheEndingAgeIs), then sum the total of the values in Column 3 and Column 4 - and then assign these totals to the Y & Z Variables I dimmed with the array.

So, Using 2 variables that indicate a starting age and an ending age, I want to find the values in column 2 of the array that are inbetween the starting age and ending age, then sum the values in columns 3 and 4 and assign the sums to the Y & Z Variables respectively.

The ages in Column 2 of the array are in ascending order (i.e 0 to 110)
so hopefully this helps to simplify the solution.

I hope you understand what I'm getting at - please reply if you need any help deciphering my half a@sed attempt at explaining what it is I'm trying to achieve.

Thanks in advance all.

 

[Andrew Poulsom]

You could us SUMIF directly on the range to do that:

Sub Test()
    Const PVI_TheStartingAgeIs = 23
    Const PVI_TheEndingAgeIs = 46
    Dim LGARangeStart As String, LGARangeEnd As String
    Dim Rng As Range
    Dim WF As WorksheetFunction
    Dim Y, Z
    LGARangeStart = "$A1179"
    LGARangeEnd = "$D1260"
    Set Rng = Range(LGARangeStart & ":" & LGARangeEnd)
    Set WF = WorksheetFunction
    Y = WF.SumIf(Rng.Columns(2).Cells, "<=" & PVI_TheEndingAgeIs, Rng.Columns(3).Cells) - WF.SumIf(Rng.Columns(2).Cells, "<" & PVI_TheStartingAgeIs, Rng.Columns(3).Cells)
    Z = WF.SumIf(Rng.Columns(2).Cells, "<=" & PVI_TheEndingAgeIs, Rng.Columns(4).Cells) - WF.SumIf(Rng.Columns(2).Cells, "<" & PVI_TheStartingAgeIs, Rng.Columns(4).Cells)
    MsgBox Y & " " & Z
End Sub

 

[lozzablake]

can't you just loop through the array and check to see if each element meets the criteria? if it does add in the appropriate amount to Y and Z eg

for i=1 to ubound (SelectedLGAArray)
    if SelectedLGAArray(i,2)>LGARangeStart and  SelectedLGAArray(i,2)<LGARangeEnd Then
        Y=Y+SelectedLGAArray(i,3)
        Z=Z+SelectedLGAArray(i,4)

    end if

next i

 

[In Distress]

Spot on lozzablake!

Worked a treat. Just gotta love VBA - 50 ways to do 1 thing, Thanks for the help - I'm new to arrays in VBA - and I must say - I'm finding them quite useful with my current project.

Cheers again.