+ Reply to Thread
Results 1 to 7 of 7

Looping through rows in range AND comparing range cells

  1. 10-04-2006, 08:24 PM #1
    Damask
    Damask is offline
    Registered User
    Join Date
    10-03-2006
    Posts
    8

    Question 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.
    PSUEDOCODE (not working code, please advise)
    Please Login or Register  to view this content.
    So the expected result would be 18.
    Any help very much appreciated.

    - Damask -
    Reply With QuoteRegister To Reply
  2. 10-04-2006, 09:55 PM #2
    duane
    duane is offline
    Valued Forum Contributor
    Join Date
    07-11-2004
    Posts
    851
    could you jst use the sumif function?

    =sumif(B1:B5,"g2",D1:D5)
    not a professional, just trying to assist.....
    Reply With QuoteRegister To Reply
  3. 10-04-2006, 10:35 PM #3
    duane
    duane is offline
    Valued Forum Contributor
    Join Date
    07-11-2004
    Posts
    851
    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
    Reply With QuoteRegister To Reply
  4. 10-05-2006, 10:14 AM #4
    Myles
    Myles is offline
    Forum Contributor
    Join Date
    11-11-2005
    Posts
    267
    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.
    HTH
    Myles

    ...constantly looking for the smoother pebble while the whole ocean of truth lies before me.
    Reply With QuoteRegister To Reply
  5. 10-05-2006, 11:51 AM #5
    Damask
    Damask is offline
    Registered User
    Join Date
    10-03-2006
    Posts
    8
    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,
    - Damask -
    Reply With QuoteRegister To Reply
  6. 10-05-2006, 01:10 PM #6
    Myles
    Myles is offline
    Forum Contributor
    Join Date
    11-11-2005
    Posts
    267
    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.
    Last edited by Myles; 10-05-2006 at 02:04 PM.
    Reply With QuoteRegister To Reply
  7. 10-16-2006, 10:30 PM #7
    Damask
    Damask is offline
    Registered User
    Join Date
    10-03-2006
    Posts
    8
    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!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1