Valid Email Check Within Regular Cell?
Feb 18, 2014
I have a cell in which I would like to have a user enter an email address. If it does not follow a valid format I would like it to display an error message, "Invalid E-mail Address", clear contents and re-select the cell otherwise move to the next cell. By Valid email I want it to check for only one "@", and at least one "." but I also want it so that a user can't just bypass it with typing "@.". I would like it to reject special characters and spaces and only allow "-" and "_" maybe a general format of <name>@<place>.<domain>
The cell in question is K45 and the following cell would be K47 if a valid email is entered
View 8 Replies
ADVERTISEMENT
Aug 26, 2007
I am using VBA to determine the data type of the active cell value. I have the following:
'To check Number
IsNumeric(ActiveCell.Value)
'To check Date
IsDate((ActiveCell.Value)
'To check Formula
ActiveCell.HasFormula
'To check Text (*not perfect solution)
WorksheetFunction.IsText(ActiveCell.Value)
However, I cannot find the way to check for Time. In addition, the method IsText will return True even if the active cell value is a date.
View 8 Replies
View Related
Apr 13, 2007
I am using the following to check if a string has a sequence of 6 decimal digits in it. But am getting an error. If(str Like *######*). I have to check if str has values like 123456USA ; ABC725439 ; jh658478hd. I thought # represents a single digit and * represents any no of characters
View 2 Replies
View Related
Dec 16, 2008
I am looking for a formula to count the number of valid mobile phone numbers in a list and the number of valid email address in another list, 2 different formulas.
The mobile phone number formula would count the number of indervidual mobile phone numbers in a list that contain 077 and 078 and 079.
The email formula would count the number of indervidual email addresses in a list that contain ...........@..........dot com or .............@............dot co dot uk
Both lists may contain random junk text, like in the email list some one may have entered words like 'no email address' etc.
View 11 Replies
View Related
May 20, 2008
I have a macro that creates a new sheet and names it based off a cell value (date, MM-DD-YY), and copies a 'Template' sheet to it, values only. Every so often, two sheets need to be made with the same date. I need the right direction in creating an addition to the below code that will add an A, B, or C at the end of the sheet name if the name is already taken?
View 13 Replies
View Related
Oct 29, 2012
I've 1000's of website URLs which I need to open one at a time, and check if the page contains message such as 'Product not found' or 'Oops...' or 'Page not found' or 'Error'
Is this possible using Excel / VBA ?
[URL]
View 2 Replies
View Related
Jan 22, 2014
I have a large spreadsheet that has thousands of hyperlinks that I would like to check periodically to make sure they are up to date. I have found dozens of examples of VBA code that will do this for me and seemingly very clear instructions on how to do it (for example this), but haven't yet been able to successfully run anything in my spreadsheet. It either doesn't do anything, or I get an error message of a bad file name.
View 2 Replies
View Related
Dec 10, 2007
I have this formula =COUNTIF(WallA,D35) which works great unless the named range is deleted. Is there a way to check to see if the named range is valid in formula?
View 9 Replies
View Related
Apr 28, 2006
I was wanting to use an IF Then statement to check if numberes entered into cells in Sheet1 were valid numbers in another sheet. The valid numbers will not always be consecutive.
View 2 Replies
View Related
Aug 19, 2012
Any way to check if data entered is a valid date using a VBA function/routine?
For example, cdate() and isdate() functions accept dates like 31-Feb-12 and 29-Feb-11 when they are not valid.
If the cell is formatted as date format, then Excel replaces hyphens with forward slashes for dates, except invalid ones like 31-Feb-12 and 29-Feb-11, so in these cases I can test for existence of forward slashes in the following way
Len(Mid(pCell, InStr(pCell, "/") + 1, Len(pCell) - InStr(pCell, "/"))) 7
However this approach fails for Input boxes. Obviously I can't convert the Input box response using cdate function as it will incorrectly accept 31-Feb-12 as a valid date.
Any routines that will work for both cell entered and Input box entered dates?
View 5 Replies
View Related
Aug 31, 2007
want to do something simple, and it just wont paste. not sure why. anyone care to look it over and tell me what i'm doing wrong here?
'Insert Date Information
Dim vDate As Date
Range("D2").Select
ActiveCell.FormulaR1C1 = Date
vDate = Date
'Get date from user
Dim dDate As Date
vDate2 = Application.InputBox(Prompt:="Type in the due date for the location." _
& Chr(13) & Chr(13) & "*If you want the date to default to " & Date + 5 & " then leave the field blank.", _
Title:="Due Date", Type:=1 + 2).............
View 3 Replies
View Related
Jul 23, 2014
I have a macro to send emails to a group people that based on various criteria, it will attach a number of files to a recipient's particular email. This is a small portion of the code that does the attachment adding:
Code:
If Range("B" & a) = "Y" Then
If citChev "" Then .Attachments.Add citChev
End If
If Range("C" & a) = "Y" Then
If citMits "" Then .Attachments.Add citMits
End If
If Range("D" & a) = "Y" Then
If citToyo "" Then .Attachments.Add citToyo
End If
If Range("E" & a) = "Y" Then
[code].....
Most people get several of the files attached to their email and all works fine. But, there are certain conditions when all the IF() stmts fail where a recipient will not get any files attached. I do not want to send the email if this is the case, but it currently is sending it.
Is there a way after all the IF() stmts have processed to check to see if this current email has any attachments assigned to it? (IF .Attachments "" Then...) does not work.
View 2 Replies
View Related
Feb 27, 2008
I need to create a leave tracker wherein i will get the names of the person who want to take leave in that particular month. I have prepared a calendar to make an entry. I need to take care of the following things
1) The person must have sufficient leave balance available to take the leave. so i have to first check if he/she has sufficient leave balance.
2) There are four team leaders and each team has around 13-14 members. so if lots of people from the same team apply for the leave then they will not be eligible for leave. this i want to know in percentage as to how much percentage of people are taking leave from that particular team.
View 5 Replies
View Related
Jul 9, 2009
I am using the code below tied to a button on the worksheet to email a sheet and would like it to check that there has been text entered intothe cells below prior to emailing the sheet. If the cells have no text entered i would like a message box to pop up stating that all fields need to be completed.
Range of cells:
H6,A9,F9,A12,F12,A16,A23,A26,C28,D30,D32,D34,A37,D39,F36,F28
Email code I am using:
View 9 Replies
View Related
Dec 23, 2011
I check on the status of Purchase orders all day long and have to send out email to different vendors asking if they received it.
I would like to create a VBA code that will open up a new email and have a general question inserted in the email (question below). I can fill in the PO number and email address on my own.
Please confirm that you received the following PO and verify the Qty, price and delivery date.
View 5 Replies
View Related
Oct 18, 2006
I need to email a page from a worksheet to a series of people and am currently using the following
Worksheets("Report").Activate
ActiveWorkbook.Save
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application. ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
however, I also need the worksheet to be email to the person currently running the macro (i.e. when they email it to the others, a copy is sent to thier own inbox too), is there any way in which this can be done?
The user's email address could be formed from data in the spreadsheet, if there is an easier way to do this (i.e. email address is based on a cell in the spreadsheet).
View 3 Replies
View Related
Aug 10, 2012
I have data in Row 53 that spans 7 columns, but stays in the same row. I want to design a loop to select every 7th cell in that row and check if it is empty. If not, add onto a "counter" then display the final number of occupied cells (the value of the counter) at the end. This is what I have so far, but I get all sorts of errors.
Code:
Sub Tester()
Dim WB As Workbook
Dim WS As Worksheets
Dim modCounter As Long
Dim Cell As Range
Set WB = Workbook("Transverse Series.xlsm")
Set WS = WB.Sheets(BM18)
[Code] ......
View 1 Replies
View Related
Dec 11, 2009
Documents......Data.xls
where "..." is any old string of characters?
View 9 Replies
View Related
Jul 7, 2014
I'm trying to do a COUNTIF using regular expressions. I can do it in SQL, but I'm having a hard time figuring it out in Excel.
My SQL query looks like:
[Code] ....
In Excel, I'd like to do the same thing so that I can send it to a client and have the counts already in the spreadsheet. Basically, I want count anything that has 5 numerical digits followed by "_.....".
View 3 Replies
View Related
Mar 15, 2014
I want to apply regular expression for password
a) The first letter cannot be number
b) the password should have a combo of numbers letters and one special character
c) The password should begin with a big case letter
View 1 Replies
View Related
Sep 24, 2009
Hope the title isn't too broad. Every time I think I have got to grips with RegExp I get stuck on something apparently quite simple. So, I have a few queries so I hope OK to bundle them together.
1. How do I remove the word 'of' only if it occurs after certain other words (the other words should also go). E.g.
"Vale of White Horse" should stay the same
"Borough of Lambeth" should become "Lambeth" (because 'of' after 'Borough')
I tried "Borough(sof*)" but that doesn't remove either word.
2. How do I remove everything after a '/' (inclusive)?
I have tried "/sw+" which removes the first word after '/' but there could be other words
3. How do I remove everything inside brackets (inclusive)?
View 6 Replies
View Related
Apr 10, 2013
I have built a custom .xlam file that I"m using as an excel add-in.
I'd like to write some VBA code which takes ranges from any random worksheet (into an array), and get a range from the xlam (into array).
I can't seem to figure out what combination of This.Worksheet and Active.Worksheet I should use.
View 1 Replies
View Related
Jul 31, 2009
I'm trying to figure out the whole Regular Expression thing...so I figured Hotpepper's EXDATA UDF would be a good place to try and rewrite with Regular Expression...I got it to work...but not sure why as it seems opposite of what I would have expected...
eg pattern [^/d] i thought would give me everything except numerics...but it appears to be giving me numerics....
here's the code and sheet...can someone help explain what's going on? .......
View 9 Replies
View Related
Jul 23, 2009
I am creating an electronic timesheet. I have created drop downs for times of the day, which then automatically calculates the number of hours worked based on the times selected. The trouble I am having is, I also need to incorporate "Vacation", "Sick Day", etc into the drop down box where the time of day is located.
The sheet works great until someone selects a non-time related answer, such as "Vacation". When this option is selected, the cell that is supposed to contain the number of hours worked says #VALUE! when I, in fact, need it to list a value of 0 for the number of hours worked. Any idea on how to combine my formula =(D6-D5)*24+(D8-D7)*24+(D10-D9)*24 with an IF statement that will automatically assign a value of 0 if a text answer is selected from the drop down?
View 6 Replies
View Related
Jun 10, 2014
I want the first 60 rows of column C to be constant meaning C1, C2, C3, C4..C59 and after 60 rows it should start again with C1, C2, C3.....C59 rather than C60, C61, C62. In other words i+1 but after 60 rows i should be reset to 1 and then again increase by 1. how can i implement these changes
[Code] ......
View 2 Replies
View Related
Oct 13, 2008
spreadsheet has daily hours worked per employee and a weekly total. What is the formula to take the weekly total per employee and separate the regular hours (first 40) and have the overtime show up in the next column (without getting negative numbers for the totals under 40).
View 4 Replies
View Related
Feb 5, 2013
I want to track a friendly competition of squash games with a friend.
player1
player2
January
[Code].....
I have a simple column chart showing the progress of each month but I'd like a 13th pair of bars showing the percentage, with the percentage scale on the secondary axis. Do I have to choose the percentage cells as a separate series?
View 8 Replies
View Related
Aug 13, 2013
Basically the two instances shown are the same, one is a pivot table, the other is copy pasted values off the pivot.
I need to prepare a chart for this data such that the years are show on X axis and the "frequency intervals" and the Count are shown on Y axis ( Im assuming one would have to be one on primary and one on secondary).
What this data is conveying is that e.g. in year 2002 there were 16 customers who bought between 0-799 quantity of a given item, in 2003, 48 customers bought a quantity between 0-799 and so on and so forth.
See attached the image [URL] .......
View 3 Replies
View Related
Sep 27, 2006
I need to extract DIGITS from following: C23, C24, C515-C519. It may be done with regular expressions. Does anybody have experience with VBA code for RegEx?
Input: C23, C24, C515, C519.
Output: 23,24, 515, 519
View 5 Replies
View Related
Feb 14, 2009
Un-Skipping the Rank after a duplicate value to get a regular Series!
Col A------Col B------Col C------Col D
Players------Scores------Rank------Answr
SACHIN------186------1------1
DHONI------183------2------2
KAPIL------175------3------3
HAYDEN------175------3------3
SEHWAG------167------5------4
BORDER------158------6------5
RAHUL------152------7------6
LARA------148------8------7
RICKY------148------8------7
KEVIN------142------10------8
BOON------133------11------9
FLINTFF------119------12------10
BOTHAM------112------13------11
I want to prevent the skipping of numbers while using the RANK function which skips the next number when it hits a duplicate value. I want to get the rank as well as not have the next number skipped.
View 4 Replies
View Related
