Solved: help: How to get cells data for calculation in the best way
I use the following code for a function to sum data with multi conditions and flexible conditions. But when called a lot of time, the calculation become very slow. I dont know whelther there is any "stupid code" I have used and How to effectively do it. I mean what is the best practice way.
For more details, let me give an example for what I try to do with this code:
it is to sum cells in colume named "Colmonth" that satisfy:- the value in the same row at column "colLocation" is equal to "Location"
- the value in the same row at column "colCategory" is equal to "CAT"
- the value in the same row at column "colFlavor" is equal to one of the value in given range named "KeyRange"
What I'm worrying about is the way I get value of range and compare it.
And also about get value in given range (keyRange). Because it is in a different sheet in comparison with other
Please help!!!
[vba]
cntKeyRange = keyRange.Count ' Number of cells in given range
With Sheets(nmeSheet)
For icount = pstLocation To pstLocation + MaxLine 'search range
If .Range(colLocation & icount).Value = Location And .Range(colCategory & icount).Value = CAT Then
For jcount = 1 To cntKeyRange
If .Range(colFlavor & icount).Value = keyRange.Cells(jcount).Value Then
sum = sum + .Range(Colmonth & icount).Value
GoTo Continue
End If
Next
End If
Continue:
Next
End With[/vba]