Value Checking/limiting Macro --Tough One!!
Aug 25, 2006
I have a huge list of entries everyday (around 50,000) that needs a time stamp. Each of them has a time difference of 5 seconds in between. That is between two entries there is a time difference of 5 seconds. I use now() function to put the time stamp. The only problem is that I cannot have any value after 4:29:59 PM. That is, I need a macro that would check for the values in the C column, and if they are after 4:29:59 PM, put the value 4:29:59 PM, instead of the real time.
In cell C1:
IF((TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))) >(TIME(16,29,59)),(TIME(16,29,59)),(TIME(HOUR(NOW( )),MINUTE(NOW()),SECOND(NOW())+5)))
In cells C2:C9999999:
IF((TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))) >(TIME(16,29,59)),(TIME(16,29,59)),(TIME(HOUR(C1), MINUTE(C1),SECOND(C1)+5)))
However, the problem is I have so many entries that after a 10000 or so the time passes 4:30 PM. I think the only way to fix this would be to add a macro.
View 6 Replies
ADVERTISEMENT
Jan 6, 2009
I have this code that applies an Alt-Enter command following text being entered in a range named "ActivityRange". ActivityRange is only one row currently, but I have another macro that inserts a row into that range. This code works to add an Alt-Enter after text typed into a cell, but it does it for every cell in the row. I want it to only apply to column "C" and not any values in other Rows. In other words, only apply this command if values are entered into column "C", of the range "ActivityRange".
Anyone know how to alter the code below to accomplish this?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
If Application.Intersect(Target, Range("ActivityRange")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each Cell In Range("ActivityRange")
If Len(Cell.Value) > 0 Then Cell.Value = Cell.Value & Chr(10)
Next Cell
Application.EnableEvents = True
End Sub
View 9 Replies
View Related
Feb 4, 2009
I'm trying to calculate a sales run-rate which will change on a day-to-day basis, to predict the end-of-month sales total.
The invoice values are in the data range H17:H74 (I don't want the final formula to add up the refunds in this field i.e. negative values)
The date field is in data range C17:C74.
So basically the formula will need to add all invoice totals (excluding refunds) and divide by the current number of days worked in the month (not duplicating days in the date field) and then multiply this by the average number of working days in a month (21). This should should give a predicted end of month sales total.
Am I just making things up that are impossible to do on excel?
I've had a bet with the other guy in the sales office because I said excel can do a lot more than he thinks. He's under the impression that excel begins and ends with what they teach you at school.
View 12 Replies
View Related
Jan 5, 2010
I have created an image of what i want and hopefully this will explain better.
This is very hard but it is something really important for the company i work for and have an idea on how its done but not enough knowledge to implement yet.
View 9 Replies
View Related
May 16, 2009
I have checked the following reference whithin Excel VBA's references Manually:
Microsoft Visual Basic for Applications Extensibility 5.3
BUT when I run the following Macro, once checked the prior reference:
Sub AddModuleToProject()
On Error Resume Next
Debug.Print Err ' I GET 0
Dim VBProj As VBIDE.VBProject: Set VBProj = ActiveWorkbook.VBProject
Debug.Print Err ' I GET 1004
Dim VBComp As VBIDE.VBComponent: Set VBComp = VBProj.VBComponents.Add(vbext_ct_StdModule)
Debug.Print Err ' I GET 91
VBComp.Name = "NewModule"
Debug.Print Err ' I GET 91
End Sub
View 9 Replies
View Related
Mar 6, 2009
i have a problem counting the number of characters in each cell in column "A" in a sheet and checking if number of characters in a cell exceeds 5 characters.
View 10 Replies
View Related
Oct 4, 2007
I am creating an excel application and have come a bit stuck. I would like to able create a macro button for printing a blank version of the workbook...when I say blank I mean the cells that the end user is able to type into.
I think I am able to do this providing all the cells are on the same sheet as the button using this ...
View 10 Replies
View Related
Mar 6, 2009
i have a problem counting the number of characters in each cell in column "A" in a sheet and checking if number of characters in a cell exceeds 5 characters.
View 8 Replies
View Related
Jan 22, 2010
I'm trying to control the number of ways people can mess-up this worksheet. I have several buttons that copy one object from a worksheet "FISH PARTS" and paste them onto another "THE JUMPER FISHBONE"
My question is can I prevent people from copying objects from the first worksheet if they already exist on the second? Specific to the code below: how do I make sure "BONE_1" doesn't already exist on the worksheet "THE JUMPER FISHBONE" before allowing it to be copied from the other worksheet. And can I generate a message box that tells the user the object already exists on the page.
View 2 Replies
View Related
Oct 8, 2009
I've tried using multiple loops in the forum but cannot seem to figure out how to actually get them to work properly using the conditional VBA codes on two separate worksheets. The first code snippet is checking cell values from row 6 to 148 as such:
Sub Check_Shifts()
'Insure all shift entries are completed
If Range("K6").Value < "1" And Range("I6").Value < "1" And Range("G6").Value < "1" Then
Range("G6").Value = Range("F6").Value
Range("I6").Value = Range("F6").Value
Range("K6").Value = Range("F6").Value
ElseIf Range("K6").Value < "1" And Range("I6").Value < "1" Then
Range("I6").Value = Range("G6").Value
Range("K6").Value = Range("G6").Value
ElseIf Range("K6").Value < "1" Then
Range("K6").Value = Range("I6").Value
End If
If Range("K7").Value < "1" And Range("I7").Value < "1" And Range("G7").Value < "1" Then........................
View 7 Replies
View Related
Aug 1, 2012
I am writing a macro that will allow me to copy all the data in a set range (A2 and below) after checking that B1 contains the text "Year_id".
Right now, I am able to copy all the information, and paste it onto "Sheet 4". I am unable to code for the part where the macro would check for the text. The code that I have (for copying-pasting the date) is below.
Any code that would check the information in B1 into this macro code below:
Code:
Sub Copy_Allinfo()
Dim Sht As Worksheet
Dim Rng As Range
For Each Sht In Sheets
If Sht.Name = "Sheet4" Then
[Code] ........
View 7 Replies
View Related
Aug 19, 2013
I would like to have a macro designed. I have 2 workbooks, one having latest information and the other having the information in database already. both workbooks have 26 columns each. I would like to check if a value that exists in Column A of the "latest-information" workbook also exists in the "already-in-database" workbook. If so, I would like to update all 26 columns of that row in the "already-in-database" workbook with information from the "latest-information" workbook. If the value does not exist, I would like to add all 26 fields in that row into a new sheet on the "already-in-database" workbook.
View 3 Replies
View Related
Apr 25, 2007
In one sheet i have a list from cell a1:a1000. I have named this selection 'headkit'
In another sheet, i am using 'headkit' to make a drop down list in a cell.
At the moment a lot of headkit hasn't yet been filled and contains lots of empty cells that will be filled as time goes by.
The drop down list currently has all the items in the list including the blank cells therefor making a very large blank list.
Is it possible to limit the list to show only cells with an entry in them?
View 9 Replies
View Related
Dec 5, 2009
I created a very simple user form to input some data into a spreadsheet. I have a list box and this is my button code to input the data to a cell.
View 6 Replies
View Related
Jul 29, 2006
I need a few of my textboxes to not accept entries of greater than 40, Also need a message box warning to popup if this is done. I've seen some examples that are vaguely similar, but not quite what I need. I already have the following in place to limit characters:
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case Asc("0") To Asc("9")
Case Asc(".")
If InStr(1, Me.TextBox1.Text, ".") > 0 Then
KeyAscii = 0
End If
Case Else
KeyAscii = 0
End Select
End Sub
Can I add to this or do I need a different sub?
View 9 Replies
View Related
Mar 20, 2007
I have a calendar that enters a date into a spreadsheet cell. How can I modify the code to limit the day selection to a Monday?
The current code is:
Option Explicit
Private Sub Calendar1_Click()
Range("V2").Select
ActiveCell = Calendar1.Value
ActiveCell.NumberFormat = "dd/mm/yy"
frmSchStartDate.Hide
End Sub
Private Sub UserForm_Activate()
Me.Calendar1.Value = Date
End Sub
View 9 Replies
View Related
Dec 15, 2008
I work for a localizer of text heavy software. Our developers normally use a converter to pull text out of a file and insert it into the program. This text is normally input into an Excel file since it has to correspond with files/locations in the program.
Since the space on the screen of the program is limited, we often face character limits when inputting text into Excel. Now, one cell might hold up to 255 characters, and I could restrict that with data validation, but within one cell I need to restrict each line (with a hard return) to 30 characters to fit on the screen.
So for example I have a row of cells that look like: ....
View 9 Replies
View Related
Apr 1, 2009
Is there a way to limit the range of values on the Y-Axis on the lower end? Like I don't wish the Y-axis to begin from 0 but say 500. Is that possible?
I have a set of data values ranging from 650 - 850, but the Excel graph however looks too fine because the Y-axis begins from 0.
View 6 Replies
View Related
Jan 5, 2009
It's some code I found and altered that automatically updates the cell to the right of a column that has drop down box data validation, creating a list of the selected values seperated by commas. The thing is, I only want it to do this in columns 8 and 10 but data validation drop-down boxes have been used in several other columns. I've tried various methods of limiting the scope, the latest being the line: If Target.Column = 8 Or 10 Then. But the auto update is still being applied to all drop down boxes in the sheet. So, how can I make sure the auto update only happens when the drop down boxes in columns 8 and 10, updating into columns 9 and 11.
View 4 Replies
View Related
Jul 26, 2009
there is limit of argument in a function that can i put. for example i am not able to figure out why this function does not work
View 2 Replies
View Related
Jan 21, 2009
I'm creating a spreadsheet (Excel 2003) in which a user enters data in several cells, each of which will permit different numbers of characters (to include spaces). For example, in the first cell, the user will be limited to 50 characters, in the second cell, the limit is 30 characters, and so on.
I found the data validation error alert feature, but want to give the user a cue that the entry is too long so they know to stop typing before moving to the next cell. If they only are alerted when they finish making the entry, they might not take the time to properly reconstruct the entry to meet the size limitation. I'm trying to make this more user-friendly.
Is there a way to set up the worksheet so the user knows that the entry is approaching the character limit? For example, each character filling an individual cell or having a display appear with a count-down for the number of characters remaining in the limit, or something similar.
View 6 Replies
View Related
Jan 3, 2012
I have a spreadsheet that will always have the dates needed in cells H40 and H42. I would like the calendar to utilize the data validation in the cell to only allow someone to choose a date that is between the dates in H40 and H42.
Code:
Private Sub Calendar1_Click()
'ActiveSheet.Unprotect Password:="pbrmeasap"
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.NumberFormat = "mm/dd/yyyy"
ActiveCell.Select
Calendar1.Visible = False
[Code] ...
Could I add this code anywhere to make it work?
Code:
Sub DataValidation()
With Selection.Validation
.Delete
.Add Type:=xlValidateDate, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$H$40", Formula2:="=$H$42"
[Code] ......
View 4 Replies
View Related
Jul 12, 2013
Is it possible to limit a text box to 6 lines (whether forced or automatically wrapped).
View 2 Replies
View Related
Jan 9, 2014
I need to limit the number of Characters that can be inputted to the popup InputBox to just 31. How do I set MaxLength? Code I am using is listed below.
Code:
Sub NewRecipeSheet()
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("1. Recipe Master Sheet")
ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
Application.Goto Reference:=Sheets("1. Recipe Master Sheet").Range("A1")
Range("A1").Value = InputBox("Menu Item Name?")
End Sub
View 5 Replies
View Related
Sep 18, 2008
When I limit the scrolling area in a worksheet, and it works there and then, when I close and reopen the whorkbook, the limiting does not work.
This is how I do it:-
I Right-click the sheet tab for the sheet which I want to place a limit.
In the resulting Context menu, I choose View Code. The VBA editor appears and asks for the password to view the code. I keyin the password
I then set in my limit as B2:O25
Then I close the VBA Editor and display the worksheet where I try and I find it working.
I close and save the workbook.
When I reopen the limiting has disaapeared!
View 9 Replies
View Related
Oct 23, 2008
How do I stop a user from entering more than 2 decimal places?
If I format the cell to "0.00" I can still type in 1234.1234 and it will display 1234.12.
If I select that cell, the value in the formula bar is still 1234.1234.
View 9 Replies
View Related
Nov 19, 2008
Here's my problem. In column B, i wish to allow users to choose 15 items for which i have created a list using validation. Each cell in the range B1 to B15 will contain one item. I have managed to allow them to select only 15 items by locking the other cells and allowing only the range B1 to B15.
However, i need prevent users from entering more than 5 identical items from the list. They can only choose a same item up to a maximum of 5 times.
Would you guys have an idea of how i can get around this problem? I really need to prevent them from entering the same item from the list more than 5 times and not only warn them.
View 10 Replies
View Related
Mar 22, 2013
I've got a worksheet that needs to be limited by the value in a cell. I've attached it so you can look at it and understand what I need better. In I7 (red box) is a number that is the max a machine can rotate. B6 should never exceed this and B9 should be scaled down to match the output of B6. I think B9 will scale down automatically from the existing formulas if B6 is throttled correctly.
Should =FLOOR((((F5*D11)/D9)*3.82),50) have a max value equal to I7? And how would I incorporate it?
View 1 Replies
View Related
May 24, 2013
I have a spreadsheet with data in every other column (i.e. A, C, E, etc.). I am using the "filter" option in Excel on the aforementioned range of cells although the little filter icon shows up on all columsn, including the blank columns in between my data. Is there a way to only have the filters show up on the columns with data?
View 2 Replies
View Related
Aug 15, 2013
Im building a booking/diary system for work.My system runs 9am till 5pm, 10 min slots. The team is 4 people. So I have 4 columns, one per team member, and each row is 10 mins. 9am, 9.10, 9.20 and so on.
I want to limit it so that if say 2 people already have something in at a time it wont let another person type in that slot. I would prefer a none VBA sollution if possible due to the ancient computers in running it on, but not too fussed if thats the route I go down (as I suspect I will)
The sollution I was considering was having a COUNTA for each range, and if it is 2 or over, not let people type into the other slots. This would also let me add exceptions by using a COUNTIF to minus other characters if needed, but im not sure if I can make excel "block out" a time slot. My other sollution would be similar, but have them to autopopulate the empty cells with an "X" if they are 2 or over as thats what I use to block out slots (breaks, lunches etc)
My other problem is that if they then type into the cell, it will obviously type over the formula, which makes me think it will have to be VBA.
View 2 Replies
View Related
