I would like have a input box in which a user enter a number and then I would like to be able to use that number in other worksheets within the same workbook. How do I declare the variable for use with other sheets. I know I would first use
Dim intRows As Interger
But now how do I make it global.
The first goes through a directory and opens all the files.. after it opens a given file it goes off into a sub-routine to process the data in that file.
I am trying to create a counter in the first sub-routine and then pass that value into the second sub-routine to tell it to put the values out on the next row down.. so the first time through it puts the values out on row 1, next time it puts them out on row 2, etc.
I have a large excel matrix that contains many references to a Word document. The path to that document includes /ref Doc1/ in every reference. I want to change that to /ref Doc1 rev1/. Is there a 'replace' tool to do that?
View 2 Replies View RelatedIf I want to set a global variable when I open my workbook which will be used in code on the individual spreadsheets, how would I do this? I want to set the time the workbook is opened to a variable (constant) and then compare that time to current time on each calculation in the worksheets.
I'm using Excel 2000.
explain the difference between declaring a variable as global or as public. Aren't they both available to the entire project including forms?
View 9 Replies View RelatedI have a worksheet that has 2 sheets. I want to declare a variable that can be accessed for reading and/or overwriting by both of those sheets. Where do I declare it and how do I access it from Sheet1 for example?
View 4 Replies View Relatedhow to set up a global varable over a workbook? Incognito439
View 5 Replies View RelatedI have a smattering of experience within various programming languages, but am still coming to terms with the basics of VBA. I am trying to declare a global variable, assign it a value, then use that global variable. Within 'ThisWorkbook' I have the following...
Public myText As String
Private Sub Workbook_Open()
myText = "Hi There"
End Sub
...and in the Microsoft Worksheet Object Sheet1 (Sheet1) I have...
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox myText
End Sub
Now, my understanding of this code is that upon opening the workbook, myText variable will be declared, and then assigned the value "Hi There". Then, once I have clicked anywhere on Sheet1, a message box will appear stating "Hi There". Problem is, the message box is blank. This is all fine, except I want the message box to state "Hi There". What am I doing wrong? Is the variable declared (publically) correctly? Am I assigning the public variable the value correctly?
Am I referencing the public variable correctly in the Worksheet_SelectionChange procedure correctly?
I maked a userform who use global parameters (to let the user decide where write things) and later i need to use this parameter in a module (who is the main program). I try to resolve this problem put this global parameters like global parameters in the module and later in the both sides (in the module and in the useform), but it can't work. How i can resolve that?, i.e., How i can use a global parameter in a userform and the same global parameter in the modulo with the same data?
View 6 Replies View RelatedI am working on a workbook which uses a large number of variables. I am trying to keep them as "local" as possible to keep it simple. Some of my variables are local to the subs they're used in. Some are global as they're used by subs in several sheets. A third type of variable is used by several subs all belonging to the same sheet. Is there a way of declaring them so they're known by all subs in that sheet, but not by every sub in the workbook?
View 4 Replies View RelatedI have a UserForm that runs when my excel project starts that prompts the user for two pieces of data: a username and a password (these are not for logging into the file itself, I need to use them to call web queries later). I want the UserForm to store both of these data in global variables, so that macros that are run in the future can refer to them and read their values. Unfortunately, I am very new to VBA and I cannot figure out what code I need and where exactly I need to put it. Here is my current code, where "authentication" is the name of the UserForm object, and the textboxes I use for entry are named "user" and "pass": In "This Workbook"
View 4 Replies View Relatedhow to declare&initialize a variable as Global in vba?
I have a variable ,
listGroup=Array("aaa","bbb","ccc")
now i am using this variable for 3 different functions. so what i am doing is wrote the same code to 3 functions, so how i can declare&initialize this variable as global and access to all functions.
I need to declare a global variable in Application level not Module level.
I have declare a variable in Module1 and then Module2 with same name then complile it and get success. That means there has two variable with same name in different Module. I think this was not a proper global variable declaration by which I can allow to declare only one variable in all Module, Class every where. I have used code as below -
How can one change the cell values of a worksheet by creating a setup page in another worksheet. Example: the worksheet value is =average(E7, F7, G7, AQ7)*0.6 -- which this formula makes 60% of the average. On the setup page or worksheet I want o change value of *0.6 to say *0.5 for all the cell that has this value. In other words form the setup all the values will change on the related worksheet from the setup page.
View 5 Replies View RelatedI would like to ask the user if when the name the worksheet the same as an already existing spreadsheet tabe if they would like to overwrite it or unload the user form.
I am not sure of two things:
1. how to find the already existing tab?
2. Once I find out how do I programatically delete it, so the code can continue
The code below works with the exception of the last section (trying to achieve the questions stated above).
I am using Excel 2010.
Code:
Private Sub CommandButton1_Click()
If TextBox1.Value = blank Then 'Need name for processing
MsgBox ("Name must not be blank.")
Exit Sub
End If
If Len(TextBox1.Value) > 12 Then
[code]....
i hv following code , which is working fine.
Sub M_0_1_project_20080107_ask_how_to_make_last_row_as_variable()
Range("F1") = "month"
Range([e2], [e6].End(xlDown)).Offset(0, 1).FormulaR1C1 = _
"=month(RC[-1])"
Range("g1") = "yr"
Range([f2], [f6].End(xlDown)).Offset(0, 1).FormulaR1C1 = _
"=YEAR(RC[-2])"
End Sub
i like to change to code as following , but it cannot work , hope some one can help me.
Private Sub Worksheet_BeforeDoubleClick _
lastrow = 6
Range("F1") = "month"
Range([e2], [e[lastrow]].End(xlDown)).Offset(0, 1).FormulaR1C1 = "=month(RC[-1])" _
Range("g1") = "yr"
Range([f2], [f[lastrow]].End(xlDown)).Offset(0, 1).FormulaR1C1 = "=YEAR(RC[-2])" _
End Sub
As i have many variable with same function, tried of keep change last row number.
So I'm trying to use the SEARCH function in excel to search for a particular text in a cell. I am trying to create a macro for it.
View 9 Replies View RelatedI am trying to make a spreadsheet that will have a weekly schedule and will recognize and make some sort of message box or something when a person's name is used twice in the same range. For example, if Smith is in B2 and in B4 then message box. I'm not sure how to do this.
My thought was to make a variable range to check if anything in B2:B7 is equal to say, B3. But the range B2:B7 would have to exclude the cell that is compared, B3. Otherwise it is always true.
I have 2 worksheets - "Results" and "Historical". I am trying to record a macro that copies data from "Results" and then pastes into Historical. The data I want to copy will always be in "Results" A1:A10. When I want to update the macro (which will be weekly), I want it to copy A1:A10 and then paste into "Historical". Simple enough. Here is the problem:
Since this will be updated weekly, the first time I update I will want "Results" data copied into "Historical" A1:A10. The next time I update, I want the data copied into "Historical" B1:B10; then C1:C10, etc. Essentially I want the keep a running total of all of my prior data.
So I need the macro to be able to say something like - paste date into the first available column. If there is data in A1:A10, then paste into B, then C, D. How do I make the Macro variable?
I am trying to make a calculation using variable text.
My outcome cell is D3 and my two variable cells are C3 and F3. In C3 I want to be able to insert a staff members name, of which we have 6 different members of staff. In F3 i need to insert the week number which runs 1 through 6. The outcome is the individuals place on the week roster which needs to be a number between 1-6 inclusive. SO basically if F3=1 & D3=J.SMITH then I need D3 to equal 1 keepng in mind that if F3 & D3 have 6 possible variables.
I have made a very basic spredsheet which using an assumption table makes a particular column of values which works out the revenue. Then I just drag the box down to the number of rows I want it to go down.
However, I want to be able to enter a number in a CELL away from the tale eg. 50 and have the number of rows in the column go down to 50
How to make + symbol variable in the below formula?
This formula is in G25 and symbol is in F25 has + & - symbols
=(NOW()-E$592)+E614
How do I make a column array variable within a function? I've done nested vlookups to get a column number, but the function I'm using doesn't use a "number", it uses a column aray. '=AVERAGEIFS(L:L,K:K,">="&B$2,K:K,"<="&B$3). The "L:L" needs to be moved according to Market, (or column choosen).
View 2 Replies View RelatedI have read meanwhile dozends of articles and comments about absolute and relative cell references.
But I found nowhere an example on how to make a the column part of a cell reference variable and the row absolute.
Is
K$10
a valid expression?
In opposite to $K10 is must work this way
I would like to run a calculation based on selected active range in sheet. I tried several methods and cannot make it work.
View 2 Replies View RelatedI have written a code for a function in VB editor in a sample excel sheet. The UDF is working fine, but I am not able to use it in every other worksheet. I need to give the code to my managers, so even they can use the function conviniently just from the formula bar. I had declared the function as a Public function, so I thought it will be accessible in other excel sheets as well but that doesn't seem to work.
View 7 Replies View RelatedI have a holiday chart which has a summary at the end which counts holidays and sickness etc. It is in a workbook with a worksheet for each month, how can I define a range name that i can use with all worksheets, at the moment I am having to define new range names for all of the sheets and this is taking some time?
When I am in the new worksheet I can click on the range names in the top left but when i select one it takes me to the worksheet where the range is.
I am trying to match up two data sets that are composed of some not very clean data. Some of my match criteria is going to include text strings.
One data set often has abbreviations for words. Example:
Massachusetts General Hospital
MASSACHUSETTS GENL HOSP
Or St vs Street. Mt vs Mount.
The list of criteria of subs I want to do is quite extensive so it does not make sense to try and do this with embedded replace() and substitution() formulas.
Want I want to is to write some kind of case statement in VB where I can load a list of substituions into a worksheet with the word in the A column, and the sub in the B column.
A b
GENL General
And then the code would find these words and sub them out whenever they are present.
Ideas on how to get started? Maybe there is a function out there already written that I can use? I searched a bit, but I am not familar with all of the resources out there. I don't need someone to write this for me, but just maybe a best practice on how you would, or point me to examples of similar functions.
Is there any way to select things like tools - options across all worksheets in a workbook? I often want to suppress display of zeroes in an entire workbook, but so far the only way I've found to do this is to go to eash worksheet in turn and go thru the procedure on each one.
View 3 Replies View RelatedI have never used or created a Macro before. I have worked out how to record one etc. What I would like to know is, is it possible to design a macro and whenever an excel workbook is opened it will list all the Macro's you have created so that you can just action on any sheet or work book.
View 8 Replies View Related