Newbie Macro Query - Clearing Variables and Assigning a Variable
 

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

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

Newbie Macro Query - Clearing Variables and Assigning a Variable
 

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

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

Newbie Macro Query - Clearing Variables and Assigning a Variable
 

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

---------
Worksheets(Worksheets("DATA").Range("B9").Value).A ctivate

--
Regards,
Tom Ogilvy



"Mcneilius " wrote in message
...
Hi all

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

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

2. I wish to specify a worksheet to activate in the macro by using the
contents of a cell. For example, if I enter "October" in Cell S4, I
want the macro to activate the worksheet "October". I've tried
something like the code below, without success. I also thought
INDIRECT may help, but I'm unsure how to use it in a macro. Could
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

Neil


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

Newbie Macro Query - Clearing Variables and Assigning a Variable
 

Guys, that's perfect - works a treat!

Thank you very much indeed!

Neil


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