CODEWITHSUNDEEP
excelvba
Vivek
Vivek

Reputation: 15

refer to part of an array in excel

I have an array and need to refer to a single column within that array so that i can use it in calculations. Is there a way I can do it directly as indicated by the placeholders for the arguments in sumifs function below or do I need to manually do a sumif via loops?

Sub testarrayinxlformulas()
ScoresArray = Sheets("Scores").Range("A1").CurrentRegion
ScoresSum1 = Application.WorksheetFunction.Sumifs(5thColumnofArrayGoesHere,4thColumnHere,"Good",3rdColHere,VariableGoesHere)
End Sub

Upvotes: 1

Views: 917

Answers (3)

tigeravatar
tigeravatar

Reputation: 26640

For something like this, I prefer to use the With statement and then use the .Columns property, like so:

With Sheets("Scores").Range("A1").CurrentRegion
    ScoresSum1 = WorksheetFunction.SumIfs(.Columns(5), .Columns(4), "Good", .Columns(3), myVariable)
End With

Or expanded for readability:

With Sheets("Scores").Range("A1").CurrentRegion
    ScoresSum1 = WorksheetFunction.SumIfs(.Columns(5), _
                                          .Columns(4), "Good", _
                                          .Columns(3), myVariable)
End With

Upvotes: 1

John Kiernander
John Kiernander

Reputation: 4904

You can refer to ranges within ranges, which can be a little confusing but if you reference a range like this Range("B:C").Range("A:A") will return column B.

Therefore you could write your code like this:

Set ScoresArray = Sheet1.Range("A1").CurrentRegion
ScoresSum1 = Application.WorksheetFunction.SumIfs(
    ScoresArray.Range("E:E"),
    ScoresArray.Range("D:D"), "Good", 
    ScoresArray.Range("C:C"), VariableGoesHere)

NB. You forgot Set on the variable assignment. Without it you assign the value of the range and not the range itself.

Upvotes: 0

Ioannis
Ioannis

Reputation: 5388

I would suggest the Index function. It needs ranges, so ScoresArray and the arguments have to be ranges. A possible scenario (tested):

Sub testarrayinxlformulas()
    Dim ScoresArray As Range, ScoresSum1 As Double
    Dim Scores5th As Range, Scores4th As Range, Scores3rd    As Range, VariableGoesHere As Range
    Set ScoresArray = Sheets("Scores").Range("A1").CurrentRegion
    Set Scores5th = WorksheetFunction.Index(ScoresArray, 0, 5)
    Set Scores4th = WorksheetFunction.Index(ScoresArray, 0, 4)
    Set Scores3rd = WorksheetFunction.Index(ScoresArray, 0, 3)
    ScoresSum1 = Application.WorksheetFunction.SumIfs(Scores5th, Scores4th, "Good")
End Sub

I hope this helps!

Upvotes: 0

Related Questions