Newbie Macro Query - Clearing Variables and Assigning a Variable

[Mcneilius]

Hi all

I have a couple of appallingly easy newbie questions, that I wondere
if somebody could help me with? I have a feeling I'm close, but
can't make it work...

1. I have a macro performing a summation with the results in
variable. It works fine, unless I execute it twice - presumably I nee
a way of clearing the variables after the code has run? What synta
would I need to use for this?

2. I wish to specify a worksheet to activate in the macro by using th
contents of a cell. For example, if I enter "October" in Cell S4,
want the macro to activate the worksheet "October". I've trie
something like the code below, without success. I also though
INDIRECT may help, but I'm unsure how to use it in a macro. Coul
anyone suggest how I might do this?

Dim MonthText

Worksheets("Budget").Activate
Range("S4") = MonthText
Worksheets(MonthText).Activate

Any advice would be much appreciated!

Many thanks

Nei

 

[Andoni]

1)One easy way:

X=""
once you have used the variable named X

2)
Sub TryThis()
Dim Sht As Worksheet
Dim X As String 'the name of the sheet
'Worksheets("Budget").Activate
X = Range("S4")
'Lcase avoids upper lower... case problems
For Each Sht In ThisWorkbook.Worksheets
If LCase(Sht.Name) = LCase(X) Then
Sht.Activate
Exit For
End If
Next Sht

End Su

 

[Tom Ogilvy]

Assuming you have something like the below, initializing the sum variable
should do it.

Public lngSum as Long

Sub ComputeSum()
lngSum = 0
for each cell in Range("A1:A10")
if isnumeric(cell.value) then
lngSum = lngSum + cell.Value
end if
Next
end sub

 

[Mcneilius]

Guys, that's perfect - works a treat!

Thank you very much indeed!

Neil