# Something funky with Arrays - Can it be done?

#### In Distress

##### New Member
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.

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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``````

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``````

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.

Replies
7
Views
474
Replies
7
Views
323
Replies
1
Views
522
Replies
7
Views
216
Replies
7
Views
655

1,217,386
Messages
6,136,289
Members
450,002
Latest member
bybynhoc

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back