Macro To Validate Contents Of Cell And If Not Match Send Msg Box

May 21, 2014

Userform that people use. I need validating the users input into the form.

1. Need to validate that A1 only has 3 letters (Alpha)
2. Need to validate that A2 is not left blank
3. Need to validate A3 has only 10 digits

My state is that when the user hits the submit button in the user form the macro checks for these validations above and if everything is ok emails the sheet. However if the user havent inputed the data correctly a msg box will pop to let them know what is wrong. If thats the case i do not want the sheet emailed. below is the email code i'm using

HTML Code: 

Sub SendMail()
' Copy the sheet(1)
ThisWorkbook.Sheets(1).Copy
' Send Email
.SendMail Recipients:=Array("email.address@email.com"), Subject:="Test" & Format(Date, "dd/mmm/yy")
.Close SaveChanges:=False
End With
End Sub

View 2 Replies


ADVERTISEMENT

Send Email Using Cell Contents In Subject

Mar 10, 2009

I have tried a number of things, but have not been able to figure it out. It may not be available with this particular code, but I am not sure what limitations this code has. Does anyone know how to include the contents of a particular cell in the subject line? Currently the subject line says "Notification." What I am looking for is for the subject to say "Notification - John Doe" where John Doe would be the contents of cell D13.

View 2 Replies View Related

Enable Macro Automatically - Then Send Send Email

Sep 7, 2008

I used Scheduled Task to set up my spreadsheet to open daily. I have the code with assistance to pull out the due date items and place them into an email.

I have come across XLSTART/AUTOEXEC/ACTIVATE...ETC...
But cannot figure out the code that will automatically "enabling macro" once Scheduled Task opens the spreadsheet?

Then once the macro runs, the email with the due dates, how can this auto send without user interaction?
(currently I would have to hit send)

I am trying to make the process totally automated to open the spreadsheet at a certain time, send the email with due dates and close the spreadsheet.

Following code in ThisWorkbook--

Private Sub Workbook_Open()
Check_Date_Send_Mail
End Sub
Code in Module1--

Option Explicit
Sub Check_Date_Send_Mail()
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim rnDate As Range, rnValue As Range
Dim stAddress As String, stMsg As String
Dim stRecipient As String, stSubject As String
Dim stPost As String
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets("Sheet1")
With wsSheet
Set rnDate = .Range("d2:t23")
End With

View 9 Replies View Related

How To Match Number Strings In Cell Contents

Sep 3, 2013

I have a spreadsheet with 60k lines. One of the columns contains barcodes as per the example below, with a suffix of -XXX-XXX where the XXX are numbers. I want to highlight all the cells where the -XX-XX are the same number (as in the example below -303-303) how I can do this with a formula as I've around 30 spreadsheets each with 60,000 lines I need to sort through?

2500071189898-303-302
2500071189898-303-303
2500071189898-303-304
2500071189898-303-305

View 4 Replies View Related

Copy Cell Contents Between Tabs Provided Cell References Match (VLOOKUP)

Apr 29, 2014

I have 2 inventory reports: what my store has and what my supplier has. I need to copy Tab1:K# to Tab2:T# provided that Tab1:A#'s contents match Tab2:A#'s contents. The A column represents the SKU of the item, but there is a difference in the amount of SKU's in each (my store sells ~6,000 items, supplier has ~10,000 items), so it's not as simple as sort by column A and copy pasting column K to column T.

For instance:
On Tab 1, A2's value is [1], K2's value is [9.38].
On Tab 2, A70's value is [1], K70's value is blank, but I need it to be [9.38], to match Tab 1's respective SKU.

I almost thought I had it figured out with VLOOKUP, but I cant seem to get it right... It doesn't reference the correct number.

Screenshots for reference

First tab, from the wholesaler: [URL]
Second tab, store's stock: [URL]

View 2 Replies View Related

Match 2 Cells On 2 Worksheets And Copy Contents Of Another Cell

Jan 3, 2014

Setup: I have 2 worksheets with between 8 and 9,000 rows on each

Column A in both worksheets Sheet1 and Sheet2 have an email address in them.

Not all addresses in sheet1 will be on sheet2 and visa versa

Column J on Sheet 2 contains a date

What I need: Column M on Sheet1 is empty

I need a formula to place into Column M on Sheet1 that will

Look at Sheet1:A

Locate the corresponding value on Sheet2:A

Pull the date from Sheet2:J same row into Sheet1:M

View 2 Replies View Related

Check On Cell Contents And If Match Change Another Cell

May 29, 2012

I want to check the contents of all the cells in S Column to see if it equals "Process Recover/Rebill"

If True, then Change corresponding cell in C Column to "Manual Correction"

I have this VBA and it doesn't look like anything is processing. It appears that I must have something missing in the If line..message about missing.

(tot_new is the number of rows of data) I begin in the 3rd row.

For Counter = 3 To tot_new
If Range("S" & Counter & ").Value = "Process Recovery/Rebill" Then
Range("T" & Counter & ").Value = "Manual Correction"
End If
Next Counter

View 3 Replies View Related

Index Match Offset - Find Contents Of Reference Cell

Jun 21, 2012

How can I modify the following formula to find the contents of reference cell, for example if the cell reference (G$3) is equal to 9 I want it to use 8 instead.

Code:
IF(ISERROR(INDEX(tra!$E$2:$E$1100,MATCH(G$3,tra!$C$2:$C$1100,0))),"",
INDEX(tra!$E$2:$E$1100,MATCH(G$3,tra!$C$2:$C$1100,0)))

I tried using G$3-1 but this doesn't appear to work.

View 1 Replies View Related

Find Function Where Search Criteria Isn't Exact Match For Cell Contents

Dec 19, 2011

I am new to VBA and am having difficulties in getting a find function to successfully locate search criteria within cell. When the search criteria matches exactly that of the cell contents then the code works; however should the search criteria only form part of the total cell contents (such as a seach for "the" in a cell containing "the cat sat on the mat") the code doesn't recognise it.

Essentially, I need the code to search a range for the required string and if found within a cell activate that cell and populate a combobox with the full cell contents of the activecell.offset(0,-2).

The relevant section of code attached below:

Dim role_count as range
Dim role as string

If Application.WorksheetFunction.CountIf(Range("Role_Count"), Role) 0 Then
Range("role_count").Select
Selection.Find(What:=Role, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate

Me.Controls(ComboBoxName) = ActiveCell.Offset(0, -2).Value

View 4 Replies View Related

Macro That Copy Address In Cell And Send Active Sheet To That Email ID

Apr 15, 2014

Is it possible for a Macro to read a cell say D:11 and copy that email address and send the active sheet to that email?

View 9 Replies View Related

If Multiple Criteria Match Send Data

Feb 22, 2014

I have two different workbooks. In workbook1 I have a table like below:

A
B
C

[Code]....

What I want to do is to create a drop down menu in workbook2 where I can select a name
and then see below what time that person is working each day of the week.

View 1 Replies View Related

Macro That Will Clear Contents Of Cell Based On Format Of Text In Adjacent Cell

Feb 18, 2009

Been racking brain, searching through the forum here, and my Excel 2003 Bible all day trying to figure out this problem to no avail. I would like to clear the contents of any cell in a given range if the cell immediately to the right of is formatted as bold.

View 2 Replies View Related

Macro To Enumerate Cell Contents

Oct 21, 2006

I have attched a spreadsheet to try to illustrate that task I am trying to complete.
In the original data "HANDSET MODEL" in cell D3 contains 3 handset models separated by underscores. I would like to enumerate these so that each handset model has its own row(the underscores will not be needed in the final version). All other data in that row stays the same(cells A3,B3,C3,E3)

Once this has been done I need the macro to move down to the next row and enumerate the handsets in cell D4, on this occassion thare are four handset and the number will vary from row to row. The macro would need to be able to cope with a variable number of rows for the original data and variable amount of handsets in the D column.

View 2 Replies View Related

Copy Based On Cell Contents Macro

May 13, 2013

Basically lets say I have in cell A1 to A10 floor access data i.e. Users who used their ID cards to access a room.

Now the data always starts with either Rejected or Admitted then the user's name and then the card number and the access floor etc. the card currently has.

What I am looking for is that the macro should first check if the cell has Rejected or Admitted written in it - this I can do myself using =Left(A1,8) which should give me the helper column I need.

Then based on this I want it to only copy the name of the individual i.e. it should look in the cell and only copy whatever is written between "Rejected" and "(Card". The cell data is something like this:

Rejected Doe, John (Card #123456) at ABC 123 Floor1/Floor2/Floor3/Floor4 Door 1 [In] [Clearance]

View 4 Replies View Related

Allow User To Edit Cell Contents Within Macro

Feb 4, 2014

I need to query a column and find the cells that have been incorrectly coded. For example the coding standard is #<reference number>/. Sometimes people forget the "/". It is not as easy as using search and replace because the string may have more contents than the above.

For example it may show: ATM/45678/#789876 dt. 1-2-14/ or it something like CHQ/44384/#78987600.

There are many permutations. So I need to find the cells that don't have a "/" following the #<reference number>, then allow a user to enter the slash. At this stage, I don't need code to find cells containing errors, I think I can figure that out. I do need to know how to allow the user to edit the cell as part of the macro. Once the user corrects the error, the macro should then continue. Is this possible?

View 2 Replies View Related

Internet Search Contents Of Cell Using Macro?

Mar 7, 2014

how would I go about having a button on my worksheet which is clicked will open up Internet Explorer and search the information located in a specific cell?

View 4 Replies View Related

Macro Will Not Run Find And Replace With The Contents Of A Cell

Apr 3, 2007

I want to have a macro that will look at a few cells say A1 & A2 and then use the contents of the cells say 1Q07 & 2Q07 respectively to do a find and replace find 1Q07 and replace it with 2Q07. I want it to use the contents of the cell and not just the text because the contents of the cell will change each time used. I tride to do it here but I dont have it yet. It doesnt seem to pass the value to the variable and the variable doesnt seem to do the find and replace.

Private Sub Update_Click()

Application. ScreenUpdating = False
Dim quarter As String
quarter = CStr(txtquarter.Text)
txtquarter.Text = CStr(quarter)

Sheets("Income Statement").Select
Dim oneq As Single
Dim twoq As Single
Dim threeq As Single
Dim fourq As Single
Dim fiveq As Single

oneq = ActiveSheet.Range("A2").Value
twoq = oneq - 1
threeq = twoq - 1
fourq = oneq - 4
fiveq = oneq - 5

View 9 Replies View Related

Macro To Clear Contents In Every Other Cell Of A Selection

Apr 25, 2008

I need to clear the contents of every other cell in a selection of 5000 rows starting with the first cell. Is there a macro that can do this for me?

View 3 Replies View Related

Validate A Particular Format In A String In Excel Without Using A Macro

Nov 18, 2009

I have a string and I have to validate that it is entered in a particular format. The length has to be 12 and the characters should be entered in this format: XX1111/11XXX where X represents alphabet and 1 represents number. The "/" is a part of the string.

I tried using the Data---> Validate function and the problem I faced is that the code turns out to be very long and the formaula cell doesnt let me enter such a long formula. Is there any other way to do this without using a macro?

The formula I was trying is something like this.

=AND(LEN(A1)=12, CODE(LEFT(UPPER(A1),1))>64, CODE(LEFT(UPPER(A1),1))64, CODE(MID(UPPER(A1),2,1))64, CODE(MID(UPPER(A1),10,1))64, CODE(MID(UPPER(A1),11,1))64, CODE(MID(UPPER(A1),12,1))

View 9 Replies View Related

Rightclick Macro If Cell Contents Formatted Correctly

Jan 16, 2009

I'm attempting to create something that can be used on any workbook open to automatically open a file based on the data in cell.

1) User right clicks on a cell.
2) If the cell's value matches an expected format (##-A-####-## for example), a macro is available in the rightclick popup menu.
3) The user selects the macro in the popup menu if they want to run it.
4) The macro takes the cell value and converts it into a filename.
5) The specified file is opened in an external program.

View 4 Replies View Related

Macro That Will Open Certain Files Based On Contents Of A Cell

May 6, 2013

I am trying to create a macro that will open certain files based on the contents of a cell. There are three possible files that I will want to open.

File1.xlsx
File2.xlsx
File3.xlsx

If the contents of cell Q2 = 10, 20, 30, 40 or 50 then open "File1.xlsx"
If the contents of cell Q2 = 60, 70 or 80 then open "File2.xlsx"
If the contents od cell Q2 = 90, 100, or 110 then open "File3.xlsx"

If the contents of Q2 do not equal any of the possibilities listed then I would like an error box to show with the option to end or debug the code.

View 3 Replies View Related

Macro To Clear Contents Of Cells When One Cell Equals To 0 %

Oct 8, 2013

I'm trying to create macro that will clear the contents of the cells in the 4 columns to the left of a cell that = 0 %, as well as the cell that = 0%. For example, if cell F13 = 0 % then the contents of B13:F13 would need to be cleared. The range in which data is being pasted into is B13:F27.

View 4 Replies View Related

Macro - Format Rows Based On Cell Contents

Aug 18, 2009

I have a spreadsheet that has a subtotal row after each data grouping. I need help with a macro that will go to column C, look for any cells with the word "Subtotals" in it, and format that row based on another macro I already have set up. I've tried about 50 different types of macros that I found out on the web, but none have worked.

View 9 Replies View Related

Macro Rename Excel Files Based On Cell Contents?

Jun 23, 2014

Can a macro rename Excel files based on a cell's contents? The problem maybe that I download these files en masse from a website and they all have 54-character long randomly generated filenames which I can rename by selecting all and then use the rename function to get a series of filenames like a, a(2), a(3), ... but I would rather have the files renamed based on the text that exists in cell B1 which is the title of the report. I also read somewhere that this kind of macro only works if the tabs have names like "Sheet 1"

View 1 Replies View Related

Function To Match Contents Of Comments?

Feb 27, 2014

On sheet1, I have numbers and names. These names are also contained in Comments on sheet2, and i would like a function in Column M that matches the numbers with the names in the comments. Cell M1 I have manually filled for demonstration purposes.

Can this be done with a function or is this a VBA task?

View 7 Replies View Related

Clear Contents Macro For Merge Cell (Compile Error - Expected End Sub)

Jun 13, 2014

I'm trying to use the "clear contents macro" for merge cell, but I keep receiving this "compile error : Expected End Sub" error.

FYI, I have named my merge cells to "myMergedCells"

[Code] .....

View 3 Replies View Related

SPORT STATISTIC - How To Clear Contents In First Leg Of Cup Match Row

Mar 10, 2014

I want to write a Formula or Macro :

To clear Second Leg score in First Leg of Cup match

in Attached file, I explained detailed what to do. [URL]

if u need to know anything about Football,

View 2 Replies View Related

Validate Cell Values?

Apr 7, 2009

I would like to simply validate the value entered to a cell against cells of a number of columns. I’d like it to return a vlue to be able to report it in another cell as below. (had to cmma separate the columns, can't get them aligned in this editor)

A, B, C, D, Q, R
1, b, 1, 2, 1, existing

or

A, B, C, D, Q, R
1, b, 1, 2, 5, not existing

So the user enters a value in cell Q to check against the other columns and return a value to R. Additionally, the cells to be validated (A - D) may contain more than one character, i.e. cell A could = 1b2.

View 4 Replies View Related

Validate Empty Cell

Feb 1, 2010

The code below automatically displays the validation when I select the cell. But I would like the cell validation only display when the cell is empty. If the cell has data in it, then do nothing, but if the cell is empty, then display the validation.

If Not (Nothing Is Application.Intersect(Target, Range("B9"))) Then
SendKeys "%{down}"
End If

View 9 Replies View Related

Compare Cell Contents With Given Range Of Contents

Dec 30, 2013

I have attached a workbook stating my problem.

file1.xlsx‎

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved