Something funky with Arrays - Can it be done?

In Distress

New Member
Joined
Mar 12, 2003
Messages
37
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.
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You could us SUMIF directly on the range to do that:

Code:
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

Well-known Member
Joined
Dec 15, 2005
Messages
818
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

Code:
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

New Member
Joined
Mar 12, 2003
Messages
37
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,165
Messages
5,527,179
Members
409,750
Latest member
BorisYeltsin

This Week's Hot Topics

Top