I have a userform which has a textbox for employee id. If an employee enters less than 5 digits, it should prompt the user via msg box that he needs to enter 5 digits for employee id. However my coding doesnt work...can someone help me correct it?
View 6 RepliesThis is a lottery challenge that I am facing at work. 6 numbers are chosen from 45 on a weekly basis. Using about 20 years worth of information, I have code that provides a dynamic frequency list in descending order.
I'd like to now have the user pick a number from the above list and see what other numbers have come up with these frequencies. I guess ultimately I'd like 2 or 3 "favorites" with the corresponding frequencies. Trouble is I've been going around in circles with nothing working
I have some code where I need to check if the first five char are numbers and not letters.
I have in a column for example
12345-someone is here
23456-someone else is here
someone is here too
I need to get all of the ones that have 5 digits and not pull in the other into a new list.
I have tried Left(CPHierAll. Cells(CPHierAllRow, 1),5) which will get me the fist 5 char. and then i need to check to make sure that they are all numbers and not char.
if Left(CPHierAll.Cells(CPHierAllRow, 1),5) = "#####" then
But this does not bring anything in.
I also tried if CPHierAll.Cells(CPHierAllRow, 1),5) = "#####" then
I have 3 column that i am checking for different thinks the first two work just find and seperate out on the check but the last one with the numbers is being a pain.
I attempted to copy the cells (which had formulas) from one workbook worksheet into another workbook worksheet. It didn't paste properly, so I deleted it and resolved to enter the formulas I needed manually one by one. The formulas reference values in other worksheets within the same workbook. The issue is, now, for some reason when I attempt to enter the formulas, I get an "Update Values" prompt and the formulas won't reference the sheets within the same workbook anymore. Is there a way to stop this prompt from appearing? and unlink the workbooks?
View 1 Replies View Relatedi have attachmed excel file. in that file total 8 colums among them i kept 8th colum empty .
but i want name racks in that file. i knw..it will confuses u. but once u see my attachemts u will realize.
eg: if G2 =XA06-G-001 I2 will be XA only..(XA is rack Name)
if G2 =XB06-G-001 I2 will be XB only
if G2 =XC06-G-001 I2 will be XC only
if G2 =XD06-G-001 I2 will be XD only
i mean to say..i want only first two alpabatics of G2 in I2..
i need to format my numbers in the following format
10,00,000.00
the first three digits will be separated and then subsequently 2 digits
Need a T/F formula that will return T if any 2 of the 3 values from previous row are double digits and the third digit is also from previous row. The 3 digits in the same row must have a zero.
Examples
668 T
806
995 T
509
995 F
519
******** ******************** ************************************************************************>Microsoft Excel - CASH 3 EVE COMBO GROUPS.xlsx___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA3=ABCDE302/06/09006T402/05/09016F502/04/09110T602/03/09610F702/02/09661T802/01/09160FSheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I have the following working great, but would like to see it refine a little, as the data vlookup is 6 digits, but i only needs the last 4 digits is enough for me to work, my question is how do i go about adding that to the following function i have implemented and working fine.
=IF(ISERROR(VLOOKUP(B4,' cmfs01home$peter[tracker data 4-25-14-a.xlsx]ControlSheet'!$B$2:$F$301,4,FALSE)),"",VLOOKUP(B4,' cmfs01home$peter[tracker data 4-25-14-a.xlsx]ControlSheet'!$B$2:$F$301,4,FALSE)
X-Axis = Week1:Week13
Y-Axis =Revenue for each week.
I have a line graph. If "Week9:Week13" have no value, my graphs line drops to 0. How do I make my line graph show no value when no value is inputed for "Week9:Week13"? I think I need to incorporate the =NA() in the original formula but I'm not sure how to do this.
was looking if you could change a row of cells background colour if certain word is inputted elsewhere. in b2:f2 is data- was looking if, could these cells background colour change when yes is entered in G2? does not matter which is used, VBA or con formatting just cant work it out.
View 9 Replies View Relatedi am going to be making other columns with data. i want it to highlight the cell from the new columns if the value is higher than the standard.
not quite sure how to do this? do i make an if statement and put it in the cell, then go and input my data over it?
What i would like to happen is after inputing a number into say column B have that number update a formula in cell D2.... so for example... lets say i have
B1 = 1000
B2 = 2000
B3 = 1400
B4 = ???
D2 = (B3-B2)/B2
is there a way to make it so when i enter a new number in B4 it will automatically update the formula to display the value for (B4-B3)/B3 and then continue on to repeat this process when i enter values into B5, B6, B7 ect?
I want to have a variable range.
ie. This value M4 needs to change based on the week.
Range("M4").Select
If it's week 1, then it can be say M4, but week two will need data to go to N4.
I have found out how to get the week number from the user. ie.
' Select Week Number
Dim NumSheets As Integer
Dim Prompt As String
Dim Caption As String
Dim DefValue As Integer
Prompt = "What week do you want to get data for?"
Caption = "Week Number"
DefValue = 1
NumSheets = Val(InputBox(Prompt, Caption, DefValue))
If NumSheets > 52 Then MsgBox "Week Number too high"
If NumSheets < 1 Then MsgBox "Week Number too low"
I tried then linking this value saved as NumSheets by:
Dim Rng As Range
If NumSheets = 1 Then Rng = Range("D3")
If NumSheets = 2 Then Rng = Range("E3")
In a column I have data like 2W, 2BM, 4W, 6BM, 10W and 15BM. Question is how can I total all W and all BM. Total for W = 2W + 4W+10W = 16W and total for BM = 2BM +6BM +15BM = 23BM.
What formula should I used to get totals
When using the replace rule in Excel, you can use "?" to represent a standard variable, e.g. if you had the text "Bottle 100g", and you set the replace rule as "?g", it would delete the "g" and the four characters before it, leaving you with "Bottle".
However, if I have lots of replace rules to make in a file, and I only want to eliminate the numerical values before a letter, how do I go about doing this?
For example, if I had the text "Dog food 10g", and I made a replace rule "??g", it would leave me with " food ". What I would really need, is a way of taking away the numeric values before the "g", which would give me "Dog food".
Therefore, is there a unique identifier for numeric and/or text values?
I need a left side padding for numeric values to keep them in 4 digits
ie 1 becomes 0001, 25 becomes 0025 and 345 becomes 0345. Following is the custom number format I tried and it fails.
[<10]"000"#;[<100]"00"#;[<1000]"0"#
I have the following code and I am really confused to, why its not reading numeric values from the code below:
Code:
Sub MG08Aug32()
Dim Rng As Range
Dim Dn As Range
Dim n As Integer
Dim Num As String
Set Rng = Range(Range("E2"), Range("E" & Rows.Count).End(xlUp))
[code]....
I desire the following output below:
97.25-97.50a
97.25
97.89-90.50a
97.89
89a
89
98a
98
I have the following issue:
=AR85*36*75*6.29
The above formula does not return any value and it simply appears as it is keyed. Cell AR85 has 5. I know it is something to with this cell, but I cannot fix it. In fact the whole Column AR has these values downloaded. I tried "text to columns" but it still doesn't work.
The Macro below will not combine numeric values Just Text. I get a Type Mismatch Error When I open It. I need for this macro to run with Numeric Values 1,2,3,etc.
View 2 Replies View RelatedI am editing a wine database which contains a vast amount of data, one column has the wine name and sometimes the vintage year in the begining or at the end of the cell. Sometimes the year is made of 2 digits (03, 05, ..) or 4 digits (1978, 2004, 2005, ...).
Is there a way to remove this vintage year form the string?
to make matters worse, there is often a single quote/apostrophe in front of the vintage year, which is driving me mad as 98% of the time it is one of these hidden ones that cannot be deleted using the find/replace function.
examples are like below:
De Wetshof Finesse/Lesca Cahrdonnay ‘07
De Wetshof Sauvignon Blanc ‘07
Lord Neethling Cabernet Franc 2002
Lord Neethling Pinotage ‘01
Bouchard Finlayson Tete de Cuvee Pinot Noir ‘07
Jacobsdal Pinotage 1994
Zondernaam Sauvignon Blanc 2007
Tokara Red
1976 St Emilion
03 Tokara rose
Plasir de Merle Cabernet Sauvignon ‘05
DuToitskloof Pinotage/Merlot/Ruby Cabernet
1999 Tradition Juracon 375ml
I have been searching the Internet for the past 2 days without luck on how to delete the end of string vintage year.
I have had some luck with the left side, as in:
=IF(ISERROR(VALUE(LEFT(B2,SEARCH(" ",B2)-1))),B2,MID(B2,SEARCH(" ",B2)+1,LEN(B2)))
As I am not an expert with Excel, I have no idea on how to use VBA (every time I have tried even basic things, I failed) nor even sure how the above funtion works (found it on another site).
I thought I could acheive my goal in two steps, first removing the left side vintage and use this partial result with the RIGHT equivalent funtion, but it simply is not working!
I am editing a wine database which contains a vast amount of data, one column has the wine name and sometimes the vintage year in the begining or at the end of the cell.
Sometimes the year is made of 2 digits (03, 05, ..) or 4 digits (1978, 2004, 2005, ...).
Is there a way to remove this vintage year form the string?
to make matters worse, there is often a single quote/apostrophe in front of the vintage year, which is driving me mad as 98% of the time it is one of these hidden ones that cannot be deleted using the find/replace function.
examples are like below:
De Wetshof Finesse/Lesca Cahrdonnay ‘07
De Wetshof Sauvignon Blanc ‘07
Lord Neethling Cabernet Franc 2002
Lord Neethling Pinotage ‘01
Bouchard Finlayson Tete de Cuvee Pinot Noir ‘07
Jacobsdal Pinotage 1994
Zondernaam Sauvignon Blanc 2007
2003 Tokara Red
1976 St Emilion
03 Tokara rose
Plasir de Merle Cabernet Sauvignon ‘05
I have been searching the Internet for the past 2 days without luck on how to delete the end of string vintage year.
I have had some luck with the left side, as in:
=IF(ISERROR(VALUE(LEFT(B2,SEARCH(" ",B2)-1))),B2,MID(B2,SEARCH(" ",B2)+1,LEN(B2)))
As I am not an expert with Excel, I have no idea on how to use VBA (every time I have tried even basic things, I failed) nor even sure how the above funtion works (found it on another site).
I thought I could acheive my goal in two steps, first removing the left side vintage and use this partial result with the RIGHT equivalent funtion, but it simply is not working!
Does anyone have an idea on how to help with this?
Ideally I would love to cut the vintage year, whether 2 or 4 digit, whether on right or left of cell and paste it in another cell, so to avoid manually doing it.
However, this is surely too complicated to do, so iwould settle with just deleting the vintage year and manually typing the vintage in another cell.
I'm trying to show learner progress during our half term collections but the values we track are similar to that below
2B+
2B
2B-
2C+
2C
2C-
1A+
1A
When I attempt to make a chart in the normal format it doesn't recognise the value.
My colleague has a problem where he needs to Extract numbers from alphanumberic strings. Here is the formula he is using:
=MID(B7,MATCH(TRUE,ISNUMBER(1*MID(B7,ROW($1:$297),1)),0),COUNT(1*MID(B7,ROW($1:$297),1)))
It works if alphabetic and numbers are clustered together such as scs987
It works if alphabetic and numbers are not clustered together such as scs987dtg
Problem: It does not work when numbers are not clustered together such as scs987dtg1234
Is it possible to convert :
Nine Hundred Sixty Three Thousand Seven Hundred Eighty One
Eight Hundred Seventy Eight Thousand Eight Hundred Seventy Eight
Eight Hundred Twenty Two Thousand Seven Hundred Eighty Four
Eight Hundred Twenty Six Thousand One Hundred Eighty Nine
Nine Hundred Three Thousand Nine Hundred Six
to numeric...
EG:
963781
878878
872784
903906
formula to add letters but with a numeric value. this is for a schdule sheet. where w would equal 7.5 and x would be 0.
i am using this
=SUMPRODUCT(--(ISTEXT(B3:H3)))*7.5
reads the w and adds up ok but need to be able to put w for work and x for off days and still add the total hours
I have lists of room areas in metres squared, I extract the web based lists to excel: for example.
10.56 m2
14.23 m2
21.34 m2
How do i calculate the total of these amounts?
Is there a formular to remove the "m2"?
I have tried using =SUBSTITUTE(A1,"m2","") which deletes the "m2" from the cell, but then I can't use =SUM(A1:A3) as this totals the 3 cells as 0.
If i use =SUM(A1+A2+A3) the total is correct, but I have over 3000 cells in one column to add together.
Is there a formula or something to remove all instances of "m2" or "m3" from the whole sheet or another way i can calculate the totals.
I'm currently trying to create a rota timesheet which automatically takes shift patterns and deducts for unpaid breaks.
Basically if a shift is less than 4.5 hours long then no break is deducted
If the shift length is 4.5 - 6.5 hours then 15 mins are deducted
If the shift is longer than 6.5 hours then 30 mins are deducted.
The problem I currently have is all numeric values only are not being recognized on my password column. I have tried changing the cell formats but still problem still there. The program work if an alphanumeric value is declared as password. However if you simply indicate plain numeric values only as password, it returns incorrect values, it like it doesn;t recognize all numeric values. Also how can I make 0000 as a declared password? when I place 0000 on my password column, only one zero value is being displayed instead of four another thing if I happen to place 0123 only 123 is being displayed.
View 4 Replies View Relatedwho can restrict the input values to numeric values only? I have 2 textboxes where the user enters employee id and numeric choice value of 1,2 and 3...I need help in making the textboxes restricted to numeric values input only. How Preventing the users from entering alpha and symbols values.
View 6 Replies View RelatedI have created a calendar in Exel for individuals to put in their time at the bottom for everyday I have put in a total. Is there a formula that I can sum the dates, pick a certain time from those date and get my total hours for that particular week?
View 6 Replies View Related