another won't work for you fine people

[ksnapp]

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

 

[Kieran]

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

 

[Darren Hill]

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 Hill]

Oops forgot to close the loop - see amended code below:
Darren
----- Original Message -----
From: "Darren Hill" <[email protected]>
Newsgroups: microsoft.public.excel.programming
Sent: Thursday, March 04, 2004 5:13 AM
Subject: Re: 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