I need to have an inputbox for users to enter multiple store numbers. What I need to do is actually filter out the store numbers that was entered into the inputbox
my code so far is:
'Inputbox to filter out new stores
Newstore = InputBox("Enter New Store Numbers seperated by a space", "Enter New Stores", "119 120")
x = Split(Newstore, " ")
For i = 0 To UBound(x)
Range("A1").CurrentRegion.AutoFilter Field:=2, Criteria1:=Array(x) _
, Operator:=xlFilterValues
Next i
I have the inputbox so i can set a string value, When the inputbox Cancle button is pressed i want to exit sub, If the inputbox value is nothink and ok button, I want the msgbox displayed then goto newname. If the inputbox has a value do >>>>>>That>>>>>
I have data that I calculate by month and at the bottom I calculate a weighted average. However, I want to exclude the last two months (data points) because they are not mature.
So every month when I update the spreadsheet, it would be the two newest data points to exclude.
I'm running a query of 30 and 60 days worth of data but I don't want certain dates to be included in the queries. How do I exclude certain dates and at the same time pull in day 31 to include this in my 30 day high or average etc.?
I want to exclude 1/15 because it is a holiday and it just repeats the data from 1/12. So I want it to pull in the data for 1/12 if I were doing a 5 day query. As I said before I need to do a MAX, MIN, AVERAGE and STDEV of 30 and 60 days but my formulas are skewed because of the holidays.
I have a worksheet, attached, that is a supplier response to a purchase order. Column E contains the original qty ordered of each part number, Column H contains the original price. Now, I've created a quick macro, (in the workbook), that copies the original values to the confirmed values, and the formulas in columns G and J calculate the qty backordered, or flags the part as a price change.
After copying the original PO values to the confirmed values, the supplier would need to go in and make whatever changes are necessary. For example, we ordered 3 and they shipped 0 of a certain part, or the price has changed.
What I would like to do is create a macro that pops up an input box where the supplier enters the Keystone part number, (column A), then enters the new quantity or new price, and those items are automatically changed on the response form. I picture it in the form of 3 input boxes, in the first box the buyer enters a part number. A second box pops up asking if it's a qty change or price change. They would enter the value in a 3rd box, and that value would automatically be changed on the response.
I want to use the SaveAs function using the Inputbox results. The user will input a date like 5-30-06. Then I want save the workbook as "WE5-30-06.xls". I can't figure out how to do this b/c it wants to use 5/30/06 which isn't allwoed in naming conventions. BTW the inputbox name is dWeekend.
I am trying to exclude unusable data from my analysis. I have flagged data in the flag column on sheet 1 by a "1". A "0" is good data. On sheet 2, I have counts for the amounts of a certain code per partner. I used a sumproduct formula for the counts, but now I want to exclude all data with a flag "1".
I have found a base macro that has most of what I want the only problem is I need it to search column A by the data entered by the user in the inputbox and send out an email if it meets all the requirements. I also need it to be able to be able to do that search by only the first letter of the company name (example, only needing to put in A in the inputbox for Amber Inc.). If I remove the red text the macro works perfectly but just runs down the list on the spreadsheet.
Column A has company names, Column B has email address' and Column C has the company contacts first name.
I have a column that contains combined data of numbers and letters. I want to filter that column to exclude cells that contain the letter "d" or "f". I am trying to create a pivot table from the results of the filter to only display the ID #s that do not contain the letters "f" or "d".
The data inside the fields is not the same length and is not listed in a particular order. I have over 14,000 records I need to filter.
Data set example: The items in red are an example of the cells I would like to exclude from my worksheet. Is there a way to filter without deleting the data?
I have a sheet for addresses (Column 1 = Name, Column 2 = Address line 1, Column 3 = City, Column 4 = Postcode) and I run a macro that Filters that data based on the the city to different Tabs. This works fine except that sometimes, once the data is filtered I will find one or two addresses on my new filtered sheets that I really wanted to exclude. As my main address Tab information changes regularly, Ideally, I would like to see the Item on the City filtersheet, Double click it, and this would have the effect of deleting it from the City filtersheet but also copying it to an "Exclusions" Sheet which I could use before I run my normal filter macro to filter out these bad addresses.
I use a SQL query to export a subset of data from our server. After manipulating the received data I need to Average one column that has been sorted into a number of blocks consisting of variable numbers of rows and post the result to the ajacent cell. I have tried the Countif worksheet function but am unable to resove the syntax problem.
Sub SumBlock() Dim First_Row As Long Dim Last_Row As Long Dim iTotalRows As Long Dim iCount As Long iTotalRows = Range("A65536").End(xlUp).Row First_Row = 2 Do While Last_Row < iTotalRows - 1 Last_Row = ActiveSheet.Range("j" & First_Row).End(xlDown).Row iCount = Application.WorksheetFunction.CountIf(Range("J" & First_Row), ("J " & Last_Row), ">0") ActiveSheet.Range("K" & Last_Row + 1).Formula = "=Sum(J" & First_Row & ":J" & Last_Row & ")/iCount" First_Row = Last_Row + 2 Loop End Sub
Here is my delimma. I am using the standard InputBox for my users to input the number of errors found for each category in a record from a daily report. The problem I having is that every time the user types in data using the InputBox it overwrites the previous days numbers. I need to set up the InputBox code so that after the user has input the numbers for that day that the next time the InputBox is used it selects the next column to the right and continues to do this for each day.
I'm trying to set up a diet/training spreadsheet so that when a client enters foods and exercises they don't want/can't do, those foods and exercises are made unavailable in dropdown menus (on my programming sheet) so that I can't inadvertently put them into their plans. I have a webform they fill out, which automatically puts the data into a spreadsheet and I know how to pull data from there into the clients' workbooks...
I am using Excel 07 with Vista, and creating a spreadsheet in which users will be inputting a record into every row.
There is a row that requires a number to be entered, where some of the numbers will begin with 0 or 00 or even 0000. I want to make sure no-one incorrectly enters a letter, however with data validation set to whole numbers, it formats the cell to change an entry of "005667" into just "5667". There is no set length for the field.
The only way to have "005667" is the change the column to text, but then obviously that defeats the whole purpose!
I have another dilema. I am writing a macro that essentially takes a template from a workbook and copies it to another workbook that already has data. the data is then imputed into the pasted template through the macro... my dilema is that I have numerous data sheets so at the begining of the code I need the macro to pause so the user can manually select the cell or range that the copied template is to be pasted too... I was thinking an inputbox would work but I am unsure how to write this part of the code.
I use currentRegion to add the item to the combobox, but i don't know how to add without including the first cell in the range,as usually, it is the field name.
The report is a "template" that will be ran once a month every month and will look back at the previous month's data. What i've invisioned is writing a vb script to load on workbook open asking various questions via "inputbox()" method.
SET NOCOUNT ON DECLARE @StartDate DateTime DECLARE @EndDate DateTime SET @StartDate = '20070731 10:10:51.450' SET @EndDate = '20070731 10:11:51.450' SET ROWCOUNT 100 SET NOCOUNT OFF SELECT EventTagName = EventHistory.TagName, Value FROM EventHistory INNER JOIN AnalogSnapshot ON EventHistory.EventLogKey = AnalogSnapshot.EventLogKey INNER JOIN SnapshotTag ON SnapshotTag.SnapshotTagKey = AnalogSnapshot.SnapshotTagKey WHERE SnapshotTag.TagName IN ('SysSpaceMain') AND DateTime >= @StartDate AND DateTime
I am trying to create an inputbox using code from this site:
Sub NumericDataType() Dim lNum As Long On Error Resume Next Application.DisplayAlerts = False lNum = Application.InputBox _ (Prompt:="Please enter you age.", _ Title:="HOW OLD ARE YOU", Type:=1) On Error Goto 0 Application.DisplayAlerts = True If lNum = 0 Then Exit Sub Else MsgBox "You are " & lNum & " years old." End If End Sub
I get an error on the first line that says, "Compile error: argument not optional".
I want to conditionally format the highest and lowest number in a range of numbers in a single column (Column C). The numbers in Column C are created by a formula that simply adds the data in the two cells in the same row of Columns A & B.
Numbers are entered into columns A & B daily. The column C formula has been filled into enough rows to complete the entire year. Since future days do not have numbers filled in for Columns A & B, Column C for those days results in a sum of 0.
I have set up conditional formatting for Column C so the cell of the highest number in filled in green. I want to conditionally format Column C so the cell with the lowest number is filled red, but it automatically fills all the cells for future days in Column C in red because the sum displayed there is 0.
Is there a way to exclude the 0 sum cells? The date is filled in as simple text, rather than formatted as a day. Is that the answer?
I have 2 excel sheets with records of email addresses. Excel Sheet A , Excel Sheet B.
I want to exclude the email addresses of Excel Sheet B from Excel Sheet A (if there are similar records I want to remove them from excel sheet A)how to do this?
I have a macro code (attached below) which i am using to do some conditional formatting for all my worksheets in a workbook. I would like this macro to skip 1st 2 sheets in this workbook.
Can some1 kindly edit my following code so that macro does not runs on 1st 2 sheets namely "Sheet1" & "Sheet2" and continue running for all remaining.
I have a workbook event where the macro inserts the username and date into the comments of the cell that has been edited.. However I would like it to exclude the 'Index' worksheet that has its own macro for indexing all the sheets in the workbook. As every time the sheet is clicked and the macro updates the sheet names all the cells comment.
