# Looping through rows in range AND comparing range cells

1. ## Looping through rows in range AND comparing range cells

Hi all,

I've been wrestling with Excel and VBA for the last few days with no result. I'm a programmer so know how to structure logic, but VBA is... different (to put it nicely :P )
Anyway, I'm trying to write a function that will accept a range and a variable, checking on each row if the variable is in the required cell and then doing a calculation based on another cell.

SHEET LAYOUT
``Please Login or Register  to view this content.``
FUNCTION CALL
To use this function you would have to select range B2 to D5 and provide a category, as so:
``Please Login or Register  to view this content.``
``Please Login or Register  to view this content.``
So the expected result would be 18.
Any help very much appreciated.

2. could you jst use the sumif function?

=sumif(B1:B5,"g2",D1:D5)

3. in VBA, and I am not very good at this - this will do the function assuming the data was in adjacent columns (ie your column B in column 1, and your column D in column 2), and you placing the cursor on the top left corner of the array. Anyway - probably now what you wanted but maybe this will help you along.

Sub rangecalc()
Dim myrow As Integer
Dim mycol As Integer
mycol = ActiveCell.Column
myrow = ActiveCell.Row
mylastrow = ActiveCell.End(xlDown).Row
For i = myrow To mylastrow
If Cells(i, mycol) = "g2" Then total = total + _
Cells(i, mycol + 1).Value
Next i
Cells(myrow, mycol + 4) = total
End Sub

4. Dam,

What you are trying to accomplish can be better handled by formulas of which the SUMIF formula earlier posted is one. Others are:

=SUMPRODUCT((b2:b5="g2")*(d2:d5))
=SUM((B2:B6="g2")*(D2:D6)) enter as ARRAY FORMULA with Control+Shift+Enter

However, since you are programming inclined, your Function can take this form:

``Please Login or Register  to view this content.``
whereupon, = RangeCalc(b2:d5,"g2") will return 18.

5. Thank you both duane and Myles, but unfortunately the solution isn't quite there yet (although I'm starting to understand how to structure code in VBA!).
Yes my example has simple data and could easily be done by an inbuilt function, but my real data is quite extensive and requires some pre-processing calculation, thus I need the info to build a UDF - even though it triggers a macro warning .

Using your solution Myles I notice some problems. For example:

SHEET LAYOUT
If I also have data in column A that has the same 'search' parameter as what I'm looking for in column B...
``Please Login or Register  to view this content.``
FUNCTION CALL
...and change the range in the function call to include column A...
``Please Login or Register  to view this content.``
...my answer is 22. Your code declares 'c' as a range (a default 1 cell range I presume). By using it to loop through the range passed into the function it's literally going through each cell checking if it is equal to the search parameter. How could I limit it to only look for values in column B on each row?

Thanks,

6. Dam,

In the event of requiring a selective Column key in a broadly defined range, you will need a 3rd argument as in:

``Please Login or Register  to view this content.``
Thus, =RangeCalc("A2:D5,2,"g2") will search on Column B while, if there is bodily shift, RangeCalc("E10:H15",2,"g2") will be keyed on Column F.

PS: Yes indeed c is defined as a range and hence so declared.

7. Myles, that is fantastic!

I felt a little silly asking (what I thought) was such a simple thing to do, but without your help I never would have thought to structure my code like that! Better still I understand exactly how it works and have written a real function tailored to my needs.

Also thanks to duane - I used the concepts in your code for another problem I was having.

Case closed!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1