MrExcel.com - Your One Stop for Excel Tips & Solutions

Macro to totalize a number of values under condition of a data validation.

Posted by Nona Sloven on May 18, 2001 9:52 AM

Hi:

Can anybody help me? I have a table in a worksheet in Excel’97 and I need a code in VBA for the following:
Range F25:F31 has data validation on each of its cell that allows any of a number of entries. One of them is the word TOTAL. I also have a range (J25:J31) for dollar amounts to be entered manually. I need a macro that sums up all the values in range J25:J31, but only those values that are above the row where the user selects the word TOTAL. Let’s remember that the user can select TOTAL in any of the cells in the range J25:J31. The macro should only run when the word TOTAL is selected or entered. The macro should also write the total amount in the same row where the word TOTAL is selected.

Thank you.

Nona Sloven

Posted by Capulet Mercutio on May 18, 2001 6:32 PM

You didn't mention where you want to put the sum of the values. The following macro puts it in Column K.

Sub EnterTotal()
Dim data As Range, tot As Range
If Selection.Cells.Count > 1 Then
MsgBox "You must select one cell only"
Exit Sub
ElseIf ActiveCell.Value <> "TOTAL" _
Or Intersect(ActiveCell, Range([F25], [F31])) Is Nothing Then
MsgBox "You must select a cell in range F25:F31 that contains 'TOTAL' "
Exit Sub
Else
Set data = Range([J25], ActiveCell.Offset(-1, 4))
Set tot = Cells(ActiveCell.Row, "K")
tot.Value = Application.Sum(data)
End If
End Sub