another won't work for you fine people
 

here is the code.

Sub TOTHERIGHT()
Dim B As Double
Dim T As Double

Range("E3:E6").Select
CaseText = activecell.Value
Select Case (CaseText)
Case Is < "TOTAL ACCOUNTS"
activecell.Offset(0, 1).Select
B = activecell.Value
T = T + B
End Select

MsgBox (T)
End Sub

E3:E6 contains text
F3:F6 contains numbers

I want a message box that contains the sum of all the numbers to th
right of cells that don't say "TOTAL ACCOUNTS"

When I run this I get a message box that says

--
Message posted from http://www.ExcelForum.com

another won't work for you fine people
 

ksknapp


Try

Sub TOTHERIGHT()
Dim r As Range
Dim j As Integer
Dim T As Double
Dim WorkRange As Range
Set WorkRange = ActiveSheet.Range("E3:E6")
For r = 1 To WorkRange.Rows.Count
If WorkRange.Cells(r, 1) < "TOTAL ACCOUNTS" Then
T = WorkRange.Cells(r, 1).offset(0,1).Value + T
End If
Next r
MsgBox (T)
End Su

--
Message posted from http://www.ExcelForum.com

another won't work for you fine people
 

You need to use a loop to iterate through the cells.
Try something like:

Sub TOTHERIGHT()
Dim T As Double
Dim myCell as range
Dim myRange as range

Set myRange = Range("E3:E6")
T = 0
For each myCell in myRange
CaseText = myCell.Value
Select Case (CaseText)
Case Is < "TOTAL ACCOUNTS"
T = T + MyCell.Offset(0, 1).value
End Select

MsgBox (T)
End Sub

--
Darren
"ksnapp " wrote in message
...
here is the code.

Sub TOTHERIGHT()
Dim B As Double
Dim T As Double

Range("E3:E6").Select
CaseText = activecell.Value
Select Case (CaseText)
Case Is < "TOTAL ACCOUNTS"
activecell.Offset(0, 1).Select
B = activecell.Value
T = T + B
End Select

MsgBox (T)
End Sub

E3:E6 contains text
F3:F6 contains numbers

I want a message box that contains the sum of all the numbers to the
right of cells that don't say "TOTAL ACCOUNTS"

When I run this I get a message box that says 0


---
Message posted from http://www.ExcelForum.com/

another won't work for you fine people
 

Oops forgot to close the loop - see amended code below:
Darren
----- Original Message -----
From: "Darren Hill"
Newsgroups: microsoft.public.excel.programming
Sent: Thursday, March 04, 2004 5:13 AM
Subject: another won't work for you fine people


You need to use a loop to iterate through the cells.
Try something like:

Sub TOTHERIGHT()
Dim T As Double
Dim myCell as range
Dim myRange as range

Set myRange = Range("E3:E6")
T = 0
For each myCell in myRange
CaseText = myCell.Value
Select Case (CaseText)
Case Is < "TOTAL ACCOUNTS"
T = T + MyCell.Offset(0, 1).value
End Select
' close the loop, you silly boy.
Next myCell

MsgBox (T)
End Sub

--
Darren
"ksnapp " wrote in message
...
here is the code.

Sub TOTHERIGHT()
Dim B As Double
Dim T As Double

Range("E3:E6").Select
CaseText = activecell.Value
Select Case (CaseText)
Case Is < "TOTAL ACCOUNTS"
activecell.Offset(0, 1).Select
B = activecell.Value
T = T + B
End Select

MsgBox (T)
End Sub

E3:E6 contains text
F3:F6 contains numbers

I want a message box that contains the sum of all the numbers to the
right of cells that don't say "TOTAL ACCOUNTS"

When I run this I get a message box that says 0


---
Message posted from http://www.ExcelForum.com/

another won't work for you fine people
 

Sub test3001()
Range("A10").Formula = _
"=SUMPRODUCT((E3:E6=""TOTAL ACCOUNTS"")*(F3:F6))"
MsgBox Range("A10").Value
End Sub

Alan Beban

ksnapp < wrote:
here is the code.

Sub TOTHERIGHT()
Dim B As Double
Dim T As Double

Range("E3:E6").Select
CaseText = activecell.Value
Select Case (CaseText)
Case Is < "TOTAL ACCOUNTS"
activecell.Offset(0, 1).Select
B = activecell.Value
T = T + B
End Select

MsgBox (T)
End Sub

E3:E6 contains text
F3:F6 contains numbers

I want a message box that contains the sum of all the numbers to the
right of cells that don't say "TOTAL ACCOUNTS"

When I run this I get a message box that says 0


---
Message posted from http://www.ExcelForum.com/