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 RepliesI'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............
take a value from an input box and use it locate a record?
On my "Menu" sheet, the user will click a button which opens the input box and asks them enter a record number (format = PCAR.yy.nnn.xxxx)
The records are stored on a "Log" sheet and the lookup value is located in column "F".
I found code on another post which I used to create the input box, but don't know how to take this input and use it to find the relevant record.
I have the following code, which I use to find the first empty row in which to add a new record. if there is an error (record not found) to return to the input box to reenter the number.
Sub GetPCAR()
Dim Pcarnum
Pcarnum = InputBox("Please enter the PCAR number")
MsgBox ("Searching PCAR") & Pcarnum
End Sub
Sub AddPCAR()
Dim rNextCl As Range
Set rNextCl = Worksheets("PCAR Log").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
Worksheets("PCAR Log").Activate
rNextCl.Select
ActiveWindow.DisplayHeadings = False
End Sub
At the top of my spreadsheet, a day of the week is selected via Dropdown.
Below that in a table, the days of the week are split into hourly slots.
I want to write a macro to jump to the first cell in the bottom table containing the day selected in top cell.
See attached worksheet for reference. Is it possible (while utilizing the same spreadsheet on a weekly basis) to zero a spreadsheet subsequent to its use. Importantly however, all relevant formulas must remain perfectly intact and will re-establish themselves once relevant data is placed inside an individual cell? In this case, as soon as a “Name” (or even a letter) is referenced inside the “Name” column: H10:H19?
In other words, the entire sheet is blank bar the top date and respective headings. Once any text is placed inside cells H10:H19, the formulas from the associated Row re-applies itself to the “Week-Start” dates, “Week-End” dates and references a default “Phone” amount for ‘$10’? The Data Validation formulas I’m sure would remain undamaged? This would prevent ‘text clutter’ (such as dates extending to the bottom with no apparent referencing or connecting information?
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.
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
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 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
I 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 have a spreadsheet with 5 columns. A1 to A5
Entries are made into columns A2 to A5 but only ever one entry across all cells
If an entry is made into A2 to A5 the I need a "P" to be placed into A1
I am trying to create a macro which gives me an input box. I need to enter 2 numbers into the input box. It would be as follows:
Input Box Msg 1 - "What is your labor cost?" (NUM1)
Input Box Msg 2 - "What is your productivity rate?" (NUM2)
Then I need the macro to take this info and enter it into a formula which changes according to the row which you are on. (I need the macro to work based on which ever cell I currently have selected). The formula would be this if I currently had a cell on row 10 selected:
=(NUM1*(NUM2*$H10))/$H10
So, if I currently had cell L10 selected, and I ran the macro and put 100 in for "NUM1" and 10 for "NUM2" and my sheet had 20 in cell H10, then the macro would enter the number 1,000 into cell L10. If I had L500 selected, then the macro would enter the final number into cell L500 based on what value H500 contained.
Any way to limit the date a person can input to the fourth business day of the following month. So limiting the date the 06/05/2014 for instance. Would there be a way to keep this updating so manual updating would be unnecessary?
View 9 Replies View RelatedI would like to know if it is possible to transfer values of cells this way?
I've attached the excel file for easier reference.
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 am wanting to create a push button function with a query attched that will allow the user to input certain criteria with the end result in sorting the column containing the criteria.
I have attached a sample of my form:
My desire is the user will have the ablity to push the button and a input box will appear and they will be able to enter the data to search for. And the column with sort showing only the information required.
What I am having difficulties with is creating the pop-up input box. A simple macro with a sort or filter will not work as the data criteria is not the same everytime.
I have a spreadsheet of sales figures that I want to interrogate to show data relating to sales of specific manufacturers products to specific customers, I have a colomn (call it A) that has the customer name listed, coloum B that has the Manufacturer name, coloum C that has the type of product it is and coloum D, coloum E, coloum F, coloum G that has the sales figure for the last 4 quarters.
I would like a user input box to pop up on loading, which asks for the Manufacturer I want to search for, and then a report genertaed that lists each customer with their sales figures by type of product for the specified manufacturer for the last 4 quarters (individually not tottaled). If the customer doesnt have any sales figures for that Manufacturer I would like that listed showing '0'
I have nearly finished my project, but need to make the last real bit of code, then just some general tidying up.
What I want to do
- I currently have two tabs in a userform - one called 'main' and one called 'extra'.
- I want Main to always be shown to the user.
- I want Extra to be hidden (is it possible to make the tabs invisible?) and for this form to be shown only under a specific condition
I have a question on my userform (using Option Buttons):
"Did the customer ask about an extra product today?"
If the answer to this question is no - When the user hits the command button (after filling out the rest of the form), do nothing special - just return all the values to the worksheet
If the answer is yes - When the user hits the command button, I want them to be taken to the 'extra' tab. They will fill out some additional check-boxes, then hit another command box, which will return the info from both the 'main' and 'extra' sheets into the same row of the worksheet.
In brief: I want my 'extra' tab to be hidden, only to appear when 'yes' is selected, and for the results all to go back to the worksheet.
I'm trying to make the included spreadsheet as idiot proof as possible as a lot is a stake. I have most functionality working well; however, not I would like to add a little more data validation that does not exist in the normal cell validation rule set.
I need all the input cells to validate that the cell contains the word "BOX" upon entry, otherwise throw a warning. This is because the data entry will be done by barcode and there are a couple barcode labels on each box. The one I want will include the word "BOX" somewhere in the code sequence.
Since a previous function in the Code locks the cell, they wont be able to fix the error. but it will alert them so they can make sure to scan in the proper barcode and not miss the box. Also, once I get something that works in the code, where would I put it? In the same code box as the previous script, underneath it, or somewhere else? So, bottom line, I need a script that check the cell for Text of "Box" otherwise throw a message box.
I have a paragraph that i am trying to change a number in the paragraph. example
John has 3 kids he sells one kid he has 2 kids left. Later John's wife figures out and kills John 4 times.
I am trying to replace the "4" to an imputed on an user fourm imput box can i do this?
> When the workbook is opened I want a box to appear with a message and 4 choices (as buttons?).
> Based on the button clicked I want 1 of 4 new boxes to appear (replacing the first box) and ask for input values.
>I then want the inputs to be written to specific cells on a specified worksheets inside the workbook.
I have an excel sheet that has two text fields "A2" 2 digit (Month) and "B2" 2 digit (Day of week), both are currenlty in a text format. I need to force the users of this sheet to enter values in both of these fields prior to saving. Is there a simple macro that I can run to check these fields.
View 8 Replies View RelatedI am just starting to get my feet a little damp with VBA and I am trying to make a macro that will act whenever any text is entered in any cell within a 3x3 square. Whenever any text is entered in any of those cells I would like it to enter a text ( "X" ) in a cell which is specified by a cell in the spreadsheet (this cell will output which cell the macro should write the text in in this format "A:1" or "C:3"). How can I make the program act only in reaction to the user entering thier text, and also, how can I get the macro to read A:1 and enter the text into that specific cell? Any help would be so awesome, the book I have is really difficult to navigate.
View 9 Replies View Related I need to copy and paste the specific dates from the specific city that the user will insert in the "dash" sheet.
Basically, I need the macro to read the city and range of dates that the user will type into the "dash" sheet and copy the columns "AA" to "AD" from rows specified by the dates input. Once it has been copied, it has to be special pasted just the value onto the sheet "1" beginning at location B2.
the the data will not be available as it is vlookups to another excel sheet that i have not provided. I have deleted the data, but you can pretty much put 1's everywhere.
I 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" ?
The added piece I now need to put in is how many times a cell value is changed in a day (I will add to a macro to return it to 0 at the end of each day). The way in which I would approach this is by using a combination of a cell_change and count function. Could I ask somebody to advise if this is the best way?
View 7 Replies View RelatedWhen a cell changes i would like the cell in the next column to record the date of this change. After achieving this i want to set up conditional formatting to compare this date to the current date and if they are the same then change the original cell's colour to show it changed today!
I started using the code below (from ozgrid) which was working fine BUT my cell value in column A is actually taken from a different column. when the different column cell value changes my cell in A changes but the script below does not seem to see this as a change. it only records the date if i physcially change the cell in A rather than another cell prompting it to change.
based on my overall objective and my plans to date please can someone suggest a solution?
current code below....
I have an Excel worksheet full of data. I would like to search in a specific column for cell that contain "xyz". If found cell = xyz, then I want to delete the whole record or row.
Is there a formula or option to do this?
I am currently looking at each record and deleting it manually. With a worksheet of more than 2000 records, this become a huge task!
Could it be possible to "sequentially offset" a vlaue, say, of A1 such that, for instance, when A1 changes from "5" to "6" (via formula, not direct input), A1=6 and, say, B1=5, and so on? You know, like keeping a value record of A1, one step back, in B1.
This would be UNBELIEVABLY valuble to me. Thus, would I be in eternal debt to he/she who would resolve this here.