Complex Character Combination Validation Code
Dec 15, 2009
I really need a validation code for Cell B15. I realize that a macro could do this, but a validation code is what I really would need:
Cell B15 can only allow at least one of the following values, or two or more of the following values separated by '&' (Note the spaces between the digits):
I
I I
I I I
IV
IA
I IA
I I IA
IVA
or (some combination examples):
IA & I I I
I I & I I IA
I VA & I IA
If the user fails to meet these requirements, then he should get an error message telling him to try again.
View 14 Replies
ADVERTISEMENT
May 19, 2009
I am having a difficult time with a look up. It would be very hard to explain so I'll attach a copy of the section of the worksheet that the problem lies on with comments so you can see whats going on. The problem there is a numbered list with a reference number i can't seem to figure out a lookup that will look in the chart above and find the row associated to the reference number and according to how many before it have that reference number find a secondary reference number listed in the column above. The attachment should clear it up.
View 4 Replies
View Related
Aug 20, 2012
I want add a short cut key combination to trigger to code currently in the worksheet section, at the moment it is triggered by a double clck in a row. [URL]....
View 2 Replies
View Related
Nov 13, 2009
I wish to prevent the use of the " ' " apostrophe in an entire column and I was wondering if this can be done with conditional formatting including a message of some type.
View 4 Replies
View Related
Jan 28, 2010
My problem is that I have a worksheet tab (RawTimeSheetData) which contains a whole series of week/timecode values for a range of people.
I want to accumulate the hours for an invoice period / job code combination. As an example in the tab InvoicePeriodSummaryTimes cell D6 i want to sum all the hours from RawTimeSheetData where both cells A6 & B6 from InvoicePeriod tab = cells D6 & E6 from the rawdata tab.
View 6 Replies
View Related
Jan 5, 2014
This is my data validation formula:
=INDIRECT(VLOOKUP(H104,cat_opt_Table,3,FALSE))
Some of the lists will have up to 200 lines in them and I'd like to find a way to simply to jump to the first letter entered in the data validation cell.
Does the list need to be alphabetized?
Could it be something that is entered into the data validation box? This spreadsheet has data validation in 35 columns.
View 2 Replies
View Related
Dec 8, 2008
I have a set of Hex Code a total of 4 Sets :
A4EC 9ACF 4A02 044E
1. What i need to do is Read the 1st Character in the Hex Code which say A now.
2. Now I need to find if there is another A in this Hex Code. If yes - e.g
A4EC 9A - I need to find the difference between A-A. How many characters are in between the 2 A-A. So for this I have A 4EC9 A so the difference is 4.
2.1 - Another Senario of "A" - A 4EC9ACF4 A the difference is 8 characters
3. Next we will move on to the next Letter which is 4 and do our search.
The key is i only want to record those Hex code which have a difference of 4,8,10,12 differences.
So for that above example I should be able to record the following :
A 4EC9 A == 4 letter difference
4 A020 4 == 4 letter difference
A 4EC9ACF4 A == 8 letter difference
4 EC9ACF4A020 4 == 10 letter difference
E C9ACF4A02044 E == 12 letter difference
View 4 Replies
View Related
May 20, 2009
Find the ASCII character code for the characters in cells A3-D3? I have a problem importing data into the database because of these cells. They have some weird properties: ISBLANK would return them FALSE; however if I leave blank space in "Find and Replace" it will find and replaces those cells together with cells which are genuinely blank. So I could theoretically go and replace all blank cells to some character (for example ^), then do the opposite and replace ^ into blank and it would solve the problem, but I am curious as to what exactly this character is.
View 14 Replies
View Related
Jun 10, 2009
For icount = 1 To LenComputername
valComputername = Asc(Mid(UserComputername, icount, 1))
Next icount
If the computer name is NAMTOK-PC Then the LenComputername is 9. Does that mean then that the valComputername is equal to 78?
View 2 Replies
View Related
May 18, 2009
My question is about converting a Literal String to Character Code. I'm using the following coded InputBox. And it prompts the user for what characters to search for in a string.
View 4 Replies
View Related
Feb 18, 2010
In any cell of a worksheet, the user can input a number followed by either a plus or minus sign. After the - or + key is pressed, the cell has to move the plus or minus to the front of the number, then move down one cell. So input in cell A2 would be 125+, that needs converted to +125 and then moved down to cell A3.
View 3 Replies
View Related
Oct 16, 2009
Does such function exist in Excel?
View 9 Replies
View Related
Dec 21, 2012
I have letter and number combination code in two collumns and they differ for 10.000 numbers:
BAM98314
BAM88314
BAM90000
BAM80000
As you can see the left code is for 10000 numbers higher. the letters are allways the same. In the event that this isn't so, if difference between codes in same row is more or less than 10000 numbers. I was thinking on making conditional formating so the cells with wrong difference would be marked red, but I do not know how to make formula for this difference.
View 2 Replies
View Related
Nov 3, 2012
Junior Fit Softstyle T-Shirt
Antique Cherry Red
Junior Fit Softstyle T-Shirt
Antique Cherry Red
Softstyle T-Shirt
Antique Heliconia
Softstyle T-Shirt
Antique Heliconia
Softstyle T-Shirt
Antique Heliconia
This is column a and b. Looking at b I am looking for a formula that will pull the first capital letter out of each word like ACR to create color codes.
View 1 Replies
View Related
Jul 12, 2012
I have a formula that I can't get to paste successfully in the forum - it keeps getting cut off?!? ... but I think I can probably simplify my explanation to get the answer I want anyway.
I need to only show the value from AUS!$H$2:$H$17 if the C2 & D2 combination are the same as the AUS!$B$2:AUS!$B$17 & $AUS!$C$2:AUS!$C$17 combination.
View 9 Replies
View Related
Feb 9, 2010
I want to validate the length of data is inserted into the cell as integers only, so im wanting it to be 16 digits long (no shorted or longer). I've already managed to make sure that only numbers can be used using the code below :
ElseIf Not IsTextAllNumeric(Range("H23")) Then
MsgBox "Card number must be digits only" & vbCrLf & _
"Please re-enter the card number", vbOKOnly + vbCritical, "Data Error"
Range("H23").Select
Range("H23").ClearContents
Now im not 100% sure on how to make sure that the length is a certain amount of digits. I've babbled on for a bit on a not so complicated question.
View 9 Replies
View Related
Nov 27, 2012
To all sifus out there, how can i transfer from these:
NAS517-3-2
-41353913
NAS517-3-5
NAS517-3-4
-42MS27253-2
-43353908
-44357182
To these:
NAS517-3-2
353913
NAS517-3-5
NAS517-3-4
MS27253-2
353908
357182
View 1 Replies
View Related
May 18, 2008
I have got a list of numeric abbreviations, for instance 10739011/21/31/41. What it should really display are the numbers 10739011, 10739021, 10739031 and 10739041 (the first six figures stay the same). All the numbers in my list are 8 figures long. I want to change the list from the list seperated by the backward slash to the complete numbers. I have uploaded an example of the list with backward slash between the numbers. Is there a way that Excel can automatically change these numbers to the full numbers?
Because all the numbers are 8 figures long, I thought the first 6 figures of the 1st number can be copied and those 6 figures pasted before the other two figures after the backslash. Auto Merged Post Until 24 Hrs Passes;sorry, pressed OK too quickly. The problem is that there are sometimes 4 numbers in the cell, sometimes 6 and once three. I would like Excel to complete all the numbers in the cell and then move on to the cell underneath it and so on. Also, I would like each number to have it's own cell.
View 5 Replies
View Related
Mar 20, 2009
I have attached a file where I’ve made some dependent validation lists. When the first box is changes it clears the last 2 with this bit of code
View 2 Replies
View Related
Jul 20, 2006
change the below code from a worksheet_change to a worksheet_calculate method. and still do the same action. the reason I am changing methods is due to the fact that the validation will not trigger the worksheet_change event to fire. this is my attempt to find an alternative way to fire off the macro.....
View 9 Replies
View Related
Jul 10, 2012
I have the following code to check the date of birth entered into cell C18 and to alert the inputter if the age is either under 16 or over 25.
VB:
Sub AgeValidation()
Dim age As Integer
Dim dob As Range
Dim AgeMsgAnswer16 As String
Dim AgeMsgAnswer25 As String
[Code] .....
It works to some extent in that the correct message box pops up if the age is under 16 and similarly if over 25. If answering Yes on either message box then the correct thing happens. Its what happens if the inputter selects No thats not right. If the age is under 16 and the user selects No in answer to "Is this correct?" then the code is clearing the cell contents and showing the calendar again but is also popping up the 'Over 25' validation message box which then won't go away until Yes is selected. Also there are then multiple copies of the calendar open.
What I need the code to do is look at the date selected from a popup calendar in c18 and decide if that age is within the 16-25 year old range. If it is outside that then the inputter needs to be alerted to it. I can't use the inbuilt data validation because there are some scenarios where it would be acceptable to have an age outside of that range but we want to cover inputting errors as well as double checking the age.
When a msgbox pops up to alert the inputter and they choose "Yes" to say the date of birth is correct then I want the focus to go to cell C20 ready to input the next piece of information.
When the inputter selects "No" on the message box, then I want the original date to be deleted and the calendar to reappear so they can select another date. So effectively resetting the field so they can start again choosing a date like when they first entered the cell.
View 4 Replies
View Related
Sep 19, 2013
I found a code on [URL] that claims that it will do what I want to achieve. I tried it but it is not working for me. Here is the code
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim str As String
[Code]....
How to verify that this code is a working code and not just bogus one?
View 1 Replies
View Related
Mar 1, 2008
I have written the below code to enter some formulas, validation, and formatting. I keep running into a problem when trying to add the first validation.
The debug msg is...
Run-time error '-2147417848 (80010108)':
Automation error
The Object invoked has disconnected from its clients.
Debug highlighted portion is...
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=PRODUCT" ....................
View 9 Replies
View Related
May 8, 2009
very complex spreadsheet for weight & balance calculations. It's to the point where everything works perfectly in Excel 2007, but it must be used primarily with Excel 2003. Discovered that a crashing problem had to do with condtional formatting, that's all been cleared and will soon be fixed, but there's one that I just can't quite figure out.
I'm using the worksheet change event to trigger the update of charts... In this case, it's looking at a particular cell that has data validation on a dropdown as the trigger. Works perfectly in 2007, and if I put a msgbox prompt in to be launched by a change of that cell, it launches. I can put the chart update code in a separate sub and launch it manually every time, but I cannot call it from the worksheet change.
I've included attachments showing what I believe are the relevant bits of code -
View 9 Replies
View Related
Sep 29, 2007
I am using following code to apply data validation list. However this code gives error Intermittently. The error message is 'Application defined or Object defined error'. Also this code looks little cumber some. Will really apprciate if any one can put some ligh on why is this error appearing sometimes. Do I need to change something in the code or add few extra lines. I feel following points will be necessary when you check my code
1. The data of validation list is stored under the column heading 'Reported_By_List'
2. Validation is applied on the range under the column heading 'Reported Through'
3. Start and last Cell Address Of the cells of the column in which data of validation list is
stored are extracted into the variables 'StartCellAddress1' & 'LastCellAddress1 '
4. Start and last Cell Address Of the cells of the column on which data validation will be
applied are stored are extracted into the variables 'StartCellAddress2' &
'LastCellAddres2'
Sub Validation1()
Dim wsName As String
Dim RValue As String
Dim WorkBookName As String
Dim StartRow As Integer
Dim StartColumn As Integer
Dim LastRow As Integer
Dim CellAddress As String
Dim ColumnAddress As String
Application. ScreenUpdating = False
WorkBookName = ThisWorkbook.Name
wsName = ActiveSheet.Name...........
View 3 Replies
View Related
Mar 28, 2012
I am currently working on spreadsheet which is using a mix of Conditional Formatting, Validation and a bit of VBA code.
The spreadsheet is a log of vacancies and applicants, When "vacancy" is selected in A I have introduced some VBA code to lock down cells S:AT on the same row.
The code is working but the issue is it is stopping you using the drop down validation throughout the sheet, if you click the arrow that appears on the cell nothing happens, However you can still access the dropdown by right clicking and choosing the 'Pick from Drop-Down List' Menu Item.
The validation is set up by ranges from another Worksheet.
The Code I am using is:
Private Sub Worksheet_SelectionChange1(ByVal Target As Range)
Dim myrow
Dim StarLock, EnLock
[Code].....
View 2 Replies
View Related
Sep 20, 2009
I have a cell which will contain SER01+SER02+SER03
and what i need it to contain is [SER01]+[SER02]+[SER03]
and shocker is i've got this to work for the first instance but not the other two
code as below... be grateful for your help
Sub measure1()
Dim list As String, pos As Integer, refl As String, refr As String, newlist As String
list = Cells(1472, 16).Value
pos = InStr(list, "+")
refl = Left(list, pos - 1)
refr = Right(list, pos + 1)
newlist = "[" & refl & "]"
Cells(1472, 17) = newlist
End Sub
View 9 Replies
View Related
Mar 15, 2008
i have this formula that is complex ( atleast it is to me ) and i need to add more to it. Here is the formula:
=VLOOKUP(" "&LEFT($A5,FIND("[",$A5)-2),'7E'!$C$2:$D$682,2,FALSE)*0.99
What i am needing is, when it gets the price from another worksheet, if that price is below 0.04 that it makes it 0.05
View 9 Replies
View Related
Oct 12, 2011
I have a dropdown list in C24:C50 (=CategoryList) with data validation and a sub list in D24:D50 (=ItemList) with data validation. I am looking for a way to have code automatically run after selecting an item in the data validation dropdown list in column C.
Example; I click on C24 and make a selection. I what it to trigger code that would move me to D24 and open up the data validation list in D24. After the selection in D24 I would like it to move me back and down 1 row to C25. I have not found anything directly related to this but I have found that code can be run after a selection in a valadition list.
View 2 Replies
View Related
May 30, 2014
code that will be able to lookup a cell in Column J of Sheet1 by using a lookup value on Sheet2. The lookup value can be any cell in columns B, D, F, H, J, K, or N on Sheet2. The tricky part is, I want the result of this lookup (the result comes from Column H of Sheet1) to be placed as a data validation input message for the cell directly to the right of the lookup value when this cell is clicked.
For Example: Lookup Value is "416991" which lies in cell N8 on Sheet2. Look it up in Column J of Sheet1 and return the appropriate value "X" from Column H of Sheet1. "X" will then appear as an input message when cell O8 of Sheet2 is clicked.
View 1 Replies
View Related
