I'm trying to figure out how to pass parameters to a date field in a macro to control the display in a pivot table.
The macro currently uses a With statement to set the properties of 177 of the 180 dates to false, leaving the remaining three dates true and visible.
Prior to Excel 2007, the functions provided by the Analysis Tool Pack (ATP) are provided by a separate add-in. You cannot call them using the methods shown above. First, you must load the Analysis Tool Pack - VBA add-in. Note that this is different from the Analysis Tool Pack item. Once this add-in is loaded, go to VBA, open your project, and choose References from the Tools menu. In that dialog, choose atpvbaen.xls in the list of references. Once you have that reference in place in your VBA code, you can call the functions in the ATP as if they were native VBA functions.
View 8 Replies View Relatedhow to pass parameters to a workbook on open? how to trap/activate these parameters deom within the Workbook Open event?
View 4 Replies View RelatedIs it possible to use a macro that needs parmeters as part of an application.ontime instruction? - I am getting a macro cannot be found error message.
If I change the called macro so that parameters are not required, it is found OK (but obviously isn't doing what I want anymore!).
I reckon it is something to do with the quotations around the macro name
I have a data table that contains 180 dates e.g. 9/30/06, 10/31/06 etc. When I'm creating the pivot table via a macro I'd like to have (2) parameters to enter at run time, those being Beginning Date and Ending Date.
View 9 Replies View RelatedI have used my Excel Bible as well as MrExcel.com and tek-tips.com to write the current code I have... but it doesnt work! I think I am just missing a small piece, but I dont see what it is.
I have a macro that calls a userform - from the userform, a Customer is chosen. When the "extract" button is clicked on in the userform, it should pass the customer name to the next macro to do a bunch of stuff - including filtering the data by the customer chosen in the userform.
Here is the code - take a look and see if you can figure out why it doesnt work.
Code for "extract" button (I have tried several differnt variations - this is the current one):
Code: ....
I'm trying to set up a journal-type workbook. I have two sheets named Start and End-the first sheet has a cell that contains a formula, =SUM(Start:End!B4) B4 is where you put the number of hours worked in a day. I then run a macro that copies the sheet called Start for each day of the year. What I was expecting was that the cell with the formula would provide a running total of the hours worked through the year on each sheet, but when the sheets are copied the formula changes to =SUM(#REF!B4). My macro to create a copy sheet for the whole year is as follows:
Sub MakeYear()
Dim SH As Worksheet
Dim D As Date, Y As Long
Set SH = ActiveSheet
Y = Val(InputBox("Year:"))
If Y < 2000 Then Exit Sub
If Y > 2100 Then Exit Sub
Application.ScreenUpdating = False
For D = DateSerial(Y, 1, 1) To DateSerial(Y, 12, 31)
Application.StatusBar = D
SH.Copy after:=Sheets("Start")
ActiveSheet.Range("B2").Value = D
ActiveSheet.Name = Format(D, "mmm dd")
Next
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub
how can I pass a workbook name to a macro I have in a different workbook>? I need to prompt myself so I can type the workbook name and pass it to the macro OR... with the workbook that never changes active, pass the name to the macro of the inactive workbook.
View 4 Replies View RelatedIs it possible to set parameters on a query but use a wildcard to return all instances? I have a query that I want to be able to set multiple parameters on but give the user the ability to select as many or as few parameters as they want to see. 2 of the parameters are number fields and 2 are text fields with no spaces.
View 4 Replies View RelatedIn my code I am using several variables to create a concatenated string and this is making my code quite lengthy..Now I have manged to use it in the SELECT CASE but it still remains big..
SO I was thinking if it were possible to break it into smaller parts and create a Sub-Routine which can be called in the Main code in the select case for the specific condition this way I can use it in Multiple places without hassles..
find the code which I need passing the variables..
Code:
Sub Condition_I()
Dimm Search_Value as string
Dim INo As Long
Dim Whole_Text As String
Dim Lookup_No1 As String
Dim Lookup_No2 As String
Dim Condition_I_Text As String
Dim WksI As Worksheet
[code].....
I need to pass the Search_Value from the Main Code and also the whole_text back to the Main Code..
I am trying to add a 0 if it is missing at the start of the number in columns K2:K3000 and L2:L3000, The numbers do not have fixed lengths so I cant use the custom setting but if the number starts with a 7, 8 or 3 I want to add a 0
View 6 Replies View RelatedExcel Question 1.jpg
I need to have a code that looks up Column 1 (Parameter 1) first, then looks up Column 2 (Parameter 2), and inputs Column 3 (Value). The code needs to be in a SINGLE cel.
I thought initially using a Lookup function within another Lookup function might work, but I can't seem to get the code to work properly.
I'm working on an attendance tracker for church. We will have folks scan a QR code and fill out a 2 question survey: Name of person being checked in and date of check in. Both are mandatory and are set in such a way as to always have the same formatting every time. That's my first sheet, it just gathers the data. My second sheet actually has the attendance tracker with the roll and dated columns. What I'm trying to create on the second sheet is a formula that will search the first sheet for the date and name and return a 1 if they were there that day.
View 7 Replies View RelatedI have a datast that looks like:
ID Week Sales
1 1 $200
1 2 $300
1 3 $200
1 4 $250
1 5 $220
1 6 $100
2 1 $500
2 2 $100
2 3 $230
2 4 $250
2 5 $210
2 6 $110
I will have 3 parameters in different cells that will identify the range of data in which i'd like to get a SUM for:o
ID,
Week,
Number of weeks.
So for example my I would identify ID =1, Week = 3, # of Weeks = 2
I should get an output of: $450 ($200+$250, rows 3+4 from above).
I've tried using IF(AND and Sumifs nested.. but it's not working out..
I am using the following formula and I need to modify it a little:
=IF('Input Sheet'!A17="AR",0,'Input Sheet'!F17)
All I need to do to the above is add "MISC" to the formula. I have tried but I keep getting errors.
Basically I want:
=IF('Input Sheet'!A17="AR" OR "MISC",0,'Input Sheet'!F17)
Is there any way to use an Excel cell as a parameter for MS Query.
i.e. Select fields from table where field = "cell value"
I tried the $ ($D$5 ) got Invalid pseudocolumn error; which leads me to hope there might be a way to introduce a VALID pseudocolumn in my Query.
I have a query that has criteria based on "is one of". I would like to pass this off to a parameter selected from a list box where the user could select multiple items. Is something like this possible?
View 9 Replies View RelatedI need to modify the following formula,
=IF(G5=-105,-105,-G5-IF(ABS(G5)>251,40,IF(AND(ABS(G5)>221,ABS(G5)<251),30,
IF(AND(ABS(G5)>180,ABS(G5)<221),20,IF(AND(ABS(G5)>=100,ABS(G5)
[Code].....
I need to search for 3 parameters, L, M and P based on the *** and age from the attached excel file wtageinf.xls and enter them into the formula below
((X/M)**L) - 1
Z = -------------------------, L≠0
LS
or
Z = ln(X/M)/S ,L=0
where X is the weight of the child. The formula is for the Z score.
I have a spreadsheet in which one column I'm trying to find a value in another table that has 3 columns (A, B, C). I need to find all the values in A, that fulfill the parameters of less than 1.5 in B, and great than 5% in C.
They all need to be listed in column H while skipping the records that do not meet this criteria.
So below is an Sample (Actually about 200 people) of a manually finished product (without names). All parameters must be met.
Basically it will break the Big groups into smaller groups of 4. Assigning the smaller groups a numeric group number starting with 1.
Parameters. No more than 2 members of a club in one small group. No more than 7 lbs between lowest weight member and largest weight member. If it can not complete this, a 3 or 5 (preferably 5) man group can be made.
Sample 3 excel forum.xlsx
I am getting erors when i am trying to use excel parameters in sub query
select gen_fundnumber
from FilteredGen_fundmain
where gen_customeridnumber =
(select distinct gen_customeridnumber from FilteredGen_fundmain
where gen_fundnumber =? )
If I am changing the ? to '123' it works fine.. The second select with the ? parameter is running alone excellent.
How can I get at parameters passed on a command line to Excel 2007?
For example invoking Excel from Vista's Run command with:
"C:ProgramDataMicrosoftWindowsStart MenuProgramsMicrosoft OfficeMicrosoft Office Excel 2007" "C:GZNORPWeather StationParamTest1.xlsm" /e/abc/def
Should reveal the parameters "abc" and "def". But this does not happen!
Supposedly, the API function GetCommandLine does this, but I cannot seem to get it to work. Here's a sample of what I've been trying:
In order to be able to replicate something like the MATCH function, which accepts a String (or cell reference), an Array of Strings (or a range of cells) and then the match type as parameters, how do I declare such a function header?
it is returning #VALUE without executing any of the function. It is something to do with the Array of Strings that is causing the problem.
I've got some data formatted by column in the worksheet that I'd like to pass into the function and have it return something from an array. This is essentially a replacement for vlookup, but easier considering the volumn of data I've got. Take something like this...
Column N will have integers (0 through 20)
I want to pass contiguous parts of N into the function and have it return the corresponding sum of values from the respective positions in an array I defined locally. Here is the function:
I have a long list of past jobs, around 4000+. I have multiple fields, but I really wish to concentrate on the:
1) Job Type - Example would be Medical or Imprint
2) Job # - Correlates when it was done - Example would be 91059 would be a job within 2008-2009 fiscal year.
All the jobs are listed in the first spreadsheet. The second spreadsheet will hold generalized data broken down by the Type of Job and the fiscal year it was done.
I wish to first count how many jobs fit a specific job type. This was easily done:
=COUNTIF(Data!A2:A4121,A7)
A7 = Medical
A1:A4121 = the range of the names.
Count comes up as 346, which is correct.
I then want to add another parameter to break up the 346 by fiscal year. It ranges from 05-06 to 08-09. I came up with this:
=COUNTIF(Data!A2:A4121,A7)-COUNTIF(Data!F2:F4121,"9????")
Since the job # correlates to the fiscal year it was created, anything starting with 9 is a job done in 08-09. Anything starting with an 8 would be of course 07-08 range. I would have multiple fields with each different fiscal year.
However, when I put the 2nd formula in the function didn't work. It keeps the count at 346, which I know is wrong. I am not sure if I did something wrong here. Been looking at this for an hour and can't figure out what is wrong. The jobs that are medical within 08-09 fiscal year should be 120, but it keeps at 346.
Im currently using the below formula but was wondering if its possible to combine the parameteres that im looking up in coulmn L in sheet one so i dont have to do the formula three times? something along the lines of={"P11111";"P22222";"P33333"}?
=SUMPRODUCT(sheet1!S$3:S$10000;--(sheet1!$E$3:$E$10000='sheet2'!$B$2);--(sheet1!$Q$3:$Q$10000=678);--(sheet1!$N$3:$N$10000='sheet2'!$D23);--(sheet1!$L$3:$L$10000="P11111"))+SUMPRODUCT(sheet1!S$3:S$10000;--(sheet1!$E$3:$E$10000='sheet2'!$B$2);--(sheet1!$Q$3:$Q$10000=678);--(sheet1!$N$3:$N$10000='sheet2'!$D23);--(sheet1!$L$3:$L$10000="P22222"))+SUMPRODUCT(sheet1!S$3:S$10000;--(sheet1!$E$3:$E$10000='sheet2'!$B$2);--(sheet1!$Q$3:$Q$10000=678);--(sheet1!$N$3:$N$10000='sheet2'!$D23);--(sheet1!$L$3:$L$10000="P33333"))
I am trying to write an SQL query using parameters in an Excel Worksheet and then return the results to the current sheet ar cell B11. I have found it imposible to make it work in SQL so am trying to run it from VBA to pass the parameters. These are named cells StartDate and EndDate in cells A2 & A3 respectively and should replace the constant dates below. I am working in Excel2003 & MS SQL 2012.
I have tested the query on SQL server with fixed values (which are still in it). It works fine & I have pasted it in.
I took an existing query I found & tried to modify it for Excel2003 but ... Whatever syntax I use, I get a Run-time error 424 when I get to a line with 'connection'
Code:
Sub DateQuery()
With ActiveWorksheet.connections("ndserver01").oledbconnection
' .Connection = "OLEDB; DSN=Excel Files; provider=SQLOLEDB; Catalog=NDM_Sage200;Data Source=ndserver01"
.Refresh BackgroundQuery:=True
.CommandType = xlCmdSql
[Code]...
What have I done wrong
I have some code that works fine below. It deletes an entire row based on the value in column J.
Ideally I would like to give the user the option to choose which column to base the deletion on. i.e. some kind of drop down box or input parameter where they could choose a different column "A", "B", "C" etc...
Private Sub CommandButton1_Click()
'Removes values less than 0
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("J1:J1000"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value)
I have a range of data in columns a-g (the length of which i am unsure of). I have been manually sorting them for the values in both column a and column g, but the data in column b-f are needed for my work. would it be possible in one sheet to have column a "y" and on another column a "N" sorted and then subsequently, the numbers in column g copied and pasted along in rows after so you would have a-g copied in h-n and then for the next number o-u and so on. there are multiple of the same value in column g, and a. would it be possible to make a macro which would sort this for me
View 1 Replies View Related