Excel 2007 :: Call Sub Using Variable?
Feb 23, 2013
I'm trying to call another sub with a variable string. I'm using excel 2007. I get the error "Compile Error: Expected Sub, Function, or Property" on the "Call" code. The want the code to run multiple different subs based on user input. For example if they typed "this" it would run the t sub, then h sub, then I sub and finally the s sub.
Code example
Dim Uncvrtdtxt as string
Dim SubName as string
Dim i as integer
Uncvrtdtxt = "this"
For i =1 to Len(Uncvrtdtxt)
SubName = Mid(Uncvrtdtxt, i, 1) & "Route"
[code]....
View 7 Replies
ADVERTISEMENT
Jun 28, 2012
I'm trying to find out if there is a way to call the security alert to enable a macro. Let me explain...I was working on a sheet with a macro that I hadn't enabled yet. After working for awhile the option to enable it was gone and I found that I had to close the fine and re-open to enable the macro. I'm wondering if there is a ribbon or short cut icon to put in my access toolbar that will give me the option to enable/disable the macro. Seems strange that in 2003 I could to this, but in 2007 I have to close the file.
View 3 Replies
View Related
Feb 28, 2013
I Wrote a code which as intended to open each excel file in a folder and copy the data containing in it into a new sheet.
But While running the code the first excel file gets open, and an error message "Run Time Error 91-Object Variable Or With block Variable not set Error"
How to set the file which got opened from the folder to wbk variable.
Code:
Sub dataintoonesheet()
Dim i As Integer
Dim jk As Integer
Dim j As Integer
Dim rowstart As Integer
rowstart = 3
[Code] .......
View 9 Replies
View Related
Nov 10, 2006
Say you define a public range variable called Inputworksheet and you set it to refer to the worksheet called Inputworksheet. You have a separate string variable with the value Inputworksheet. How do you get this string variable value to call/control the range variable Inputworksheet?
I am getting an excel worksheet value from a lookup function that corresponds to the name of a VBA range variable. Once I have this worksheet value, I would like to use the range variable that has the same name as the worksheet value.
View 5 Replies
View Related
Jan 10, 2012
i make a lot of "trending" files at work where i link a cell to a seperate workbook where all workbooks are in the same folder.
these workbooks all have the same name with the date at the end
example:
workbook 06-01-2011
workbook 06-02-2011
all the way to
workbook 12-31-2011
usually i use find replace to change the date one at a time, but doing that 365 (x how many cells i have referencing) times is redundant
i have seein INDIRECT of the cell changes but nothing for the workbook name chaning
here is what i am trying to do:
='C:File Path[WorkbookX.xls]Sheet1'!A1
where the X WorkbookX is variable (the date at end of file name)
i have a list of all workbook names or just the dates and would like to do something like:
=D1&$A$1
where D1 is the full path name
or
='C:File Path[Workbook"D1".xls]Sheet1'!A1
where D1 is the date name
using excel 07 i prefer not to use a macro for how i am doing this but if a macro is the only way then i cannot have it actually open the files to pull the data. i prefer no macro since i set it up for future dates so when the new file is created it automatically updates the reference.
my end result i usually have something like A1=referenced cell from first workbook, A2= cell from 2nd book, etc. sometimes B1 would = a different cell from first workbook and so on if i am trending multiple cells from the workbooks
View 1 Replies
View Related
Feb 10, 2012
I am attempting to pass a boolean variable between two macros. The SUBMIT macro calls the STATUS macro to do some verifications and the STATUS macro should return the boolean variable RDY back to the SUBMIT macro, but does not.
I am using Excel 2007. Both macros are in the same Module. The STATUS macro is called from several places in the code.
Public Rdy As Boolean
Sub Submit()
[code]
Call Status
If Rdy=True then msgbox("You have completed your form") else msgbox ("Not ready")
[code]
end sub
[Code]....
View 1 Replies
View Related
Mar 2, 2012
I have a pivot table that has several row labels and 1 column label. I am trying to create a Ranking table outside of the pivot table because I am using Excel 2007 and can't use the new Rank option in Excel 2010. My difficulty is in being able to rank each subgroup of the row labels.
I have thrown a column for "Target Rank" in the data, just to validate my calculation in the PivotTable worksheet, but it is only correct as long as all Company and Product Name options are selected for a given Sex, Age, and Scen Defn.
My non-dynamic formula is:
=RANK(GETPIVOTDATA("Target",$G$5,"Company","AIG a","Product Name","Elite Index UL a","Sex","F","Age",25,"Class","1-Best NS","Scen Defn","Overfunded A"),$L$8:$L$30).
I have figured out how to make some of the items variable to this degree:
=RANK(GETPIVOTDATA("Target",$G$5,"Company",$I8,"Product Name",$J8,"Sex","F","Age",25,"Class","1-BestNS","Scen Defn",$K8),$L$8:$L$30).
I know can make the $L$8:$L$30 copyable across columns by changing it to L$8:L$30.
My dilemma is several fold.
1. If I filter the Company or Product Name the $30 in the range no longer applies. Is there a way to make the bottom of each grouping dynamic?
2. Is there a way to make the item entry associated with the "Class" column label a variable so I can copy across columns?
3. Similar questions related to the row labels for Sex and Age?
4. Lastly, I can write an expression that would make my Rank columns blank if the Company column was blank, but is there a way to reset the data range to look at the next block? That is, if "Age" changed (was not blank) that would indicate the top of a block of data.
I have tried to think of ways to use INDIRECT and I don't know VBA well enough to do it in a macro.
ClassValues1 - Best NS4-Nonsmoker1-Best NS4-NonsmokerTarget RankAV 5 RankAV 10 RankTarget RankAV 5 RankAV 10 RankSexAgeCompanyProduct NameScen DefnTarget Target RankAV 5AV 10Target206420125F25AIG aElite Index UL aOverfunded A 1,520 20 12,848 33,607 2,135 9181961618Allianz LPLife Pro+Overfunded A 2,796 9 10,613 26,147 2,847 7221892217American GeneralElite Global PlusOverfunded A 2,810 7 7,930 26,323 2,810 1913617198AmericanGeneralChoice Index GULOverfunded A 1,610 19 11,388 31,409 2,205
[Code]...
View 1 Replies
View Related
Feb 7, 2014
I am trying to run different macros by clicking various different buttons on the sheet, I then want different data to load into the user form depending which button was pressed. So I have buttons named "SV_1" and another named "SV_2". when either button is pressed then it runs a common macro that gets the name of the calling item. then I want to add "Macro" to the beginning of the calling item name and then call that macro. here is the code that I am working with, when using a watch i can see the value of the variable is "MacroSV_1" when button 1 is pressed but I cannot get it to run the Sub.
VB:
Public ClkBtn As String
Public CallMacro As String
Sub ItemCall()
[Code] ......
View 5 Replies
View Related
Aug 12, 2008
I'm playing around with the Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) function. I have a list of people in Column D that I'd like to move data around if the user double clicks on a cell in Column D. The list is about 158 rows long, and could grow beyond that. I've gotten lazy typing:
If Intersect(Target, Range("D4") Is Nothing Then
Exit Sub
Else
{My Code Here}
End If
If Intersect(Target, Range("D5") Is Nothing Then
Exit Sub
Else
{My Code Here}
End If
Etc, Etc, Etc
What I was hoping to do is use a variable (t) to cycle through the rows and call the various functions. So what I came up with is:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Application.ScreenUpdating = False
On Error Resume Next
Dim t As Integer
Dim Subtest As String
For t = 4 To 158
Subtest = "D" & t
I've created Sub D4(), Sub D5(), and Sub D6() as a test. This didn't work at all as it's looking for a Subtest(). Also, clicking on D5, D6, didn't do much either. Is there any way to make this cycle work, or should I keep typing away?
View 9 Replies
View Related
Sep 8, 2007
I have 46 drop downs that I need to call from a main sub. I want to create one loop that will call each one to perform its function. Below is a simple example of what I am trying to do, but where I need help is with the 'Call Y' line. I am not sure what character to use to tell excel to look at what is in the value of Y, not 'Y' itself
Sub testitout()
Dim y As String
For X = 1 To 2
Sheets(" Lookup"). Range("E3").Value = X + 4
y = "dropdown" & X & "_change()" 'this will return "dropdown1_change()" for the first loop
Call y 'i want to call dropdown1_change() below
Next
View 9 Replies
View Related
Jan 18, 2007
I have a code below which need some input from user. This input will also be serve as the input of the subroutine which i am going to call. However, i do not know how to go assign this input to the subrountine which i will be calling, can anybody help ?
For example, the "input" variable will also be served as an input in subroutine test2 ...
View 6 Replies
View Related
Nov 13, 2009
I wrote an external executable ("graph.exe") which is supposed to save a .xlsm file, close it, read some data from it, do some calculations, and print the results back into the same spreadsheet.
When I double click "graph.exe" everything works. So I created a button in the spreadsheet I mentioned that should call "graph.exe" and therefore initiate the whole process. Here is the
View 5 Replies
View Related
Feb 3, 2009
The attached file contains a simplified version of a more complex macro in which a sub routine will be called a number of times to change the colours of cells in different ranges. However, an error is triggered because the a variable and range are not defined in the subroutine although they are in the main part of the macro.
I know I am missing something obvious here, but I'd appreciate any help in knowing how I can define the ranges in the main procedure and then call the subroutine to change values in the different ranges.
View 6 Replies
View Related
Mar 3, 2007
The master has rows corresponding to numbered files:
4300 | CellValue1 | CellValue2 | etc
4301 | CellValue1 | CellValue2 | etc
In attempting a simple formula (a couple VBA codes I tried did not work and were probably substantially more than I needed anyway), I made the following:
='CMain FolderSub Folder[concatenate(cell w/file number,".xls")]Sheet1'!A1
This is identical to the copy/paste link formula currently in use except that I am trying to have the actual file number/name generated off the master list instead of what is in the file itself.
Purpose: in case I was not clear on this above, I am trying to automatically populate the master worksheet with the data from the workbook. The current procedure is to copy a line of the data from the workbook, then paste as a link in to the master worksheet on the row corresponding to the file number.
View 6 Replies
View Related
Feb 20, 2009
I'm fairly new to macro's and VBA, by searching on the internet i've copied and pasted some code together into a macro.
But it ends in a Run-time error 91...
The macro opens a target .xls file in a selected folder, performs copy - paste actions from masterfile to targetfile.
Than it filters data in the targetfile sheet1 and copy's the results to the various other sheets; saves and closes the targetfile.
The next target file in the folder is opened and the actions are repeated in this second target file.
For the first target file this works smoothly; but for the second one (of a total of around 100) it does not copy the filter results to the other sheets in this workbook.
The error message i get is: "Run-time error 91:Object variable or with block variable not set."
When i hit debug it highlights the line "ActiveSheet.Next.Select" which, at least in the first file, seems ok.
View 9 Replies
View Related
Jun 27, 2014
I want to be able to call one user form from multiple ActiveX command buttons. The problem is, the number of command buttons depends on user input on another worksheet, so it's variable.
I've renamed all of the command buttons so they are named "CommandButton" & i, where i is an integer between 1 and, say, 200. I want each of these buttons to direct to the same UserForm where additional information can be entered.
I can't think of a way around the event-handler procedure name.
Sub [Command Button Name]_Click
to call the User Form. I won't know the command button names, because I won't know how many there are (max i) until the user inputs.
Basically, I want to create a For loop through the max i and have the event-handlers call the user form
View 14 Replies
View Related
Feb 15, 2012
I am acquiring multiple spreadsheets that do not always match row number, due to additional information on some sheets. By this I mean that the information may be in row 31 on on sheet and row 39 on another, the column location is the same each time. I have been trying Vlookups, indexing and matching plus combination formulas - with no luck. How to get the information I need with a moving cell reference?
I can provide a small copy of a workbook, if needed.
View 1 Replies
View Related
May 21, 2006
I have a work book with 3 sheets. Sheet 1 is the main sheet and sheets 2 and 3 will use (I hope vlookup) to update 3 columns from info in sheet 1. my attempt at a vlookup call in sheet 2 is: =VLOOKUP($b3,[master_AoJ_2.xls]Sheet1!$B$3;$B$65,false)
my understanding is that: - $b3 is the cell in sheet 2 that will be updated as a result of the vlookup call. - [master_AoJ_2.xls]Sheet1!$B$3;$B$65 refers to range $B$3:$B$65 on sheet1 of master_AoJ_2. it does not work... infact I get nothing at all. when I type the call into cell $b3 of sheet2 excel thinks it is stariaght text. this is how I coded the function call =VLOOKUP($b3,[master_AoJ_2.xls]Sheet1!$B$3;$B$65,false)
View 4 Replies
View Related
Jul 28, 2014
At my new job the CHT/CRT that we receive is three days behind what we are actually using. It makes it hard for me to keep track of my talk time, so I thought I'd use excel to track it. I can see the time I have been on each call through the day. I had to put it in 0:0:0 format to get it to average correctly. (I was trying to get it down to just seconds but that seems a bit complicated. I just then used "average=(a1:a30)" to average them together and it seemed to work a bit.
View 2 Replies
View Related
Jan 10, 2013
I try to call a function/procedur in an Excel2010-AddIn (.xlam file) from another Excel2010-AddIn (.xlam file):
Code:
Application.Run (filename.xlam!functionname)
Both AddIn-files are in the same directory (the default AddIn directory).
This call causes the error message "runtime error 91: object variable or with block variable not set".
This problems came up with providing an existing Excel2003-AddIn for Excel 2010.
The same code runs in following cases:
1. call a function in an Excel2003-AddIn (.xla) from another Excel2003-AddIn (.xla)
2. call a function in an Excel2003-AddIn (.xla) from a Excel2010-AddIn (.xlam)
View 1 Replies
View Related
Aug 29, 2013
I am trying to open an Excel Workbook which in turn will automatically run a macro on loading which is a Userform. The Userform will contain 3 boxes, just some simple text boxes.
I don't want the Excel Workbook to show in the background, so I have opted to go for a VBScript to call the Excel Application and open the spreadsheet...
I have a current .vbs script which is as follows...
Code:
Option Explicit
On Error Resume Next
ExcelMacroExample
[Code]....
This works fine, there is no workbook showing and the userform opens as expected. The problem is, I lose the functionality to tab between the boxes. When opening the Userform directly in Excel,
View 1 Replies
View Related
Dec 16, 2002
How to covert a Excel worksheet to HTML format as outlook's stationary and then call out Outlook to send it as a normal HTML mail, not a attachment mail?
View 4 Replies
View Related
Nov 10, 2009
I had a question earlier regarding how to call a form using a variable rather than the form name. This works correctly and the form is displayed, however the forms behaviour deviates from the code within the form.
I have attached a simple example which permits a user to determine where on a page a form displays, if called using the form name directly it functions correctly, appearing at the designated co-ordinates. If it is called indirectly using the variable it reaches the line
View 2 Replies
View Related
Mar 7, 2014
I am trying to put this in about 25 cells to point at 25 different files. Basically I am trying to keep an eye on when the files have been updated each morning so that I can then pull off some data from them and who saved it.
I solved the first part (see next post) but I still havent been able to get it a function pull off the username.
View 1 Replies
View Related
Nov 18, 2008
assume we have constant P = 100, K=50 and F=10.
I would like to "lock" these cells, and have a variable which could adjust and this variable would multiplicate each one and put out the number.
For example:
P = 100
K = 50
F = 10
Variable: 10 this would generate a sum of:
P = 1000
K = 50
F = 10
View 3 Replies
View Related
Nov 4, 2009
I am searching for a macro where I can use in a command button to:
1. Launch Outlook
2. Insert variable name from the excel file within the body of the Email.
ex. ...Your new cost is: "NewCost"
View 2 Replies
View Related
May 9, 2014
I need to write a code to create visio flow diagram with excel inputs(Excel 2007). Attached is the requirement.
Requirement_Specification.docx
View 1 Replies
View Related
Nov 12, 2013
how to recode a variable. Suppose I have a variable for Ethnicity and this is a text variable, and I would like to get a numeric variable out of it. I tried to use IF but it works only for one condition or for nested conditions, which is not my case. What I would need is something like= IF(B2=Black,1, IF B2=White, 2, IF and so on)
Is there a way to do this without writing a code in VBA?
View 9 Replies
View Related
Nov 7, 2008
I have big Access file .mdb. I need to work with the file in Excel.
The Excel read the file and I tryed to save the file as .xlsx
I receive a message saying only 64k rows will be saved.
How can I translate the file to Excel 2007?
View 1 Replies
View Related
Jan 29, 2009
I use Excel 2007 which is creating a whole load of tmp files of the spreadsheet that I work on.
Is there a way tostop Excel from creating and saving the tmp files?
View 14 Replies
View Related