Exact Values Needed In InputBox
Jan 16, 2007
When I click a command button an InputBox pops up asking me to enter a set of initials. When I enter the initials and click OK all of the rows with the initials in column 'H' are transfered to another worksheet. However if I was searching for say the initials 'AP' and I only entered 'A' and clicked OK, it will still transfer all the corresponding data with an 'A' in column 'H'.
In other words I want the data that is entered into the inputbox to be exact or nothing gets transfered.
View 9 Replies
ADVERTISEMENT
Jan 8, 2009
I am looking to build a VBA to do the following action:
Look up an ID number from column F (ID numbers are separated by an empty row at current),
then check whether there is a corresponding value in columns N to P,
if there are values, copy them onto a different sheet into three different columns
if there values below this particular row, i.e. if the rows below say row 9 (where we looked up the ID number) in columns N - P are not empty, also copy all these values and add additional rows as needed on the sheet this is copied to
then look up the next ID in column F and do the same until there are no more IDs
View 9 Replies
View Related
Jun 30, 2009
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>>>>>
View 6 Replies
View Related
Mar 6, 2014
I am writing a macro that will take data from a series of objects and then filter 3 columns according to their attributes.
EX.
Object 1 Example1 20140306 10
This combination of values is unique so is used to grab the value in the third column after the filter has ran.
My issue is that in the second column the actual values are 50201403069999 and they change per value although they represent the same date "20140306" so manually I can type in "20140306" and the filter will show the results however it seems the macro requests the exact which I can't provide as this the superfluous numbers change.
Is there a way to make it the auto filter search the value??
My second issue is how to extract the value sought once found. Since this cell will be different each time, what is the best way of selecting would it be C3 select then down?
Here is my current code:
Code:
Sub IMPORTPRICES()
Dim i As Integer
For Each Ativo In Sheet4.Range("A2:A6")
[Code].....
View 1 Replies
View Related
May 14, 2007
I have a macro that should allow users to change a certain range to values, but I have problems with that last piece. Can anyone tell me what the command is to paste the range as values?
Sub Change_Range()
Dim UserRange As Range
DefaultRange = Selection.Address
On Error Goto Canceled
Set UserRange = Application.InputBox _
(Prompt:="Change Range to Values:", _
Title:="Range Change", _
Default:=DefaultRange, _
Type:=8)
UserRange.Copy
UserRange.PasteSpecial
Canceled:
End Sub
View 9 Replies
View Related
Apr 23, 2013
Excel 2010, I have the following list (showing part of it):
Name
T-Shirt Sizes
Nathan
S
[Code]....
The aim of the list is to see what Sizes each and every person needs and then count how many T-Shirts are required for that particular size. The list is over a 100 entries long so counting it manually isn't really an option as it's time consuming as well as errors might arise.
Thus, I've been trying to use the IF statement with the following logic but to as no avail
IF Size in Column is XS
Add 1 to a particular Cell
IF Size in Colum is S
Add 1 to a particular Different Cell
and so on for the other sizes...kind of like a counter for a for-do-loop.
View 2 Replies
View Related
May 16, 2014
I'm trying to create a macro that will take a value found on Sheet 1 in Column A and search Sheet 2 Column D. If the value matches exactly it will highlight the row on Sheet 2 in Red and if a match is found but it has some extra characters in the list it will highlight the row in Yellow. I have included a sample sheet to show you exactly how the outcome should look. It will need to run on a loop until there are no more values found in Column A on sheet 1.
View 7 Replies
View Related
Nov 20, 2013
I have a file and i want to make an exact copy with only values and the layout.
I can do copy paste each sheet in the new file one by one. with first the values and then the layout.
But the file has lot of sheets so it would be great to find a faster way then do it one by one.
View 1 Replies
View Related
Jan 15, 2014
Below is my current code. The strFind1 searches for a name within the database and then I need strFind2 to do a exact for a Subproject search and a partial search for everything containing the Subproject selected and other Subprojects. Currently, when the database entry in the worksheet includes Subproject 1 the search function works but when I have an entry that contains Subproject 1/Subproject 4 it does not find the entry. How can I expand the strFind2 to equal what is selected in the Combobox2 and find entries that have what is selected plus more text. I have set the line where I think everything is going wrong to a bold format.
[Code] .....
View 2 Replies
View Related
Feb 13, 2014
I need an autofilter which filters rows based on a list of values (+50).
The problem is that I am looking for rows INCLUDING values from the list, not for exact match.
I am not able to make excel filter values including the values from the list... I am able just to filter values matching exactly values from the list.
[Code] .....
View 2 Replies
View Related
Jan 11, 2013
I have "systems" that have multiple "units". I what to sum values for units that have EXACT MATCH in the system column. Example data is below.
I am trying things like =SUMIF(B:B,"EXACT MATCH",C:C)
How do I express exact match? For example the first 3 in the list I would want to return a value of 9. Note: Lists can have up to 3000 rows and > 1000 system ID's.
Unit ID
SYSTEM ID
Qty
MRK0012179
MRK0012179
3
[Code] ......
View 9 Replies
View Related
Jun 19, 2014
So I have two sets of data, I've attached a worksheet which has them both. The datasets have different intervals, but I need to drag "Colour" from Data2 into Data1 and associate it with the correct interval. Sometimes the intervals match up and sometimes they don't - which could be a problem, but as long as its within a small spread it's fine. The "Colour" column in Data1 is filled with my desired result, the source being Data2.
ManyToMany_Example.xlsx
View 3 Replies
View Related
Sep 13, 2008
way to take 18 different workbooks, each containing the same 5 worksheets, and create a TOC that reference the workbook name only.
For Example, I would like to have one workbook with the first worksheet as an index or a table of contents that lists the name of 18 workbooks. When I hover my cursor over the name of a particular workbook in the index or TOC, it will open that workbook where I can now view 5 worksheets of that workbook.
Each workbook will contain the same 5 worksheets.
I am using excel 2002
View 9 Replies
View Related
Oct 12, 2008
I'm trying to make a spreadsheet for work so when im not there they can just enter the day for example sunday and what they have and then it will show them what they need to prep
View 4 Replies
View Related
Apr 9, 2014
I have a big list with names, money spent and date. I need somehow with a formula to do:
Take all the money spent on some specific day from a specific person an put it into a field.. and this for every date of the week and for everyname... Im gonna upload a photo.
Name
Money
Date
Daniel
5
01.03.2014
[Code] ........
I need the list of the left to produce me list of the right somehow, at least just the sum of everything spent everyday.
View 7 Replies
View Related
Jul 3, 2014
I need identifying correct title and corresponding company name. For this I need a UDF which looks for first occurrence of title having event or marketing or meeting word in data and if found the remove other titles and company names already separated with "/".
Sample Data
Producer, Target BTC@Periscope/Events Manager, Recognition Events@Minneapolis Park & Recreation Board/Event Producer@Events by JLS
Required Result
Events Manager, Recognition Events@Minneapolis Park & Recreation Board
Sample Data 2
Sales Associate@Teavana/Event Assistant@City of Saint Paul
Required Result 2
Event Assistant@City of Saint Paul
Sample Data 3
Sales and Catering Manager@Bunker Hills Golf Course at Town & Country Caterers/Marketing Coordinator@Town & Country Caterers
Required Result 3
Marketing Coordinator@Town & Country Caterers
View 3 Replies
View Related
Jul 31, 2009
I am trying to get a MsgBox to appear if the cell value within a given range exceeds a certain number value in my case "8". The only issue I am experiencing is that the code seems to take into account any input from any cell, not just the range specified, and additionally since there are 7 columns in the range the MSgBox pops up 7 times, and I need it only once and if the cell value exceeds "8" only when input.
ideally i would like to do a named range as there as there is a block of data where this will need to repeat.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
With ActiveSheet
For Each cell In Range("J40:P40")
If cell.Value [A35].Value Then
MsgBox "Red Cell cannot exceed 8 hours per shift. Please correct this."
End If
Else
End If
Next
End With
End Sub
View 9 Replies
View Related
Jul 9, 2014
See the screen shot I have attached: ramp rate.jpg
Basically I have to trawl through tonnes of data every day and I would like an automatic way to do it for me. I am calculating the ramp up rate on an Autoclave oven so these figures are temperatures.
I have various ranges for each section of a cure cycle so I will use this one example.....
I need to calcuate the ramp up rate between 115°C and 140°C
I need to find the min and max between 115°c and 140°c then count the cells within the min and max, minus the min from the max then divide by how many cells there are inbetween
(MAX-MIN)/CELL COUNT BETWEEN THE TWO
Is this possible?
View 3 Replies
View Related
Apr 25, 2013
I want to create a web address for the attached file and then post the link on my football forum so that members can view the file in their browsers. Is it possible to do this?
Attached File : Book1.xlsx
View 2 Replies
View Related
Jan 31, 2009
I have a macro on: Sheet101 (DAY 1) that adds additional sheets.
Example: When I click active button it will add Sheet102 (DAY 2) & can add up to Sheet130 (DAY 30)
I want a macro that will delete any of the sheets added from Sheet130 (DAY 30) to Sheet102 (DAY 2) But WON'T Delete Sheet101 (DAY 1)
Example:
If I have added 3 sheets I want it to delete those 3 Sheets:
Sheet104 (DAY 4) <--DELETE
Sheet103 (DAY 3) <--DELETE
Sheet102 (DAY 2) <--DELETE
Sheet101 (DAY 1) <--DON'T DELETE
& give a vbOkCancel MessageBox saying: Do you want to Delete Added sheets?
Okay the code below works, but one thing!
It deletes DAY 30 to DAY 20, then it skips DAY 19 to DAY 10, then Deletes DAY 9 to DAY 1.
So it's not deleting DAY 10 to DAY 19...
Do I need to add a wildcard?
Here is the Code: .....
View 10 Replies
View Related
Jul 31, 2009
Hi, I have this good working formula: ...
View 6 Replies
View Related
Sep 24, 2009
Is there a way to delete this un-needed data while it is still in an array (effectively resizing the array - removing specific columns and rows) and then writing the array back to the worksheet?
Example:
(5X5) Array with the following data
View 3 Replies
View Related
Sep 29, 2009
The worksheet perfectly performs as expected, big thanks to JBeaucaire again. Now, as am looking around of the file and doing some exploration on the array, as I was trying to make this applicable to real life application, I noticed the following mysteries...
1. When I tried to delete some rows, The Members Sheet output went wrong and displaying #N/A. I noticed it's because the formula range have change. But it would be hard for a new user to re-arrange it over and over again everytime when a row is deleted.
2. I've been trying to solve this problem to no avail. I found this link that gives me an idea how my worksheet should work, this was already solved but I can't apply it on my worksheet...
http://www.excelforum.com/excel-prog...nd-resort.html
3. I would like to add another column on A to put the 0 and 1 on, but I don't need to delete this column after the resort so that next time if I need to delete another row existing row i'll just replace the existing 0 with 1 on column A.
View 2 Replies
View Related
Jan 14, 2009
In order to avoid embedding 12 if statements, I want to take an average of a selection of cells based on what month it is. So I have a "now" function, and then a month function referencing the "now" cell so that I get the current month as a number 1 through 12. I have a row of expenses (C14:N14) and I just want the average from January up to the current month. The reason I can't take an average of the entire selection is because the users don't want to have to enter a 0 everytime there's nothing, they'd like to leave it blank, so the sheet needs to recognize what month we're up to. And the average function in Excel only takes an average of the data that is present, not the entire range. For example, if the data in the first cell was 15, second cell was 15, and third cell was blank, the average function returns 15 and not 10. So what I need is, for example, if it's March, the average will calculate =average(C14:E14) and so on. Any ideas? Maybe it's a simple change of formula instead.
View 13 Replies
View Related
Jul 17, 2012
Anyway, I am basically done with a program which looks for one condition and displays an email to send as a reminder.
What I need is to have an if/then statement which checks a column in the spreadsheet and if the cell in the column states "closed" then an email is NOT displayed/sent. It seems whenever I run the macro, an email appears even if the cell for that row reads "closed."
My if/then statement in question lies following my note " 'here lies my current dilemma to solve"
Code:
Sub ThreeDayEmailTest()
'
' ThreeDayEmailTest Macro
[Code]....
View 6 Replies
View Related
Oct 3, 2008
I am using HLOOKUP to show a date depending on the month shown in B10.
If B10 is January then the dates throughout January ie. 01/01/08 to 31/01/08 are shown in the cell range of F13 to F43. There is no problem with this unless there are 30 or 29 days etc in a month. So if November is in B10, in F43 I have the value of 00/01/1900. Can I modify my formula so if there is no data in the lookup table to return a blank value ie. "".
Here is my formula:
=HLOOKUP($B$10,DATES!$B$1:$M$32,2,FALSE)
I obviously change the 2 to 3, 3 to 4 as I copy the formula down the table.
View 9 Replies
View Related
Oct 4, 2008
need a macro that will open the following folder
c: my documentskatyexcel
View 9 Replies
View Related
Jan 21, 2014
Basically I have in the first column, different products (lets call them a, b,c and d). in the second column is a counter which starts at 1 and continues counting down the column.
In the third column I need to identify if it is the last time the product has been run. i.e there are no more duplicates of product a further down the 1st column.
I have attached an example : example.xlsm
View 2 Replies
View Related
Jan 13, 2009
I've got this macro I pulled from some other site and it's not working quite like it should. In my eyes at least...
OK, first, a little back story...
At work we used to have a custom Outlook form that was used for submitting office supply order requests.
The form was stored on our public drive for all to access it when needed.
Well, the form broke somehow and no one knows how to fix it.
We need something to replace it.
Keep in mind that we have no access to the Internet, nor can we send/receive emails outside of the company.
So, I created a simple form in Excel with a drop-down list of all the items in the supply cage, a form field for the senders name, etc...
What the macro does is, it copies the active worksheet the form that just got filled out) to a new workbook (dropdown data is on other sheets in the original) and then opens Outlook (2003) to send it as an attachment.
The only catch is that when the email arrives the only thing filled out is the form fields, NOT the drop downs...
The ONLY way I can get the drop downs to send with data in them is to leave the original open.
Oh, and for some reason every time I sent this while testing, it would name the attachment Book1, Book 2, Book3, and so on. I can't have that...
Here's the code I'm currently using:
View 9 Replies
View Related
Dec 2, 2009
I have this module that is supposed to look in column A and if it sees duplicates it looks in column B (with the date) and erases the entire row where the date is older in column B. I am attaching a sample to better illustrate it.
The highlighted in yellow rows are were I have duplicates and I want the row where the date is in red border to remain.
The module I have so far erases the newer records instead of the older.
This is the file:
Sample sorting.xls
Here is the
View 3 Replies
View Related
