im trying to do is create a database of autocad drawings based on style of house number of bedrooms sq ft and if theres a garage. what i am wondering is how can i add a search function. for example i want to find a Cape with 2 bedrooms...i have all the information on the spreadsheet. what do i need to do to be able to search among the spread sheet without using the find function this is just the beginning of the data
I'm looking for a function (or combination of functions) that will essentially search for a row that contains two specific values (in two different cells), then grabs a value from another cell in the same row to use in a calculation.
I don't know... that probably doesn't make any sense, and maybe there's no way to get it done. If so, it would save me hours. Couldn't see a way to attch an example file to my post, otherwise I could probably describe it a bit clearer.
I have developed a functional User Form that works like a charm. The form will Add New Records to my database and Get Next and Get Previous records. This all works like a charm. I am stuck on how to search by Last Name or Employee Number and then to Update the Existing Record. If I change anything in an existing record as the VBA is written today, my only choice is to add a new record to the end of the database. I have added Command Buttons, but I'm lost as to how to write the code. Here's what I have so far:
VB:
Dim currentrow As Long
Private Sub cmdGetNext_Click()
Range("A2").Select
ActiveCell.End(xlDown).Select
lastrow = ActiveCell.Row
[Code]...
I have created a Userform for entry data's in a Excel2003 file. I would like to retrieve a complete record (= a row) by searching on a string contained in a cell of this searched record-row. Here an example of a row and I'm searching upon "2041" via an inputbox.
idn° claims Dateréf. Suppl supplier Fax n° PO
1AAA2041 2/01/08200039 BOSCH (RAS) 056/20.26.75 774634
I'm working on a spreadsheet that has a counting number value on the cell L2 and a score on cell L4. These cells will be changed during the day, L2 always increasing, but not with a uniform increment. The interval between the changes also varies. Each time L2 changes, L4 may or not change. I'd like to keep the record of the changes on L2 and L4, each one in a row, together with the date and time when the changes where made to L2 and only add one new row if the L2 value has changed. The records should start from line 6 on...
See the attached file for more details. Currently I'm doing the updates to the record rows manually, I'd like to make it automatic every time the value of L2 is changed from the previous value to a new one.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$L$2" Then
If IsNumeric(Target) Then
'Stop any possible runtime errors and halting code
On Error Resume Next
'Turn off ALL events so the Target * 2 does not _
put the code into a loop.
Application.EnableEvents = False............
I have a list of times in a column. I'm looking for a macro that will go down the column and record all the times that lay between 5 and 7am, 1 and 3pm (13:00-15:00) and 8 and 10pm (20:00-22:00)
How to write it?
I'm creating a mileage log for my work as a traveling art teacher. Most templates have you just input the number on the odometer when you started and when you finished. Since I can never remember to check the odometer when I am getting in and out of the car I thought it might be easier to just record the distance from each location once and then record that number of miles each trip. I usually travel the same route so the mileage is always the same.
I have an array of numbers right now, with 3 columns listing my 3 most common start locations. Then I have 5 rows listing the 5 schools I travel to for work, and the distances from each start to each end in the array. On a separate sheet I have my log.
What I would like to be able to do is select a start location and an end location from a drop down list on the log and have the number of miles automatically appear in the miles column. Then I can double this for the return trip and total it at the bottom and calculate my expense.
I am having trouble writing a formula that works for all 15 possible distances, and I don't quite know how to write it so that it references my array and finds the correct distance for those two specific choices. So far I have the drop down menu's pulling from the array but I'm stuck at how to index the array using the information that is coming from the drop down list.
Since I travel to these locations multiple times I think it would be a lot faster than looking up the distance every time and typing it up manually.
Basically, I have a large spreadsheet with basic data including names, dates and a unique identifier number (UIN). I have created a questionnaire in a userform (with a UIN txtbox) and want to link the answers to the specific individuals on the worksheet.
i.e. If UIN 201 fills in the questionnaire on the userform it will enter the details on the spreadsheet next to the details already entered for UIN 201.
I am trying to create a continuously updating record or log of the numbers in a particular cell that has numbers that change frequently. I want every number that was every in that cell recorded in a table or column so I can analyze those numbers. Please note that I do not have much VBA experience.
View 9 Replies View RelatedI have recorded (i.e. manually as opposed to writing VBA code) a number of macros to perform a routine, however they do not work when I change the filename.
Could someone please advise on how to edit these macros (which reference a specific filename) so that they work when the file name is changed. The macros copy and paste values from different worksheets and then run another set of macros. However all macros and worksheets are located within one excel file
how to make the << and >> buttons as well as the find button work on this form. Here is the code for the >> (next Record) button. I think if I can understand how one of them is supposed to work I can do the rest. I have been trying for days to figure it out, and even with RoyUK's help I still can not get it to work....
View 9 Replies View RelatedI'm trying to search through multiple worksheets (that are closed) to see if a value in cell B12 (of every worksheet) matches a value in a seperate worksheet (which is in a seperate workbook)
View 14 Replies View RelatedI have attached a sheet with what I am trying to accomplish...
I have several records that I am doing a lookup through. Most records have the same "work order number". As you can see on the attached sheet, my lookup function returns all the work order numbers that match my criteria.
What I need is to only return the first instance of the work order number then return the next unique work order number that meets my criteria.
Attachment 297239!
I would like to have a set of cells that add up all the sales within a given week. I know how to do this simply for one week, but how do I get Excel to automatically take this function and create the rest for future weeks?
After entering the SUM function in one cell, I click and drag on the box to try to get Excel to correctly input the functions in the next cells (like how Excel will correctly input the next date, week, or month). But Excel doesn't do it correctly.
Using the search macro code below, could someone please help to add in more codes what I'm currently using, and also where to insert it. The Search function works well for what I need and it helps me to locate data. When using the search function somehow it search all sheets within the workbook but I only want it to search an array of sheets when using this macro that is needed to complete the task for what I'm after.
Macro
Public Sub FindText()
'Run from standard module, like: Module1.
Dim ws As Worksheet, Found As Range, rngNm As String
Dim myText As String, FirstAddress As String, thisLoc As String
Dim AddressStr As String, foundNum As Integer
myText = InputBox("Enter the text that you want to search for:", "Start Search!")
If myText = "" Then Exit Sub...................
I'm from Bavaria, Germany. Right now, I am doing an internship for my studies. my problem: I need a search procedure which shows an Input Box where you can enter a word to search for. It should work like the original Excel search (Ctrl-F), but with a simpler design, like with my own Text "Enter your Query" and a Button "Submit Query" / "Quit search". Is there the possibility to Highlight the Search Target? The problem hereby is that this highlighting should not be permanent. That means the user sees the target for which he searched for, the cell highlighted in a different color, etc. But as soon as the user clicks onto another cell, etc., the highlighting vanishes. If there is no fitting match, there should be a MessageBox like "Sorry the Target xyz cannot be found"
View 9 Replies View RelatedI' having trouble using the =IF(ISNUMBER(SEARCH formula to search multiple cells.
I can get it to work to search one cell (as below):
=IF(ISNUMBER(SEARCH("Same",G10)),"No Change",'Aug 09 Matrix'!F10)
(In this case the respone is No Change as Cell G10 contains "Same")
However cannot get it to work for several cells. I have pasted below and highlighted the function I would like it to perform:
=IF(ISNUMBER(SEARCH("Same",G10:R10)),"No Change",'Aug 09 Matrix'!F10)
Essentially the other cells in that row (G10:R10) all contain "Same", however for some reason it is identifying this as FALSE and putting in the data from the 'Aug 09 Matrix' sheet.
i used excel as a calculator but when i needed to make some effort to reach a solution for my problem it let me down, so i will write my problem
i have a record in my data base and i want excel to look if the record is in another range and apply some maths. so i wrote this formula
I created this macro (below) using the record function but when I kick it off it takes 40 seconds or more to run.
View 4 Replies View RelatedI have made a database to record drawing numbers.
In column A is the drawing number and in Column B is the revision number.
I am trying to write a macro that will alert the user when they are trying to enter a drawing that contains BOTH the same drawing number AND the same revision number as one already in the database.
Since drawing numbers will be reused and only revision numbers changed when a revision is entered, I need the function to check both column A and Column B then display a msgbox if there is a match.
I have an 'Update' Module that search the value of the Userform1.txt_sc.Value in Column B, but I want to search to 2 criteria, how can i amend the below code to search for 2 criteria ( userform1.txt_sc.value & userform1.txt_linenum.value )
View 2 Replies View RelatedI seek advice on using the value of NOW() as a record ID in an address book program. Question #1: Do Excel developers often use a record ID? Question #2: What record ID schemes are fequently employed besides date/time? I have decided to create an Excel address book as an exercise to increase my knowledge of VBA, and also as a useful application for work.
I realize that a record ID is not essential in Excel in the way that it is essential in Access, but I feel the need to have some unique ID associated with each address, so that I may have different worksheets, with data related to a given Contact, sort and manipulate it, if necessary, but have the record ID as a way to restore the relationship of rows to a given Contact, and also, as a handy way to examine the data in the date/time sequence in which it was entered. I have experimented with the following code, to assure myself that I can access the number returned by the NOW() function, manipulate it as a string, and format in various ways if necessary.
Dim n As Double
n = Now()
sn = Str(n)
p = InStr(sn, ".")
first = Left(sn, (p - 1))
l = Len(sn)
d = l - p
S = Mid(sn, (p + 1), d)....................
I am creating an asset management sheet. For the formula I am trying to work out there uses 3 fields : ID, start date, and end date.
What I want to do is be able to show if the ID is duplicated within another record with an overlapping date. So an item is flagged if it is in the list within the same dates as another record. I tried a few countif formulas but with no success.. I may just be approaching the problem incorrectly though.
I want to search for a value, in a specific range (in this case, in a row). If that value exists, l want the formula to return that value, otherwise l want it to return "FALSE" or whatever.
The formula must exist in one discrete cell only, i.e. the output will be contained in only one cell.I don't want to match the occurrence of that value with another row's cell. I don't want to search for multiple values (although if you have a way to do that, l'll be interested to know!), l don't want to count the number of times the value occurs.All l want is to return that value if it exists in the specified range of cells, and if it doesn't, l want to say "FALSE" or something.
I have a Master work sheet consisting sale data of more than 400 Sales person for a period of 6 Months.
The Employee Codes are entered in column "F" starting from F6, F7, F8 and so on....
G5,H5, I5, J5, K5, L5 to GV5 has the product names.
G5,H5, I5, J5, K5, L5 to GV5 has the qty of product sold by that concern sales person.
Since each sales person's occurrence is 6 times in the master sheet, I would like to have a code to search each and every sales person by their employee ID through out the entire master sheet, sum each product they sold as per the product name and get it entered in their concern cells.
I need to look through a range for a specific value. When I find it if the nearby time column matches a specific time I want to retrieve the lookup value. Because I am not very macro savvy, and the spreadsheet has to run on both mac and pc I do not want to use a macro. Can I do this with an IF((AND combination or is there a better way? If there is a way to look through a range with IF, how would I structure the criteria to look through an entire column for the value? Lookup won't work because the value can occur several times through out the range, but only once with a specific time value.
View 5 Replies View RelatedI am looking to create a macro that will create a new sheet when data is added on a summary sheet. Example.
1. Summary sheet called "Variations" contains columns that will contain the information needed for new sheet (Columns A to D)
2. When data is entered on "Variations" sheet: Column B, then macro automatically creates new sheet renamed to e.g. VO1 (Number used on "Variations" tab) and is a copy of "Master" tab.
3. Data entered in Column A to D on "Variations" tab is automatically entered onto new sheet created (e.g VO1). Shown is blue on attached file. Additional data is updated on "VO1" sheet and this then links back to "Variations" tab
I'm trying to find a macro that will run allowing the user to select a 'starting record number' and a 'finishing record number' when printing.
I have a spreadsheet that feeds from a master list in excel, from over 5000 records.
I need to print the s'sheet with any given indivdual record's information at any given time.
Individual prints are fine. However if I wanted to print from record number 1500 to record number 3000 it would take me all day.
Is there a way I can set up a macro so an option form pops up? allowing selection of "From record" and "to Record" ?
In a cell(s) I have for example {text}dd%2BMore_Text}
what i want to do is search and replace the final }
so it should be {text}dd%2BMore_Text
the regular search and replace in excel removes all } which is not what i need.
I have a code which can search a column of data and register if any of the cells are equal to eg. "LOMM" or another given shortname.
But sometimes the cell that i want to compare to "LOMM" contains multiple shortnames. (eg. "LOMM, GIV, ANDHE").
In such a case I cannot use the if cells(i, y) = "LOMM" then...
I need an "if"-statement which can identify LOMM in a cell containing multiple shortnames.
I have tried using the FIND and SPLIT functions but it does not work.
