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 9 RepliesI 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.
I m trying to write a formula for my financial model. If anyone can take a stab at a solution. I'm trying to write a formula that will equally distribute revenue either over the next 1 month, 2 month or 3 month period depending on size of the deal.
Details:
Sales will fit in 1 of 3 categories. Less than 25k; between 25k & 100k; greater than 100k.
- if under $25K, recognize in next month (month N+ 1)
- $25K-100K, recognize in two equal parts in months N + 1 and N + 2
- over $100K, recognize in three equal parts over 3 months
N + 1, N + 2, N + 3 ...
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.
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 8 Replies View Relatedadd parameter to this macro and at the end the process to index the highest result at the end, is that possible?
Here is the macro;
Code:
Sub Macro()
Range(Selection, Selection.End(xlDown)).Select
Calculate
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range
[Code]..
to run 5000 times
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.
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
I have a a table at the top of my worksheet which is a breakdown of performance. (Table 1), i would like Table 2 to roll up performance using a =SUMIFS calculation. However number of rows in table 1 may vary.
View 2 Replies View Relatedis 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)
Last time i got macro from this forum how to import files automatically. I am importing the data from the specific folder.In code itself we are hardcoding that file names.But in that folder i have lot of files is there any option to pass any parameter value.That means each time it will asks for filename(Prompt).Once you give file name it will automatically load the data.
[Code] .....
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"...
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.
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 3 Replies View RelatedHow can I pass a column as a parameter when I execute the sub? ex:
View 2 Replies View RelatedI want to pass the name of the routine as a parameter.
View 6 Replies View Relatedexplain or provide a link to info about the .NumberFormat parameter?
I am using:
What is the best way for my UDF to return an error to the calling worksheet if it detects an invalid parameter?
In the past, I have usually set a breakpoint so I could check the the values and the logic. Other times, I return an invalid result, like 0 or -1.
I am working on a UDF now that is called hundreds of times. The workbook is a work in progress so I am constantly making changes to the UDF and the calling cells. Periodically, I screw up and do something that causes every call to get an error (like divide by zero).
I've written a function to delete the charts on a worksheet: ....
View 9 Replies View RelatedFrom 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
I am running a macro to loop through a list of codes which takes the unique code, goes to a different spreadsheet, finds related data based on that code and if it meets certain criteria, and retursn the same code if it works. Pretty much a filtering process, but I have it returning the code because I do not want to do the calculations in the Macro itself.
Here is the code:
VB:
On Error Goto Label1
Do
Windows("Byron_MonthlyPDPWellList.xlsx").Activate
Range("A" & I).Select
[Code] .....
"I" begins at 2 and counts up until it reaches Count, which is the number of total rows in the column.
However, when I step through the code, it continues to loop through the code where "I > Count".
I have tried "Until I > Count", "Until I = Count", "While I <= Count". And none of them seem to work. It gets stuck in an endless loop cycle.
So for example I Have three suppliers for a given delivery depending on weight to point "A" to "B"
From To AA pallet AA semi AA fullload BB pallet BB semi BB fullload CC pallet CC semi CC fullload
A B 1 2 3 4 3 4 2 3 4
A C 2 3 4 3 4 5 3 3 4
Given weight parameters that
min max mode
0 < 4 pallet
4 =< 6 semi
6 =< 12 fullload
Now I need a a table like this
Weight From to Mode Cheapest supplier pri
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 3 Replies View RelatedI have a formula that hides rows within a range for a sheet.
View 2 Replies View RelatedI 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.
I got a couple of userform and want to make sure all form are closed before opening the next one.
I'm trying to pass the active user form as a parameter to a subroutine that will ensure the form is close before opening the main menu, but I'm doing something wrong.
Code:
Private Sub CmdMainMenu_Click()
Call gotoMainMenu(FrmInventoryMain)
End Sub
Code:
Sub gotoMainMenu(acsheet As String)
Unload acsheet
Unload FrmMainMenu
FrmMainMenu.Show
End Sub
Within a module I have about 10 Label_Click events. Since they essentially do the same thing I want to create a another procedure that these 10 label_click events will call.
This is what the click event on Label2 looks like:
Code:
Private Sub Label2_Click()
LabelClick (frm.Label2)
End Sub
As you can tell I am trying to call LabelClick routine, which looks like this:
Code:
Private Sub LabelClick(ByRef lbl As Object)
...{my code here}
End Sub
But when I run it, it throws an error at event level saying "Type mismatch".
I also tried "Label" instead of "Object" in the signature, but I get the same error.
I'm trying to set my code to force the user to enter in 8 characters for an employee ID within a text box. Currently when I attempt to enter in an employee ID, my message box alert appears after the first digit. So instead of validating the user has entered in 8 characters for the employee ID, the msgbox alert appears as soon as the user enters in the first number. I cannot figure out what I am missing within my code.
Code:
Private Sub txtPERNR_Change()
If Len(Me.txtPERNR.Text) 8 Then
Me.txtPERNR.SetFocus
MsgBox ("PERNR number must be 8 characters in length")
Cancel = True
End If
End Sub
I honestly thought this validation would be easy, but I've tried it a couple of different ways and nothing is working.
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 9 Replies View RelatedI 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 Related