Excel 2003 :: How To Pass A Parameter In The Input Range
Mar 21, 2014I have a combobox in a excell sheet. It is possible to pass a parameter in the input range instead of Parm!$B$1:$B$10
View 2 Replies
ADVERTISEMENT
Pass Parameter From Excel Through MS Query To MS Access Query
Nov 26, 2012I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.
I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.
Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.
If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?
Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.
Macro Sub Pass Parameter
Aug 21, 2008I've written a function to delete the charts on a worksheet: ....
View 9 Replies View RelatedPass A Sheet As A Parameter To A Sub
Feb 1, 2010From 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
VBA To Pass Command Line Parameter
Oct 17, 2008I 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 9 Replies View RelatedPass Combobox As Parameter To Other Functions
Mar 13, 2008Can 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 2 Replies View RelatedPass The Result To A Sub Which Takes An Array Of Dates As A Parameter
Dec 27, 2008I 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 2 Replies View RelatedExcel 2003 :: Protect Textbox From Formatting But Not Input?
Mar 15, 2013Is it possible to protect an inserted text box from being moved, stretched, etc. while allowing data input into the cell/s? I'm using a 2003 version of excel.
I've attached the sheet in question. The yellow text boxes are what I want to lock, yet still allow data input.
excel tip example.xls
Excel 2003 :: Unprotected Cell Won't Allow Input On Protected Sheet
May 19, 2010Using excel 2003, Workbook has several sheets, all sheets are protected workbook is not. I have my cells containing formulas locked, I have unlocked the other cells that require input of various data. When the user goes to type in a "unlocked" cell, (to over write the now obsolete data) it will not allow the user to delete the data, the curser remains a cross and the formula bar or the cell itself will now allow any changes.
I have double checked that the cells are unlocked. I can enter data after the last entry in the unlocked row and or column, but I want to be able to "cut or delete the data that is no longer need and begin entering data from the beginning cell of choice. I want to copy this "old" data to an archive sheet, but it will now allow me to select it.
Example: I have 6 columns starting, (a to f) the formulas are in columns e and f and start at row 4 These columns are locked and hidden. Columns a,b,c and d are unlocked for user input. All data is started from A5 which I want it to start from each time the old data is deleted and new data entered. If I place my curser on any of the cells in the e and f rows, the cursor remains a cross which is what I expect it to do, but columns a to d are doing the same thing, they are acting as if they are locked OR atleast those cells in the a to d areas which have existing data, as I can enter below.
Input Parameter To Set A Limit
Apr 26, 2008How 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"...
Parameter To Reference Certain Cell To Input Date Into URL?
Apr 3, 2014I have a workbook that is pulling data for every hour of the day from an internal website. The macro is built to pull data 24 times (each hour)
ex.(http://url/[""2014/04/01""]/starthour0/endhour1/)
(http://url/[""2014/04/01""]/starthour1/endhour2/)
(http://url/[""2014/04/01""]/starthour2/endhour3/) and so on.
What I am trying to do is set up a parameter that will reference a certain cell (Master!K5) which will contain the date I need to pull. I want to be able to have that cell referenced automatically and input the date for each URL in the macro.
To Use Solver To Determine An Input Parameter Of The BINOMDIST()
Apr 2, 2009I'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.
Count Selected Rows In Sheet (and Use As Input Parameter)
Dec 4, 2009I 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 3 Replies View RelatedExcel 2003 :: Auto-populate Columns From Range 1 And Continue With Range 2
Jan 27, 2013I am trying to automate a process where a series of numbers would get populated according the range values. Also I am trying to get the automation to pick up the next range when finished with first one and continue with the task.
Here's what I have as start info and where I want to get to.
Sheet1
A
B
1
Ranges
[Code] ......
Excel 2003
Pass Input-box String From Sub To Main
Oct 23, 2013how to specify a file extension type for a browseforfolder part of what i've been doing.
It's not calling correctly as i'm not sure how to feed the inputData string back into the main.
Obviously there's probably better ways of doing this, but can my way be corrected easily?
I've been looking into ByVal and ByRef, but have only seen examples goin from main to the sub and not the other way.
Code:
Sub Main()
Dim strFolderName As String
Dim strFolderTest As String
Dim FRCntrHiLim As Variant
MsgBox "before calling inputbox"
Call InputBoxTest(inputData) '
Input Box To Pass Text To Cell?
Dec 31, 2013I have the below code which creates a new template
Code:
Private Sub NewTemplate_Click()
Dim Tsh As Worksheet
Set Tsh = Sheets("TEMPLATE")
Tsh.Copy After:=Sheets(Sheets.Count)
shName = InputBox("Please enter new sheet name:")
ActiveSheet.Name = shName
Tsh.Visible = False
End Sub
What I would like to add to the above code is two more inputbox prompts, the first should prompt the user for a Name (text and length) to go into the new sheet cell A6. The the second input box should as for code, (number any length), to go into the new sheet cell b6.
Setting Parameter For One Column And If True Then Check Line Above And Below For Another Parameter
Jul 31, 2013I though I could do this with a nested IF statement but it is too cunfusing for me. What I am trying to accomplish is this:
Experiment
Is Steward
EU ID
Location
Data Quality
GE
Entry Order
[Code] ........
I want to have a screen pop-up asking me what my limit < would be for column "ESTCNT" so if I put in 25 or any other number that it would highlight all the rows that are less than 25, then look at the row above and below and if it matches the same number (that is in the cell "Range" of the highlighted column) in column "Range" then copy that row to a new sheet. Meaning all tha rows that match the "Range" would be in the same new sheet.
The rows might be different lengths and that there will not always be a number in cell "ESTCNT". Column headers will always be the same but might not be in the same column each time. And if it is not to hard once it is completed to find column "SPPLOT" in the new sheet created and asking what I want to autofil the column with.
Excel 2003 :: Copying Range Of Cells From One XLS File To Another
Nov 5, 2012I'm using Excel 2003. I've got two different .XLS files, each with multiple sheets.
I'm trying to create a macro which will copy a range of cells from one sheet on one .XLS file (which is closed) to a specific place on a specific sheet on the current .XLS file (which is open).
So for the sake of argument:
I've got two Excel files: C:ApplesOldFile.xls and C:OrangesNewFile.xls
OldFile.xls is closed -- NewFile.xls is open and in front of me.
I'm trying to copy the data in ranges B6:C41 and F6:F41 from Sheet2 in OldFile.xls to the same ranges on Sheet6 in NewFile.xls. There are no formulas in these cells -- just data (numbers).
I keep getting error messages, failures to copy to clipboard, etc.
Excel 2003 :: How To Use Countif For Multiple Range And Criteria
Jan 1, 2014How to use countif in Excel 2003, for multiple range and criteria ? Can 'Nested ifs' be used? If so, a sample of such ifs
View 3 Replies View RelatedExcel 2003 :: Adding Values If Column Next To It Is In Certain Range
Sep 9, 2013I have this code working fine in excel 2007 but I need to get it working in excel 2003.
=0.02*(_xlfn.SUMIFS($D$2:$D$98,$C$2:$C$98,">="&I5,$C$2:$C$98,"<="&I4))
I have attached a test document to show what I'd like to do. It is basically adding values if the column next to it is in a certain range.
counting columns in Excel 2003.xls
Excel 2003 :: VBA To Add Worksheets Named For Items In Range?
Jan 17, 2012I some code that reduces a long list of dates from one source of data (in its own worksheet) down to a unique list of dates (on another worksheet). I need to create a new worksheet for each date in the range (named for the date), and then allocate each line item for that date in the source data into its repsective worksheet.
I'm using Excel 2003.
Excel 2003 :: Dynamic Range For Pivot Table
Jan 21, 2013How do I create a Named Range that is dynamic (I think using the OFFSET function) that automatically expands and contracts as the rows and columns of my data changes?
Next step, I then want to use that Named Range as the source of the data for a PivotTable.
Yes, I know starting in Excel 2007 you can use Tables and don't need to create a dynamic named range. That is not an option at this time.
Excel 2003 :: Count Where Value In One Cell Range But Blank In Another?
Nov 21, 2013I am trying to perform a count against two ranges of data.Both ranges contain contain values (dates in my case).I am trying to perform a count where cells in column A have data but cells in column B do not.
Is there a way this can be done in Excel 2003? I have tried numerous COUNT and SUMPRODUCT queries but have not found a way to get this to work.
Excel 2003 :: Sum Data Depending On Week Range
Jun 26, 2014Objective: I have a financial spreadsheet, which i want to bring to my front sheet the sum of data in between 2 week ranges. For example, if I select week 26 and 52 then all the relevant data will be summed into the relevant cell on my front sheet.
Current Technique: I have on my "data" spreadsheet my columns in B1 down listing Week 1-52 and then the cell headings in B2 onwards. I have created a summary table which currently shows the 4 quarters of the year (1-13, etc) and I am using helper columns to pull this data onto my "front sheet", depending what selection is made on the drop down e.g. 1-13, then all summary data will be dragged onto my front sheet.
On the front sheet, in each cell I need information to be displayed I use the following formula;
"=IF(ISERROR(INDEX(Data!$C$56:$BF$60,Data!$BI56,COLUMNS('Front Sheet'!I13:J13)))"
As you can see the quarter data is useful, but a more flexible date range would be more useful to sum data between any selected week.
Excel 2003 :: Average Of Cells Which Are Not In Range Which Ignores Any Zero Values
Dec 4, 2013I have a worksheet which has various figures for each day of the week however I need to establish the weekly average of these figures.
Due to the way in which the figures are displayed, I am unsure how to use a formula which does not require a range with cells located adjacent to one another.
I have attached a test sheet as an example. The cells in yellow require the formula and I need a weekly average for criteria 1-3. This formula also needs to be compatible in Excel 2003
Test Sheet.xls
Excel 2003 :: Counting Occurrences In Two Columns Involving Range?
Jan 29, 2014I am using Excel 2003 and I need to count occurrences involving two colums like this: on sheet "SALES", column I has values ranging from 8.00 to 10.00; column M has positive and negative values.
formula to count how many numbers in column I exist between 8.00 and 8.99 associated to a positive value in column M? And likewise for negatives?
Excel 2010 :: VBA Input Box For Range Selection
Sep 6, 2012Excel 2010. I have a macro, stored in a workbook.
The macro is: collecting data from another opened workbook,processing the data,saving the processed data in yet another workbook.
The workbooks' names it is operating on are hardcoded in the macro. I'm trying to make it more flexible, that is to allow user to specify both source and target workbooks along with the data columns.
The workflow follows:
User opens both source and target workbooks,User opens the workbook with the macro,User runs the macro (with a button for example),Macro prompts the user to select range in the source workbook,Macro "memorizes" the range and the source workbook's name from user's selection,Macro prompts the user to select range in the target workbook,Macro "memorizes" the range and the target workbook's name from user's selection,Macro processes the data.
I found out that this can be achieved with Application.InputBox("Select cell(s)", Type:=8) method. However there are some issues that I was not able to solve playing with the returned value's methods:
The .InputBox defaults to active workbook. I was able to select another opened workbook only with Ctrl+Tab combination, but it will not work with the end users - they are too poor with these tricks. Is there a better, more intuitive way to allow user selecting a workbook?I was not able to get the workbook's name from the .InputBox returned value property. I was trying with .application.caption and .application.activeworkbook.name - none of these worked. They returned name of the workbook with macro, not the one with selected range. How to get the workbook's name from given cells range?
Excel 2013 :: Get Range Of Results From Input
Aug 31, 2013Here is an example of what I am trying to do in E2013.
The orange Bin is my input. It want that to return the column associated with the # as show in orange output column. I think this is an index command? I cannot seem to make it work.
Also - is there an easier way to insert sheets other than a screen grab and photos?
Excel 2003 :: Include Text Criteria In Date Range Formula
Dec 20, 2012Having a hard time putting this one together..Trying to do: Create a formula that counts how many cells in Column L, that fall within a date range and also have a specific category of "text" (Column E). What I've tried:=COUNTIF($L$4:$L$166,"
View 5 Replies View RelatedExcel 2003 :: Cell Result Based On Range Of Filled / Empty Cells
Apr 3, 2014I'm stuck using Excel 2003 to auto-populate a cell.
I have a range of dates in five consecutive columns called:
Phase 1, Phase 2, Phase 3, Phase 4 and Phase 5
I enter the date that 'Phase 1' starts under the first header. Once Phase 2 starts I enter a date under 'Phase 2', and so on to Phase 5.
Each phase is consecutive to the next so will always be filled in from 1 to 5.
I want to create an additional column called 'Status' that shows the Column Title of the last phase with a date in it. For example, if Phase 1 to 3 had dates but 4 & 5 were blank, "Phase 3" would be displayed in the 'Status' column.
I've tried nesting some ISBLANK functions without any luck.