• Status: Solved
• Priority: Medium
• Security: Public
• Views: 323

# 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
• 3
• 2
1 Solution

Commented:
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)
``````
It's not vba, but does this formula do what you need?

hth
Rob
0

Author 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

Commented:
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
``````

Enjoy!

Dave
Running-Total.xls
0

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

Lol,

Dave
0

Commented:
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

Commented:
Well, that's good then.  Good luck, Expert!

Dave
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.