I'm trying to make a userform to show the results of a search from column A. This code will search the coulmn but when the results show it show's all ranges not just the ones that contain the string
VB:
Private Sub BtnFind_Click() Dim strFind As String
Dim c
Dim rSearch As Range
[Code]......
I want to setup a worksheet that I can search the name of the comic book and the number of the book and have the results underneath the search criteria. I am attaching a sample workbook. On the search database sheet, I have setup how I would like the page to look.
The spots where Enter Comic Name and Enter Comic Number I would like to act as search boxes, as in Google.
I would like the Search and Clear to be buttons.
Is it possible to return the results I want? And can I keep my searches until I push the clear button? If not I will get rid of the clear button.
The last thing I want to see if possible is once I get a result, can I add a button to go to that comic books location to enter data. Or add a save button so I can edit any info right on the search page and save it in the database.
I've got an question about using excel macros to search for information in a database with certain restriction. In my attachment you can see an example of the database I use.
Example.xls
I've got a database with certain packages, the length and the width of these packages is known.
I've also got a database with certain products, the length and the width of these products is also known.
I want to use a macro which allows me to see which packages are useable for a certain product. A package is useable if the length/width is less then 10 bigger then the product. So in the example if the width is between 10~20 and the length between 40~50.
Originally I thought I could use a macro with advanced filter and having restrictions width >10 <20 and length >40 <50. But I can't fit this in as macro because I already get the width and length from the product from another database and macro so it can't say >40 only 40.
I have created a form that will add new records to a database. On the database there is a predetermined ticket number. The "Create" form looks for the first blank row and adds the information on the form the database.
What I am trying to make happen is when a "Ticket Number" is entered in the appropriate field on the "edit" form, the "Date Raised" and "Raised By" fields populate based on the information stored on the row for the ticket number enter.
I'm trying to write a macro in Excel that will search certain values a Siebel database. I'm already working with a macro to ADD data to the database, so I have all the connections taken care of. But now I need to pull data from it. In looking for how to do this, I see the setsearchspec method that I need to use to set up the search then the ExecuteQuery to run it to get the pointer. But before that, I have to create the referencing object. I've noticed in several examples here and on a couple other sites, these two lines:
Set busObject = SiebelApplication.GetBusObject("Revenue")
Set busComp = busObject.GetBusComp("Revenue")
So here's my question: in all of the examples I've seen, they used "Revenue" as the argument for these methods. Does it really matter what we use there? Or this just essentially a place holder? I've looked at some of the online documentation and was unable to glean an answer from that.
I have a list of values in column A of sheet 2. What I would like Excel to do is search for each of these values on the whole of Sheet 1. Sheet 1 has around 20 columns of information and a dynamic number of rows.
In column B of Sheet 2, I’d like TRUE or FALSE displayed depending if the value is found on Sheet 1. For example
AB
1 DOGTRUE
2 CAT TRUE
3 MOUSE FALSE
4 HORSE TRUE
If this can only be achieved via macro, please post the code rather than a file as I can’t download the file here.
I have a value X in cell B5 and a Value Y in cell B6, in multiple worksheets
In my userform, i have 2 comboboxes
one holding value ranges for the x coordinaate (eg 0-10, 11-20,21-30)
the other holding values for the y coordinate (eg 0-10, 11-20,21-30)
i want the user to be able to select a range from the x coordinate and the y coordinate...then i want to search thru all the B5 n B6 cels across the worksheets and return those worksheets that match the users input...
is it possible for this to be done?
It has been a while since working with code and I can't seem to find something similar to this on the site, but I know that this will be a quick fix for someone out there. I am trying to search a range for certain numbers. If they are there I would like for it to pop up a message box. I have narrowed it down to it being the range to which it is giving me an error 13 type mismatch.
If Range("B3:B100") = "8800" Or "9900" Then
MsgBox "You have incorrect...."
Else: MsgBox "Correct"
End If
Is there any lock/security option in excel sheets, suppose we make some table/list and after entering one data when the cursor goes to second raw, the previous raw data automatically lock. the user cannot edit or delete that record.
View 10 Replies View RelatedI am currently working on a project for college. I have downloaded an example from a website and want help editing some vb code so I can use it in my project.
I have attached the example. As you see there is an input worksheet and the record of what you have already entered. The vb code takes some information from the system such as time and date and the user. All I want it to do is that it takes data from 5 cells I have.
I am no vb expert but I have tried and failed.
Is there a way to search a column range, and do an if/then on it in another cell. Ex) search e25:e37 and if none of the cells have anything in them, then input "--" into cell c14.
View 7 Replies View RelatedI have vba DATE variable 'FirstDate'; I assign it from a cell with say, 01/12/08; How do I then use that variable to search range of dates, without using the day and searching by only month and year?
Currently, I get the error: "Run-time error '91'; Object variable or With block variable not set" at the point of With Selection.Find(FirstDate).Select
I have been going in circles many times on this type problem: managing clumsily to resolve in the past using: Format(Firstdate,"dd-mm-yy"), text-to-column, NumberFormat etc
I have a database which shows members details with a colour system for varying levels of payment. I want to copy the membership number title and name from this d/base to another worksheet in the same w/book so I can print it in a4 size and select the page breaks. I think this is achieved by some thing called "concactia"??
View 1 Replies View RelatedThe following macro searches for missing combinations. This macro will search the complete list and will return any missing combination from "1, 2, 3, 4" to "7, 8, 9, 10".
I need to make some changes in this macro, so that it will search for missing combinations only within a specified range of cells (and not the whole list). For example (see excel file attached), I would like to place a search within range("G23:J183"), from combination "1, 2, 6, 9" to combination "4, 6, 8, 10". In this case, it should return only 9 missing combinations.
Attached File: Example Find Missing Combinations.xlsm‎
The following code works fine to determine if a particular character occurs within the selected range of cells:
Code:
Sub CheckIfCharacterIncluded()
For Each MyCell In Selection
If InStr(MyCell.Formula, "#") Then
MsgBox ("The " & "#" & " character was found in cell: " & MyCell.Address & " at position " & InStr(MyCell.Formula, "#"))
End If
Next
End Sub
However, I would like to extend this functionality to check for multiple characters, using some sort of array that contains all the characters I want to check for e.g. "#","*","£" and so on, without having to repeat the above code for each character for which I need to check.
I have two named ranges of cells, validcodes and actions. I want to conditionally format any cell in the actions range, if I enter something that contains certain validcode range values.
Example: ...
Its really hard to explain. See the attached.. Basically I have kind of a database of Letters and each letter has several codes associated with it, although each letter is different (a few are the same but that's irrelevant), e.g. letter E means you need to use codes 3.1, 3.2, 3.3, 3.4, 3.5, 3.6, 3.9, and 3.10. That information is stored elsewhere. I simply want a user to be able to put that letter into this box and next to it these codes will populate.
View 4 Replies View RelatedI want to find the corresponding Disc Codes from a list and copy them in the DiscName column in the summary sheet. some lab names will have more than one Disc codes so when I run the macro it should bring up all the relevant Disc Codes matching with the Lab name to DiscName column.
Col 1 col 2 col 3 Lab name Disc Name(say abcd) xxxx yyyy zzzz ppppand
The list looks something like this.
Col 1 Col 2 Lab name Disc nameabcd xxxxxabcd yyyyyabcd zzzzzabcd pppppbcda
qqqqqbcda rrrrrbcda iiiiibcda jjjjjbcda kkkkk
I have a database that has outgrown excel (over 77,000 rows now) so i now have it in Access. I want to replicate the following situation I used to have using lookups in excel with a query to the Access database: Paste a list of identifiers into an excel sheet. Values corresponding to the list are returned from the database in adjoining columns. The list can be from 10 to 300 cells long an returns data in 14 columns. I have had a go with the Import external data wizard which generates a query but have not been able to work out how to base the query on a list of cells in excel and have the query return values for each of the identifiers in the list.
View 2 Replies View RelatedCurrently I have a database in an Excel template. After a user creates a new workbook from the template, a macro button on sheet 1 brings up a window (a userform) to allow selection of items from the database sheet. The item selected is entered on sheet 1 by means of VLookup. That works fine, but to edit the database correctly it is necessary to open the template itself. This is not a user-friendly method.
I figure the best way to solve this dilemma is to separate the template from the database. That is, make the database sheet into a workbook. This I've done. Here's the problem: What code is there that links the list box in the userform (of the template) with the closed database workbook? Is it even possible? If a file path type code is required, it may work on my computer, but when I use my template and database on another computer, the code doesn't work.
Another problem, and similar is this: I'd like to be able to have a macro button that opens the database from the new workbook (which was created from the template) in order to edit the database. If having a template seems to be my problem, I must have it since each workbook created from it is a different project.
I have set up a simple form in excel, whereby people use drop down menus to select the appropriate information (as all data inputted needs to be entered in exactly the same way to allow sorting, counting etc) so I thought drop downs were best. The trouble is the data is carried through to my excel database (on another worksheet) were it is shown in numeric values dependant on how far down the drop down list the select item appears (ie "Fareham" is third on the list, so shows in the database as "3").
I'd very much like my database to be easier to read and actually show Fareham etc, rather than be populated with lots of numbers. I initially tried using IF function (ie =IF(C3=3,"Fareham") but unfortunately two of my drop down lists are too long for this (with 25 and 33 entries respectively).
Column A & B has a list of Supplier Part numbers and Buyer Part numbers as below.
Supplier P/N
Buyer P/N
HGFYE/12
111111
HYEYDH/14
222222
[Code] ..........
Cell D2 is an open cell that any data can be entered into as a search term. What I am trying to do is search for a Supplier P/N that have the characters "H", "G" or "E" in it, so entering "HGE" into cell D2 would display the results into columns F & G as below.
Supplier P/N
Buyer P/N
HGFYE/12
111111
HDGTEY/56
333333
I can easily do a formula for 1 character or a string of characters.
To complicate it further, if the search term has in this example has "YFF", I would like the same formula/code to workout that the result in F & G should show this time
Supplier P/N
Buyer P/N
YHDHFF/58
555555
I am using Windows 7 and Excel 2007.
I am a Microsoft Excel 2010 user and am trying to create a usage report for a website that I work on. I have been compiling the usage reports into one spreadsheet. The columns I use are Month, Device, Language, Title 1, and Title 2. I can filter each of these rows and it shows me the number of records found. Each row on this spread sheet refers to a time that someone selected something on the site. For example the row may say;
Month (filtered)
Device (Filtered)
Lan (filtered)
[Code].....
How do I automatically populate a table on a separate sheet with the number of records found for a certain combination of filters applied. So if I'm looking at monthly trending I want to know how many times the stress test was started in May, June, or July, but automatically using info from the multiple filters.
What is a formula I can use to compile the filtered data from above.
Jan
Feb
Mar
Aprl
May
Start
=formula?
I am trying to use the function DGET() to re-organize the information on the "DbCalc" sheet in the attached workbook. This formula can be found in C2:C5 in "purple. I am referencing the worksheet "Exp Rpt" as a database and match info based on two criteria. I can do this using an array formula like this ={SUM(IF((A1='Exp Rpt'!B$9:B$10000)*(A2='Exp Rpt'!E$9:E$10000),'Exp Rpt'!H$9:H$10000,0))} , however with the amount of data that I need to reference this array is soaking up all my memory. I would usually turn to access to do this, but the person that will be utilizing this tool only knows excel.
View 11 Replies View RelatedI am currently trying to create a database of products for my company. For each product I would like to include an image associated with it. I then want to have on another sheet a place where the user will click an error and be able to cycle through the products. As tehy cycle the associated image will pop up.
What I need to understand is after importing the image into excel, how do I associate that image to a cell so I can reference it in another sheet of the database. I am not concerned with how large the database will get, my pictures are quite small.
I'm trying to lookup a database named database and return a time in column A, based on criteria in cells a1 and b1 on another sheet, A1 would contain a number and B1 would contain a day from mon-fri
eg of Database
A B C D E F G
10:00 5000 Mon Tues
11:00 5000 Wed Thur Fri
and so on
if a1= 5000 & b1=Wed
how can I return 11:00
I have tried index and match =index(a:a,match(a1&b1,b:b&e:e,0))
I need a formula that compares 2 databases and returns a count if both values or within the specific range.
Column A is titles Column B is values The first database is simple the range is
I've used a countifs, but I'm having trouble doing a sum in a similar way. I have 2 databases and I will try to explain below. I need the to sum the values of database 1 if the second database is >= 20. So the value I would expect on this example would be 900. I would also like to be able to highlight which ones are elliminated. Such as coloring the text red.
Database 1
Eric 100
Jenny 200
Gina 300
Doug 400
Database 2
Eric 18
Jenny 20
Gina 34
Doug 55
i'm writing a refrigeration selection user interface, working from values on an excel spreadsheet. how to get the programme to automatically select a value from a list or range once a user has selected corresponding value from a list within a combobox. for example if a user sets the temperature of their refrigerator to -5 celsius i need the programme to automatically select the corresponding value of enthalpy for the air at that temperature.
View 5 Replies View RelatedI am attempting to use a named range as my Validation data source but have two questions:
1) It seems when you use a named range as a source the user can enter in any data they want in that cell. I really only want the user restricted to the list I give them. If I enter the list manually in the source box then it works but I really want to use a named range
2) My range is fixed to 10 cells and the user can enter in as many variables as they want (up to ten). When a user clicks on one of the cells that the validation is set to read the range the user has to always scroll to get to the top. When selecting the cell Ecel always defaults to the bottom choice in the list. I want to minimize the VBA because Mac users will be using this and VBA seems to be touchy with macs.