Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assigning cell values to variables | Excel Discussion (Misc queries) | |||
Assigning a variable | Excel Worksheet Functions | |||
ClearContents Not Clearing Query | Excel Programming | |||
Assigning a row to a variable | Excel Programming | |||
Assigning values from a selected range to individual variables | Excel Programming |