• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

Excel loop and add cell based upon condition of another cell

I need to loop through a range of rows, and if column D has the value 'No' in it, I need to add column L to a running total and display that on a different sheet's cell. Also, I don't know visual basic. I imagine that this is an easy task for someone who does though. Please help!
0
dale_abrams
Asked:
dale_abrams
  • 3
  • 2
1 Solution
 
broro183Commented:
hi,

Here's a Sumif formula which will give a running total in a single cell
=SUMIF($D$1:$D$100,"No",$L$1:$L$100)

Open in new window

It's not vba, but does this formula do what you need?

hth
Rob
0
 
dale_abramsAuthor Commented:
This is exactly what I was looking for. If I could give you 1000 points, I would. Thanks so much for your help.
0
 
dlmilleCommented:
Here's two alternatives, both providing running total.

I created 2 sheets:  

SheetToSearch - with the Yes/No options in Column D and a set of random numbers converted to values in column L.
ResultSheet - with the results of a running total formula like:

=IF(SheetToSearch!D3="No",SheetToSearch!L3+D2,D2)

in column D.

Also, a Column F having the results of doing a running total with the same specifications, running a macro.  

Here's the code:


 
Sub RunningTotal()
Dim RunningTotal As Double
Dim searchRange As Range
Dim WS As Worksheet
Dim RS As Worksheet
Dim myCell As Range


    Set WS = Worksheets("SheetToSearch")
    Set RS = Worksheets("ResultSheet")
    
    Set searchRange = WS.Range("D2", WS.Range("D" & Rows.Count).End(xlUp))

    RunningTotal = 0
    For Each myCell In searchRange
    
        If myCell.Value = "No" Then
            RunningTotal = WS.Range("L" & myCell.Row).Value + RunningTotal
        End If
        RS.Range("F" & myCell.Row).Value = RunningTotal
        
    Next myCell
End Sub
Sub ClearRunningTotal()
Dim searchRange As Range
Dim WS As Worksheet
Dim RS As Worksheet
Dim myCell As Range


    Set WS = Worksheets("SheetToSearch")
    Set RS = Worksheets("ResultSheet")
    
    RS.Range("F2", RS.Range("F" & Rows.Count).End(xlUp)).ClearContents

End Sub

Open in new window


See attached demo spreadsheet.

Enjoy!

Dave
Running-Total.xls
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dlmilleCommented:
Well, I guess I posted too late...  And, apparently did too much work :)

Lol,

Dave
0
 
broro183Commented:
Thanks for the feedback Dale - I'm pleased I could help :-)

Lol, I'm glad you were a bit slow off the mark Dave - I've missed two or three questions tonight because I was too slow!
I think the points for this question may have been enough to get my foot in the door & let me activate a free premium membership :-)
btw, to speed up the macro, I recommend using variant arrays to pluck the data from the sheet, process in memory & then write the results back to the sheet.

Rob
__________________
Rob Brockett. Always learning & the best way to learn is to experience...
0
 
dlmilleCommented:
Well, that's good then.  Good luck, Expert!

Dave
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now