Debug Assistance - Auto Date Entry
Apr 3, 2007
i try to lock cells in the area the VBA code affects. My goal is to actually have affected cells by this VBA code lock immediately after anything is entered in the affected cells. Area needing Debugging is in the If - Else portion. My worksheet will be protected.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LLoop As Integer
Dim LTargetRange1 As String
Dim LDestRange1 As String
LLoop = 10
While LLoop <= 1000
'Link column B to A
LTargetRange1 = "B" & CStr(LLoop)
LDestRange1 = "A" & CStr(LLoop)
If Not Intersect(Range(LTargetRange1), Target) Is Nothing Then
If Len(Range(LTargetRange1).Value) > 0 Then
Range(LDestRange1).Value = Date
Else
Range(LDestRange1).Value = Null
End If
End If
LLoop = LLoop + 1
Wend
End Sub
View 7 Replies
ADVERTISEMENT
May 28, 2014
What I want to do is if I add data into B7 or F7 then the current date will appear in J7. I also don't want the date to change every time the project is opened.
Thus,
If I enter "withdraw or any word" into B7 or F7 - The current date (4-Dec) would appear in J7.
then if I close excel and I open it tomorrow, J7 would still read as 4-Dec, while if I enter "deposit or any word" into B8 or F8, the current date (5-Dec) would appear in J8.
- would need to be able to enter multiple entries on one day as well.
- also i am not familiar regarding macros.
View 2 Replies
View Related
Feb 28, 2008
formula to auto input the date into a specific cell once one cell has been changed
View 6 Replies
View Related
May 13, 2014
On sheet 1, I have dates in column A, then numbers under LabA through C. On sheet 2, I would like the contents of columns B, C, D to autofill when entered. However, I would like to display only the last entry. For example, Lab A (column B) is currently showing "7" in cell B6, if I enter a number in B7, I would like for sheet 2 to autoupdate cell B3 and display that new entry instead of the "7".
View 1 Replies
View Related
Dec 14, 2011
I have a Change Event macro that works properly. I also have several cells that contain a drop down list. If a user tries to enter an incorrect entry...the debug is triggered on my even change macro.
Here's the piece of the Change macro that has issues. Specifically, the .undo line highlights.
With Application
.ScreenUpdating = False
.EnableEvents = False
Set SelectedCell = ActiveCell
myTitle = Cells(Range("Titles").Row, Target.Column).Value
myRow = Target.Row
.Undo: myBefore = Target.Value
.Undo: myAfter = Target.Value
SelectedCell.Select
View 3 Replies
View Related
Sep 18, 2013
I am trying to get excel to auto populate the current date and time each time I make an entry, however, each time i make an additional entry the current date and time populates but it changes all the prior entry's with that current date and time.I am trying to keep a log of all the times I make a new entry. Correct formula that will work?
View 9 Replies
View Related
May 26, 2009
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
View 6 Replies
View Related
Nov 20, 2009
I'm trying to create vehicle maintenance inspection program for a mechanic at work. The trouble is the he's not fond of computer and I'm trying to make it as simple as possible. There's a debug in the line
View 2 Replies
View Related
Mar 1, 2009
What could be the formula if i want a particular cell to auto sum the data input. For example if i click cell a1 and entered a number and again entered a number , the previous entry will be added to recent entry.
View 9 Replies
View Related
Apr 24, 2009
I'm running a macro to auto sort data and copy it to another page. One of the sort parameters is to only look at the data that has a blank in column 13. The code is as below:
View 2 Replies
View Related
May 19, 2009
If I have 3 fields: zip code, city and state; is there a way where I can have the user of my form enter their zip code and it will populate both city and state. I have the data, I just dont know exactly how to code it.
View 9 Replies
View Related
Aug 27, 2012
I'm creating an inventory of cars for a car dealership that has about 27 or so columns and 500 rows for entries. Currently there are 50 rows used to keep track of inventory and with the additional rows for future entries. I want to set it up where the data entered into the cells gets locked automatically when saved and reopened for future use and can only be altered if a password is entered. Table is C3:AE503.
View 5 Replies
View Related
Jul 31, 2012
I am attaching the example where I need to lock H2 to H1000, note that sheet is locked "anixter12 and I have also given range to user for modification I simply want to lock cells in H once date is entered.
View 3 Replies
View Related
Dec 6, 2008
Is there a way to make a cell change its fill color automatically whenever data is entered into that cell?
View 2 Replies
View Related
Aug 14, 2013
I'm trying to create a rental data base. The Items in question have 2 details, a Pokemon and a Gender. Each item also has a location but this changes.
So far the search formula works nicely, but there needs to be some way to know what items have been removed from their locations.
There needs to be some way to change the location on the 'database' sheet layer to "OUT" from the 'search' sheet layer.
E.g. (on the example file attached)
In a perfect world I would like to be able to search for a couple of pokemon, then to click on "pokeball GO!!!" and for this to change the location to "out" on the 'database' sheet layer.
View 1 Replies
View Related
Feb 28, 2007
How would I go about lock a cell after there is an entry. Once the entry has been made the user could not change the entry. For example if a user enter in cell b2 he could not change the entry, he could change a2, c2, d2. He would also be able to put an other entry in b3, once enter the cell is lock.
View 5 Replies
View Related
Jul 31, 2014
I need to know how i can select an option from a dropdown list and have it drew data from a table and place it in a certain cell based on my choice.
i have attached a sample form.
in the sample form i have two dropdown lists.
Countries and POD
What I need is when i select an option from the POD list the data on the Rate table will appear on the left side in order of course.
Attached File: test1.xlsx‎
View 3 Replies
View Related
Dec 31, 2009
=IF(OR(J4="",K4=""),"",NETWORKDAYS(J4,K4,Holidays!Z29:Z39)-1)
Above is the formula I am working with. I am inserting it into row 4 thru row 996 in a number of different columns. The auto fill function works great for this part of the formula….
=IF(OR(J4="",K4=""),"",NETWORKDAYS(J4,K4,Holidays!
However, this part Z29:Z39 I have to enter manually row by row until I can figure out a better way. Do you know an easier way?
To put this formula…
=IF(OR(J4="",K4=""),"",NETWORKDAYS(J4,K4,Holidays!Z29:Z39)-1)
Into any column row 4 thru row 996, without having to change Z29:Z39 for every row, since I cannot rely on autofill?
View 3 Replies
View Related
May 25, 2014
I am now trying to create a excel macro to auto populate all the dates with reference to a start date and end date. The catch is that only working days are required in the range. My reference cells (start and end date) are in Sheet 1 while the destination cell range are in Sheet 2. The reason for creating a macro instead of a function is that the intervals between the start date and end date changes frequently (annual, semi-annual and quarterly) Best case scenario would be a button which I can just press after i input the dates to generate the range of dates in another sheet.
View 1 Replies
View Related
Mar 6, 2014
I am working on a spreadsheet to create a school 'tracking' system based on excel. What I need is that as soon as a box in column B is ticked, a date (TODAY() is entered in column A. That is not so difficult, and I have used the formula in cells in column A:
=IF(ISBLANK(Bx);"";TODAY()), where x is the specific row.
So, keep cell A clear untill something is written in cell B. This works fine.
But, for the purpose I need to keep the TODAY() date fixed after entry. And it need to be erased again if the tickbox in cell B is emptied again. It need then to re-enter a new date after cell B is re-used.
View 5 Replies
View Related
Jun 19, 2008
I'm having a problem with data validation. I set an entire column so that it could only be a date between the first date in the list and the current date. I tested a few cells to make sure that it was working the way I wanted and I noticed that it allows a random number like 3 or 5 to be input after row 50. This would create a date in the 1900s. Why is this happening and how can I stop it.
View 9 Replies
View Related
Oct 13, 2009
i was looking for some assistance in regards to a formula i have used for ranking. i have attached a sample spreadsheet of what i have done so far but struggling to work something so thot id try here.
(Sample WB) Currently i have managed to rank in accordance to column E but as i have alot of 0% i would like to be able to rank the 0% in accordance to the number of work done (column C).
For example i have 62 0% answers and Username Q has actioned 316 jobs with 0 Failures so should be ranked 1st as he has the highest number of jobs.
View 12 Replies
View Related
Dec 3, 2006
Is it possible if 0 cells are filled with the word “out” in a column D7 thru D15 In work sheet team matches. To run a macro that will look up a column in another sheet A2 to A10 in work sheet Random Picks. And fill column D7 to D15 In sheet team matches with the contents of them cells.
If 1 cell is filled with the word “out” in a column D7 thru D15 In work sheet team matches. To run a macro that will look up a column in another sheet B2 to B9 in work sheet Random Picks. And fill column D7 to D14 In sheet team matches with the contents of them cells.
If 2 cells are filled with the word “out” in a column D7 thru D15 In work sheet team matches. To run a macro that will look up a column in another sheet C2 to C8 in work sheet Random Picks. And fill column D7 to D13 In sheet team matches with the contents of them cells.
If 3 cells are filled with the word “out” in a column D7 thru D15 In work sheet team matches. To run a macro that will look up a column in another sheet D2 to D7 in work sheet Random Picks. And fill column D7 to D12 In sheet team matches with the contents of them cells.
If 4 cells are filled with the word “out” in a column D7 thru D15 In work sheet team matches. To run a macro that will look up a column in another sheet E2 to E6 in work sheet Random Picks. And fill column D7 to D11 In sheet team matches with the contents of them cells.
If 5 cells are filled with the word “out” in a column D7 thru D15 In work sheet team matches. To run a macro that will look up a column in another sheet F2 to F5 in work sheet Random Picks. And fill column D7 to D10 In sheet team matches with the contents of them cells.
If 6 cells are filled with the word “out” in a column D7 thru D15 In work sheet team matches. To run a macro that will look up a column in another sheet G2 to G4 in work sheet Random Picks. And fill column D7 to D10 In sheet team matches with the contents of them cells.
View 9 Replies
View Related
Apr 12, 2007
I only grasp the basics of loops and not any experience w/ formulas in VBA to date.
For each item in col "I" run this formula (the cell references are relative)
=RIGHT(I6,LEN(I6)-FIND(" ",I6))
I am only at this point so far:
Sub ItemName()
Dim wb As Workbook
Dim ws As Worksheet
Dim i As Integer
Dim LRow As Long
Set ws = Workbooks("TGSItemRecordCreatorMaster.xls").Worksheets("Record Creator")
LRow = ws.Cells(Rows.Count, 9).End(xlUp).Row
For i = 6 To LRow
=RIGHT(I6,LEN(I6)-FIND(" ",I6))
End Sub
The basic function of the loop is to Delete the first term of each cell in the range including the first space.
example:
Enjoi Barletta Deck
Resulting in:
Barletta Deck
View 9 Replies
View Related
Aug 4, 2009
The will eventually end in a series of pivottables. I am currently at the step where I need to add vlookups. This is for an inventory management report that averages 20,000 rows and 20 columns of data. I add a few columns (using vlookups) to increase visibilty to the operations dept.
The first thing I need help with is creating the VBA language for using a vlookup table from another workbook that pastes the vlookup formula every cell to the bottom of the data
Then I need to utilize an IF formula for one column that says IF c2 is blank use vlookup-formula1, and IF it is not blank use vlookup-formula2, and then paste at every cell all the way down.
I hope someone can help, and I hope I didn't write too much to where everyone will skip pver this thread.
I am currently to this state, and ready to continue.
--------------------------------------------------
Sub OHRTODDI1()
'
' OHR1 Macro
' Macro recorded 7/22/2009 by iahopbxm
'
' Keyboard Shortcut: Ctrl+o
'
' Sort and Add column Headers
Cells.Select
Selection.ColumnWidth = 9.43...........
View 9 Replies
View Related
Jun 15, 2007
I get a "#value" error message when I utilize the formula noted below. I seperated it into distinct sections so that it is easier to view. Basically, what the formula is doing is determining whether if two values are the same, then take the absolute difference of the average of other values ,excluding one of the previously noted values, and compare that difference to a different parameter. If the difference does not exceed the parameter, then calculate the difference; otherwise the ending result is zero. Pls. note that the error seems to occur when I input the fifth section into the formula.
=IF(K23="","",IF(AND(K23=E23,ABS(B23-AVERAGE(F23:J23))>B20),B23-ABS(AVERAGE(F23:J23)-B20),
IF(AND(K23=F23,ABS(B23-AVERAGE(E23,G23,H23,I23,J23))>B20),B23-ABS(AVERAGE(E23,G23,H23,I23,J23)-B20),
IF(AND(K23=G23,ABS(B23-AVERAGE(E23,F23,H23,I23,J23))>B20),B23-ABS(AVERAGE(E23,F23,H23,I23,J23)-B20),
IF(AND(K23=H23,ABS(B23-AVERAGE(E23,F23,G23,I23,J23))>B20),B23-ABS(AVERAGE(E23,F23,G23,I23,J23)-B20),
IF(AND(K23=I23,ABS(B23-AVERAGE(E23,F23,G23,H23,J23))>B20),B23-ABS(AVERAGE(E23,F23,G23,H23,J23)-B20),
IF(AND(K23=J23,ABS(B23-AVERAGE(E23:I23))>B20),B23-ABS(AVERAGE(E23:I23)-B20),0))))))))
View 9 Replies
View Related
Jun 19, 2008
I am trying to use excel to forecast budget expenditure for the fiscal year, using actuals from months past + unknown (future months).
I have this formula in cell G2: =IF((worksheet A!G70>0),F2,IF((B2
>D2),(B2-C2)*F6+F2,IF((B2
View 9 Replies
View Related
Aug 4, 2009
Current Formula
{=IF(AND(G144>0,F144:F173
View 9 Replies
View Related
May 1, 2006
the scenario is a spreadsheet for adding call centre operators evaluations (1 being worst and 5 being best) from the clients. in the spreadsheet, i need to create a function to add a quick evaluation based on the operators score from the client
so if the score is 4 or higher it would read "good - improving"
and if it is less than 4 "poor - improving"
i think this is just a simple IF question...but i have forgotten how to do it
View 3 Replies
View Related
Jun 13, 2006
Im doing a year nine maths project and need a little help with excel. This is my first time using macros/VBA. I have been playing around and have figured out a few things. I was asked to produce a game using excel, i am doing a kind of "racing" game with betting, i have the racing and most of the betting all working fine. I need to know how to get a message box to appear when the total of a cell reaches 0.
View 5 Replies
View Related