Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Pass A Sheet As A Parameter To A Sub


From Macro1, I want to pass a reference to a sheet. In Macro 2, I want to select that sheet. Here's what i have so far but I'm getting a "subscript out of range" error

Sub Macro1()
Macro2 "Sheet1"
End Sub

Sub Macro2(sheet As String)
Worksheets(sheet).Select
End Sub


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Macro Sub Pass Parameter
I've written a function to delete the charts on a worksheet: ....

View Replies!   View Related
Pass Combobox As Parameter To Other Functions
Can I pass Combobox as a parameter to other function. If yes then how? I can pass Combobox1. Name as a parameter to other function but inside that function I cannot use combobox("Comboboz1.Name"). I don't know the syntax to work on the combobox whose name is passed as a parameter.

View Replies!   View Related
VBA To Pass Command Line Parameter
I found the following link that got me started [url], so now I have my code for reading/analyzing comand line parameters. Here is an example of the parm I'm passing in ....

View Replies!   View Related
Pass The Result To A Sub Which Takes An Array Of Dates As A Parameter
I delcare an array of dates and assign it with a function that returns an array of dates. Then I want to pass the result to a sub which takes an array of dates as a parameter. However, I am getting a type mismatch error on this line

View Replies!   View Related
Count Selected Rows In Sheet (and Use As Input Parameter)
I have a macro that adds a row with predefined formulas and formating. The macro is launched by clicking on a button. However, I would like to make it possible to add more than one row at a time. My plan to do this was to use the number of selected row as input to the current macro. If the user selects row 1,2,3 and 4 (or 15, 16, 17 and 18, and so on) four new rows should be added. I would just add;

View Replies!   View Related
Pass Value Of Range From Another Sheet
I am using a spreadsheet with two sheets, basically, one sheet has a combo box control, you select a name from there and click on "Go". The function is supposed to go into the next sheet (Sheet2) and retrieve a value from there (in the same row as the selected name).

I am using the following code (on button click):

Dim strValue As String
Sheets("Sheet2").Select
strValue = Range("A1").Value
MsgBox (strValue)

I've simplified this, but essentially my problem is this - it isn't picking up any value at all - there is text entered in A1 so what is the problem?

View Replies!   View Related
Pass Active Sheet Name To Label
Is it possible for when a sheet tab is selected to have the sheet name passed to a label caption?

View Replies!   View Related
Change Sheet Name In Cell And Pass Through Formula
I can't use code for this problem as I need to give this to someone who doesn't know VBA. They will need to use it in several different reports, so I can't produce something in VBA very easily. I have three worksheets, Summary, Year1 and Year2.

I want to display either Year1 or Year2 data in the Summary sheet depending on what the user enters in a cell in the Summary sheet. For example, if the user enters "Year1" in cell E2 then I want to point to a cell in the Year1 spreadsheet.
I tried using formula: =E2 & "!B3" but this doesn't work. Is there another solution? I have attached an example.

View Replies!   View Related
Using Combobox As Parameter
I have a function which has to contain the name of a combo and use it with some given parameters, here's my example: ...

View Replies!   View Related
Parameter Query
Here it goes.

My spreadsheet is populated by data coming from MS Query, i'm entering a parameter value to display the desired data in my spreadsheet. My problem is, i have to close and open the file to have the parameter prompt so in that case i can enter the parameter value.

Is there anyway to call the parameter prompt so i will not open and close the file, its really time consuming...

If possible, i just want a command button that calls the parameter prompt.

View Replies!   View Related
Parameter Queries In Vba
I want know how to pass a parameter through a cell for date in the following url

http://fc-web-phl1-101.phl1:8090/gp/...runReport.y=10

View Replies!   View Related
Send Parameter Into Macro
I have a macro in which I will trigger it to run automatically, under windows scheduler. Eg:

"D:DataMacro.xls".

I am expecting to pass parameter in such way, which I did for my other exe application.

"D:DataMacro.xls" parameter_1.

I want the macro to read the parameter_1 in such way.


View Replies!   View Related
Passing Subroutine As A Parameter
I want to pass the name of the routine as a parameter.

View Replies!   View Related
Incrementing An Alphabetic Parameter
I am running a macro where I pass it starting column and it processes the next 10 columns. How can I pass it "J" and have it increment K,L,M,N,O,P,...?

View Replies!   View Related
MS Query Parameter With Like %'s
I can run my query from within MSQuery something like

like %RESISTOR%

and it works fine.

However I cannot use %RESISTOR% on the excel sheet as a parameter.

is it possible to update a query using a parameter in this way? Maybe getting VBA to actually update the query manully.

View Replies!   View Related
Get Information About The .NumberFormat Parameter
explain or provide a link to info about the .NumberFormat parameter?

I am using:

View Replies!   View Related
Name Of Day/Month Parameter
Looking for a formula that gives the 1st - 5th day of a month, using the day name, e.g. "First Monday of March 2009" returns "2". Even better is if it could accomodate previous and following months, e.g. "First Monday of Next Month" returns "6". 3

View Replies!   View Related
Passing Column As Parameter
How can I pass a column as a parameter when I execute the sub? ex:

View Replies!   View Related
Match Function Value Parameter
is there anyones know what's the meaning of 1 in this formula
MATCH(1,('Data Issues'!$A2=Sheet1!$A$2:$A$68)*('Data Issues'!B2=Sheet1!$B$2:$B$68),0)

View Replies!   View Related
Filter Parameter In Excel For MS Query
I'm trying to create a report for a coworker to run. I want to filter the parameter in a cell (ssn), so that she can run the report. I have added a button for her to execute..once she puts in teh ssn. The spreadsheet is linked to MS Query in the background. How do I tell the Query to link to the cell in excel for filtering?

View Replies!   View Related
Tranferring From A List To A Two Parameter Table
I need a two parameters table deriving data from a list in another sheet. The vertical column of the two parameters table will be the names of the personnel, and the horizontal one will be the dates, in weeks form. The list contains the names, flight departure, flight arrival and no. of working days spent in the foreign country. What I need to do is to tranferr the data from that list onto the table.

For eg, if john flew overseas on 20-Dec to 15-Jan, the list will show john on column A, 20-Dec in column B, 15-Jan in column C and 20 working days on column D.

This list of personnel and their data should go into the two parameters table, where it will be identified when the personnel will be overseas according to the calendar of the project. Since the vertical heading is the dates of every week, a date that corresponds to period where the personnel is overseas will reflect a number in the cell. The number will be the number of working days the personnel spent on the foreign country, therefore the max will be a 5 and minimum is a 0. I need a formula that will fill every cell within the area of the tables.

View Replies!   View Related
Range As Parameter In Function Declaration
I have a formula that hides rows within a range for a sheet.

View Replies!   View Related
Input Parameter To Set A Limit
How do I adjust the VB code below so when the user clicks the command button they are prompted to set a limit.
i.e the code only runs til it hits the limit and stop (instead of running until it there are no more x in the columns)

Row 2, Col 9 stores the column numbers (listed as 1, 2,.....100) so thought this could be used in some way.

1. how do i set up the code so a user can enter a parameter value e.g 50 (which is the limit but also includes the values in column number "50")

2. how do i adjust the code so it take the parameter value and stop when it reaches column number "51"...

View Replies!   View Related
To Use Solver To Determine An Input Parameter Of The BINOMDIST()
I'm trying to use Solver to determine an input parameter of the BINOMDIST() formula that provides the exact answer I want. It works for some inputs and outputs but not for all.

For example, I want 4 successes out of 7 trials to yield a 95% answer, meaning I need to itterate on the "P" input in the formula [=BINOMDIST(4,7,"P",TRUE) should result in .95]. Using solver it iterates successfully to .3413.

Now, change 4 to 6, and solver drives "P" to an answer that causes a #NUM result (P= 4.023), however I can manually input 0.6518 and I get the right answer.

I've tried adding constraints that won't let the input go outside the bounds of 0 and 1.0 but it does anyway.

View Replies!   View Related
Oracle Date/Time As A Parameter In MSQuery
I am attempting to use MSQuery to extract records from an Oracle transaction table by passing user defined Date/Time field parameters.

So if the date range for the query is for Fiscal Period 10 of this year, my parameters would be:
[StartDate] = #2002/10/1#
[EndDate] = #2002/11/1#

Daily transaction activity is in the early morning hours ending at 6:00AM so my Criteria is:
Between [StartDate] + .25 and [EndDate] + .25
Note: The +.25 represents 1/4day = 6hrs (6:00AM)

If I use the hardcoded Start/End Dates, my query works fine, but if I try and
pass the dates as parameters I get "ORA-00932 inconsistent datatypes" error. And I can't seem to pass the Date/Time combination successfully....

If anyone has encountered a solution for how to pass a Date/Time Paremeter into MSQuery I would love to hear how you did it.
I'm starting to pull my few remaining hairs out!

[ This Message was edited by: Tuner on 2002-11-07 12:23 ]

[ This Message was edited by: Tuner on 2002-11-07 12:28 ]

[ This Message was edited by: Tuner on 2002-11-12 12:51 ]

View Replies!   View Related
Microsoft Query - Return All Unless A Parameter Is Entered
I am querying a database in Microsoft Query and I have it set to pull in jobs that match a customers code in cell A1. I would like it so that if cell A1 is blank, it pulls in jobs for every customer.

View Replies!   View Related
Web Data Connection Parameter Problem
I'm having problems with date parameter for a web connection. I can provide the parameter and type the parameter via message box ... but when I want to reference a cell the query errors out.

View Replies!   View Related
Vba Calling An Access Query With Parameter
i have code accessing an access database running a "query" which has been defined in the database itself. i asm using ADO - no issues with this. works very well.
my problem arises when i change the query to take a parameter. this works ok when i execute in access but not with the code i am using for VBA / ADO. i have tried several techniques but none seem to work.

Dim rsReport As New ADODB.Recordset
Dim cnReport As New ADODB.Connection
Dim cmdReport As New ADODB.Command
Dim prmReport As New ADODB.Parameter
cmdReport.CommandType = adCmdStoredProc
cmdReport.CommandText = "testQuery-withParam"
With prmReport
. Name = "FullName"
.Value = "joe bloggs"
.Type = adChar
.Size = 8
.Direction = adParamOutput
End With................................

View Replies!   View Related
Application.ScreenUpdating Parameter Doesn't Work
I am trying to run a macro in the background. I have added the Application.ScreenUpdating = False (and True at the end) to my code but no luck.

View Replies!   View Related
Count The Nos Of Cells After Selecting Parameter In Autofilter
I want to count the nos of cells after selecting some specific parameter in autofilter. I thought this is a simple problem & searched here in various thread but nowhere I found good solution which works nicely. I have attached a excel file where I applied autofilter in the column C & I need to count the nos of cells when I select specific parameter in filter in Column C.

View Replies!   View Related
Formula Requires Parameter In Quotes, Indirect Doesn't Work
I've been given a formula to use (embedded in an add-in that is password-protected). One of the parameters REQUIRES quotes.

The formula goes like this: =MYFORMULA(1,2,3,"ABC")

If I put ABC in a cell (say C5) and use the formula =MYFORMULA(1,2,3,INDIRECT(C5)), the formula doesn't work.

If I put "ABC" in cell C5, the indirect function still work make the function work.

Even if I have ABC in cell C5 and use =MYFORMULA(1,2,3,""""&C5&"""") or =MYFORMULA(1,2,3,""""&INDIRECT(C5)&""""), these won't work.

I wish I could just change the UDF behind this, but that isn't possible.


View Replies!   View Related
Find Matching Document Value And Max Date Using Function Based On Certain Parameter
I have the following info in a table:

name | doc date | doc value
a | 2009/01/01 | IN111
b | 2009/02/04 | IN222
c | 2009/02/05 | IN333
a | 2009/01/05 | CN111
d | 2009/03/01 | IN444
b | 2009/03/01 | CN222
a | 2009/04/01 | IN555

Firstly, I need to find the LAST DOCUMENT DATE for "a" where #doc_value starts with IN*** (invoice). Manually, I can see that it would be "2009/04/01", but my spreadsheet runs 6000+ entries. I need a function to do this.

Secondly, I need to find the corresponding #doc_value for that date (in this case, IN555).


View Replies!   View Related
Multiple Parameter Lookup For Multiple Table Ranges
In the attached file i have multiple tables for different types of conservatory roofs (16 of them in total). The ranges at the top and side relate to milimeter measurements and the data in the middle relate to the price for that sized conservatory roof. The table works where the two ranges intercept each other. I have a formula to do this for one of the tables only. What i would like is a way of choosing which type of roof to use (i.e. which table to use) and then to be able to input the measurements and the price to be displayed. All of this needs to be done in one query so its as user friendly as possible. i've had is to use a pivot table, i feel it is not possible to use a pivot table to do this sort if thing after research into them, although i am un-familiar in the making of them

View Replies!   View Related
Pass Argument To A Sub From Another Sub
The second subroutine below selects a range of cells, in this case a column. It selects a range that ends with the last cell that has data and starts with either the first cell that has data or the second cell has data (needed for instance if the first cell is just the heading and doesn't contain data). You'll notice I have hardcoded the "includeFirst" option right now. I don't know how to pass this as an argument when calling this subroutine.

In the first subroutine updateAll() I need to call the second subroutine and want to pass an argument to it. Maybe my approach using subs and application.run is the wrong way to do this?

Sub updateAll()
includeHeader = False
Application.Run "SelectColumn"
' need something like:
' Application.Run "SelectColumn(includeHeader)"
' where subroutine below has something like:
' Sub SelectColumn(includeFirst)
End Sub

Sub SelectColumn()
'Select a Column or Row of Cells in a Used Range Quickly
'http://msdn2.microsoft.com/En-US/library/aa155432(office.10).aspx
includeFirst = False
'includeFirst = True
Dim UpBound As Range
Dim LowBound As Range
If ActiveCell.Row > 1 Then
If IsEmpty(ActiveCell.Offset(-1, 0)) Then
Set UpBound = ActiveCell........................

View Replies!   View Related
Pass Value To Macro In Another Worksheet
I have one worksheet with a macro which calls a macro in another worksheet. I would like to pass a variable from the first worksheet to the second worksheet.

Worksheet1 macro includes the following line of code to call Macro3 in worksheet2:
Application.Run "'Worksheet2'!Macro3"

Worksheet2 has the following code in Macro3:
Do While response 1 And response 2
response = InputBox("Would you like to print your report on one page or two pages?", "Select number of pages", 1)

[rest of code]

I would like to be able to pass a value of '1' to Macro3 and bypass the message box when Macro3 is called from Worksheet1. However, if the user has Worksheet2 open, macro3 should display the message box.


View Replies!   View Related
Pass Variable Between Modules
I created the following sub to signal when a macro in Module 1 is complete:


Public Sub Done()
Dim complete As Boolean
complete = True
End Sub
I placed this just before the end sub in the macro for which I am trying to detect that it has finished executing:


Call Done

End Sub
In the Sheet 1 Module, the code fails at the statement:


If complete = True Then
The error returned is "Variable not defined." All three subs are declared as Public. Why does the Sheet1 sub not recognize the variable "complete" from the Module 1 macro?

View Replies!   View Related
Pass Keystrokes To Another Program
Is it possible to write a VBA macro that passes keystrokes to another program? I have a spreadsheet with over 650 UPC codes for new products that have to be entered into our inventory system. I have downloaded and tried a few macro recorders that record keystrokes and mouse movements, but find them to be unreliable at best.

When I use the macro recorder to automate a task in Excel, (such as moving data from one sheet to another or importing data from a text file) I always go in after I'm done recording and add additional code to bullet-proof the macro (make sure the focus is set properly, etc.). I need to have this kind of control for this task and a macro recorder doesn't give me that (at least not these lower-end shareware programs I have been trying). I know that there are some higher-end macro recorders that include scripting that you can write and edit, but this our "slow" season business-wise and my supervisor has made it clear that he doesn't want to spend any money right now.

My task is simple (just very repetitive, which is why I don't want to spend all day sitting in front of my PC doing this):
1. Open Excel spreadsheet, go to starting data cell
2. Open 3rd party application and go to the appropriate data entry screen
3. Write a macro that does the following:
- checks to make sure the sheet is the active sheet
- copies contents of the active cell
- moves focus to other app and pastes data from active cell
- moves focus back to spreadsheet, copies the contents of the cell to the right (using ActiveCell.Offset?)
- makes other app active and pastes data
- repeat copy/paste for the next cell to right (in spreadsheet)
- press twice to save record before returning focus to the spreadsheet
- move cursor down one line, repeat entire process until active cell is blank

Is this possible to do? The third party program is not able to listed as a reference in VBA, but I didn't know if it was possible to at least pass keystrokes to it. It is actually a terminal emulator (Anzio Lite). I guess the full-blown version of the program has some scripting capabilities (from the documentation I've read), but the Lite version does not.

View Replies!   View Related
Pass Date Value In Sql
Iam trying to pass a date value in a query to fetch data from a excel sheet and write the output in a different sheet.Iam able to get the output if I pass the data directly but unable to store the value and pass it in the string.

With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & XLS_Path & _
"PZN-MTD-Opened_Header_Extract.xls;Extended Properties=Excel 8.0;"
CursorLocation = adUseClient
.Open
End With

Set rs = New ADODB.Recordset ........................

View Replies!   View Related
Pass Workbook Name After Save
I'm trying to develop a macro to perform some actions on a workbook after it's been saved, this using the ontime method, along the lines of: ....

View Replies!   View Related
Pass A File Name As A Variable
I am calling a sub that I want to do a SaveCopyAs using a variable as the file name but can't get it to work. The file name displays properly in a message box but when I do a SaveCopyAs it does nothing.

This code works:

View Replies!   View Related
Pass The Variable In The Range
I want to work on a range

range("A1:B100")

The number 100 (in B100) comes from another varibale M

Now how do I write the range so that I don't have to write 100. I want to pass on the variable in the range.

the code should look somewhat like

Range("A1:BM").

I don't know how to pass this variable M onto the range.


View Replies!   View Related
Get Range To Pass Into Function
I am attempting to call a Function from a cell and pass it a range and an Integer. The Integer works fine, but I cannot get the Range to pass into the function correctly.

View Replies!   View Related
Pass Array Byval
Is it possible to pass a typed array byval into a sub of function?

i.e.

Sub test()

Dim arr() As Integer

Call testfunc(arr)

End Sub

Function testfunc(ByVal arr As Integer)

End Function

View Replies!   View Related
Pass Array To A Class
I have a class

Private MemoryArray() As Variant

Private Sub Class_Initialize()
Redim MemoryArray(0) As Variant
End Sub

Public Sub ReplaceMemory(GivenArray() As Variant)
Redim MemoryArray(UBound(GivenArray)) As Variant

For Index = LBound(GivenArray) To UBound(GivenArray)
MemoryArray(Index) = GivenArray(Index)
Next Index
End Sub

that I am specificall passing an array to the replacememory sub. So in the program I have a global array doved criteria:

Dim Criteria() As String

Sub Product2()
'fill criteria with various entries
'do whatever in macro sub

Set MemoryCriteria = New Memory
MemoryCriteria.ReplaceMemory (Criteria)

and on the last line I get an error message:

Compile Error:
Type mismatch: array or user-defined type expected

View Replies!   View Related
Pass Variable From MS Word
It is possible to define a variable in a MS Word macro and send it to an Excel spreadsheet?

View Replies!   View Related
Pass Value From Function To Procedure
If I have code like this

Private Sub cmdHex_Click()
Dim HexNum As String
HexNum = Me.txtHexVal
'Call a function
ToBinary (HexNum)
'Now do something else
End Sub

And the function ToBinary creates a value called BinVal. How do I pass it back to my subprocedure to do something with it.

View Replies!   View Related
Pass Arguments Between Subs
I am still learning and was wondering what is wrong with this. I have cut some of the code out as it pretty long. This is my main procedure:

Public Sub cal_parameters_Click()
'istat(i) = 0 bonded
' = 1 slip
' = 2 soil yielded

Dim istat(1 To 999) As Single

Call secondary_loop 'calls subprocedure secondary_loop
End Sub

It then calls secondary_loop:................

View Replies!   View Related
Pass Parameters To Sub From Worksheet
I am calling a XML Web Service within a function. The Web Service returns a XML grid of data. I need to populate cells in my worksheet with this data, but a UDF won't allow me to edit any other cells. I tried changing the function to a sub, but I need to pass parameters to the Web Service.

View Replies!   View Related
Pass Value: Last Used Cell In Column
I want to use the value of the last cell as a starting value and add a number to it and assign it to the next cell.
i have this

Sub a()
For i = 15 To 100
Cells(i, 2).Value = Cells(i - 1, 2).Value + 6
Next i
End Sub

how do i incorporate this

Sheets("Sheet1").Range("A").End(xlUp).Offset(0,0).value

into the code above so that the value of the last cell can be the starting value.

View Replies!   View Related
Pass Variables Between Userforms
I have a userform with a button that wil run macro "sub1". If "sub1" encounters an error on a worksheet it wil show a custom
error messagebox(a userform) that contains the option "Don't ask me again". If true it sets a global variable. Do I need to use global variables to pass to the original procedure "sub1" for the ok/cancel buttons too or is there an other method?

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved