Help with Compile error Next without for

From ExcelExpert.com

Help with Compile error Next without for: I'm trying to get excel to send an email when a command button is pushed. The email needs to be sent out if any date in column E equals today's date. I keep getting a compile error Next without for. Here's the Macro

Sub SendEmail()
'Uses early binding

Dim OutlookApp As Object
Dim MItem As Object
Dim Today As Object
Dim cell As Range
Dim Subj As Variant
Dim EmailAddr As String
Dim Scie As String
Dim CnNo As String
Dim Msg As String
'Create Outlook Object
On Error GoTo debugs
Set OutlookApp = CreateObject("Outlook.Application")
Set Today = cell.Value("C1")
'Loop through the rows
For Each cell In Columns("E").Cells.SpecialCells(xlCellTypeConstants)
 With cell.Value = Today
 'Get the data
  Subj = "Please check the CN Tracker, a CN has been assigned to you "
 Scie = cell.Offset(0, 8).Value
 EmailAddr = cell.Offset(0, 9).Value
 CnNo = Format(cell.Offset(0, -3).Value, "0,000.")
 'Compose message
  Msg = "Dear " & Scie & vbCrLf & vbCrLf
 Msg = Msg & "CN " & CnNo & " has been assigned to you. " & vbCrLf & vbCrLf
 Msg = Msg & "Please check the CN tracker and request the technical solution and supplier contact information from the responsible engineer. " & vbCrLf & vbCrLf
 Msg = Msg & "Have a great day. " & vbCrLf & vbCrLf
 Msg = Msg & " " & vbCrLf & vbCrLf
 Msg = Msg & " " & vbCrLf & vbCrLf
 Msg = Msg & "************This is an automated message, please do not. **************"
 'Create Mail Item and send it
  Set MItem = OutlookApp.CreateItem(olMailItem)
 With MItem
  .To = EmailAddr
  .Subject = Subj
  .Body = Msg
  .Send
    End With
debugs:
If Err.Description <> "" Then MsgBox Err.Description
Next
End If
End Sub
Private Sub CommandButton1_Click()
Sheet1.SendEmail
End Sub

What am I missing?

If Err.Description <> "" Then MsgBox Err.Description   RESUME Next End If

Add Data to Charts with Copy Paste [Quick Tip]

From Chandoo.rog

Add Data to Charts with Copy Paste [Quick Tip]:

So how did your weekend go?

I did a bit of gardening, painted our car shed, played badminton (I am learning), attended 60th birthday of a close friend’s dad. Pretty hectic, but fun as usual.

To start this week, let me share a simple but fun way to add data to charts.

Lets say you have a chart that depicts Annual sales for last few years. And you want to add the data of Profits (or Expenses) to this chart. Here is a dead-simple way to do it.

1. Copy the profit data by selecting it and pressing CTRL+C


2. Select the chart


3. Paste by pressing CTRL+V


4. That is all!

See the demo alongside to understand how this works.

Bonus Tips:

1. While pasting, if you go for Paste Special (CTRL+ALT+V or ALT+E S) you can tell whether the data should be added as a new series or new points and several other things.


2. To remove a series of data from a chart, just select the series and hit DEL key.


3. To extend a series (ie add new points to it), select the series. Now you will see that Excel has highlighted the range of cells corresponding to that series. Just point your mouse at the bottom-right corner and resize the range to add new points to the chart.

More Quick Tips on Excel Charting

There are a ton of things you can do in Excel with a click of mouse or press of few keys. Whenever we learn something that is simple yet very useful, we share it as a quick tip. Browse thru these to learn more on Excel Charting.

• Highlight points in line charts & scatter plots


• Display Alert Symbols in Dashboards using Conditional Formatting


• Change Data Labels in Charts to whatever you want


• Use Paste Special to Speed up Chart Formatting


• Hide “0″ in Excel Chart’s Axis


• Create a combination chart, add secondary axis


• More Quick Tips & Chart Formatting Tips

（未知标题）

From Chandoo.org

（未知标题）:

Last week Joyce asked a question on the Chandoo.org, Comment 24.

I’m wondering if there’s a way to count the number of occurrences of words when they’re all in a cell? Like this:

A1: “Windows NT, Networking, Firewalls, Security, TL, Training”

A2: “Networking, Networking, Training, Security, TL, Training”

A3: “Security, TL, Firewalls, Security, Networking, Windows NT”

Hui responded with an Array Formula:

=SUM(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,C10,””)))/LEN(C10)

As the formula is an Array Formula it is entered with Ctrl Shift Enter.

Setup the Problem

Copy the Data Above into Cells A1:A3 or download the example file here: Example File

Enter the value Security into cell C10

And array enter the formula

D10: =SUM(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,C10,””)))/LEN(C10)

Pull The Formula Apart

Lets take a look inside this and see how it works

We will break this formula apart and look at each section independently and then put the answers back together.

=SUM(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,C10,””)))/LEN(C10)

In a cell below the data

D13: =LEN(A1:A3) but don’t press Enter, Press F9

Excel displays ={57,56,57}

This is the number of characters in each cell A1:A3

ie: A1 has 57 characters, A2 has 56 characters, A3 has 57 characters,

=SUM(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,C10,””)))/LEN(C10)

In another cell below the data

D15: =LEN(SUBSTITUTE(A1:A3,C10,”")) but don’t press Enter, Press F9

Excel displays ={49,48,41}

What this section does is measure the length of each cell in A1:A3 but only after substituting the word being searched for from C10 with ””, which is a zero length string.

So the second array is shorter by X times the length of the word in C10

=SUM(LEN(A1:A3) – LEN(SUBSTITUTE(A1:A3,C10,””)))/LEN(C10)

Next we add up the difference between the two arrays

So you can see we have two arrays of numbers

Array 1 = {57,56,57}

Array 2 = {49,48,41}

If we subtract Array 2 from Array 1

= {57-49, 56-48, 57-41}

= {8, 8, 16}

We can do this in Excel to Check

In Cell D17 enter

=LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,C10,”")) and press F9

Excel displays: = {8, 8, 16}

=SUM(LEN(A1:A3) – LEN(SUBSTITUTE(A1:A3,C10,””)))/LEN(C10)

The next part is to sum these up

Obviously the sum of 8, 8 & 16 is 32

We can check that

D21: =SUM(LEN(A1:A3)-LEN(SUBSTITUTE(A1:A3,C10,”"))) and press F9

Excel displays: 32

=SUM(LEN(A1:A3) – LEN(SUBSTITUTE(A1:A3,C10,””)))/LEN(C10)

The final part of this is to divide the sum (32 in this case) by the length of the text in C10 “Security” = 8 Characters

=32 / 8

= 4

OTHER POSTS IN THIS SERIES:

You can learn more about how to pull Excel Formulas apart in the following posts

Formula Forensic 001 – Taruns Problem

WHAT FORMULAS WOULD YOU LIKE EXAMINED

If you have any formulas you would like explained please feel free to leave a post here or send me an email:

If the formula is already on Chandoo.org or Chandoo.org/Forums, simply send the link to the post and a Comment number if appropriate.

If sending emails please attach an Excel file with the formula and data

A Technique to Quickly Develop Custom Number Formats

From Chandoo.org

A Technique to Quickly Develop Custom Number Formats:

In the past Chandoo has written about custom Number Formats for cells:

http://chandoo.org/wp/2008/02/25/custom-cell-formatting-in-excel-few-tips-tricks/

http://chandoo.org/wp/tag/custom-cell-formatting/

and I have written about Custom Number Formats for Charts:

http://chandoo.org/wp/2011/08/19/selective-chart-axis-formating/

http://chandoo.org/wp/2011/08/22/custom-chart-axis-formating-part-2/

This post examines a technique for quickly developing Custom Number Formats for Cells, Charts or any other Number location in Excel.

A Technique for Quickly Developing Custom Number Formats

Instead of Selecting the cell, chart axis etc, Ctrl 1, Format Cells/Properties, Number Tab, Custom and then entering a Custom Format and Apply, only to find out that the format is incorrect, try this simple technique below.

1. Enter a few Numbers in 3 cells

Enter 3 numbers, a positive, zero and negative which have values you will expect to receive in your model.

2. Add a Custom Format Cell

In D3 I have entered ##,;-(##,);”Zero”

3. Display Numbers using the custom Format

Each Number to a display cell with a simple =Text(B3,$D$3)

Copy down

This will display the 3 numbers using the Custom Format in Cell D3

4. Develop Your Custom Format

Play around with your own Custom Number Formats to your hearts content

5. Use your new format

Once you have completed your new Custom Number Format, copy the cell contents of D3 in this case.

Select your cells/or other Excel Numbers,

Ctrl 1,

Format Cells/Properties,

Number Tab, Custom

Enter the Custom Format and Apply.

6. Extending the Technique

This technique can be extended by adding several more rows with a larger range of values.

The values are all evaluated at the same time

LIMITATIONS

The above technique does not show the effects of the Color Modifiers in the test cells

But I think it is a safe bet that you will understand what the Modifier [Red] will do

There are also reserved characters such as E

So in the above example if I had used Zero instead of “Zero”

It would have displayed Ze1900ro, where the E in Zero is taken as 10^x and x=0 so Excel interprets e as 0 or 1900, a date?

You can avoid this by using the code “Zero” or Z\ero

DOWNLOAD

You can download the worked Example File used above.

NUMBER FORMATS

For more on Number Formats check out the above links or those below:

http://www.ozgrid.com/Excel/excel-custom-number-formats.htm

http://www.ozgrid.com/Excel/CustomFormats.htm

http://peltiertech.com/Excel/NumberFormats.html

How to Look up Based on Multiple Conditions

From chandoo.org

How to Look up Based on Multiple Conditions:

This article is part of our VLOOKUP Week. Read more.

Situation

Not always we want to lookup values based on one search parameter. For eg. Imagine you have data like below and you want to find how much sales Joseph made in January 2007 in North region for product “Fast car”?

Data:

Solution

Simple, use your index finger to scan the list and find the match

Of course, that wouldn’t be scalable. Plus, you may want to put your index finger to better use, like typing . So, lets come up with some formulas that do this for us.

You can extract items from a table that match multiple criteria in multiple ways. See the examples to understand the techniques:

Using SUMIFS Formula [help]
Formula	=SUMIFS(lstSales, lstSalesman,valSalesman, lstMonths,valMonth, lstRegion,valRegion, lstProduct,valProduct)
Result	1592
Using SUMPRODUCT Formula [help]
Formula	=SUMPRODUCT(lstSales,(lstSalesman=valSalesman)*(lstMonths=valMonth)*(lstRegion=valRegion)* (lstProduct=valProduct))
Result	1592
Using INDEX & Match Formulas (Array Formula) [help]
Formula	{=INDEX(lstSales,MATCH(valSalesman&valMonth&valRegion&valProduct, lstSalesman&lstMonths&lstRegion&lstProduct,0))}
Result	1592
Using VLOOKUP Formula [help]
Formula	=VLOOKUP(valMonth&valSalesman&valRegion&valProduct,tblData2,7,FALSE)
Result	1592
Conditions:	A helper column that concatenates month, salesman, region & product in the left most column of tblData2
Using SUM (Array Formula) [help]
Formula	{=SUM(lstSales*(lstSalesman=valSalesman)*(lstMonths=valMonth)* (lstRegion=valRegion)*(lstProduct=valProduct))}
Result	1592

Sample File

Download Example File – Looking up Based on More than One Value

Go ahead and download the file. It also has some homework for you to practice these formula tricks.

Also checkout the examples Vinod has prepared.

Special Thanks to

Rohit1409, dan l, John, Godzilla, Vinod

Similar Tips

• Excel SUMIFS Formula – What is it, how to use it & examples


• Excel SUMPRODUCT Formula – What is it, how to use it & examples

Exporting Outlook Messages to Excel

From http://techniclee.wordpress.com/

Exporting Outlook Messages to Excel:

I’m writing this post primarily for Sen who in a comment to another post asked

How do i Export e-mail messages with the subject, received date & time from Outlook to Excel with the sender address?

The simplest way to carry out this is to use Outlook’s built-in export capability. Using it you can export to a .csv (comma separated values) file which you then open with Excel. However, there are a couple of drawbacks to using export. Export doesn’t allow you to pick specific messages to export so you’ll have to export an entire folder at a time. It also doesn’t allow you to limit the export to certain fields. Instead it will export everything and you’ll have to delete the columns you don’t want.

A better, but more complicated, approach is to use a macro to do the export. With a macro you can select the messages you want, export only the fields you want, and it can write directly to Excel. The code for doing this is both simple and straightforward. It creates a spreadsheet, loops though the selected messages writing the fields you want to that spreadsheet, then saves and closes the spreadsheet. This solution should work in Outlook 2003 and later.

Adding the code to Outlook.

1. Start Outlook


2. Press ALT+F11 to open the Visual Basic Editor


3. If not already expanded, expand Microsoft Office Outlook Objects


4. If not already expanded, expand Modules


5. Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting Insert > Module.


6. Copy the code from the code snippet box and paste it into the right-hand pane of Outlook’s VB Editor window


7. Click the diskette icon on the toolbar to save the changes


8. Close the VB Editor

Sub ExportMessagesToExcel()
   Dim olkMsg As Outlook.MailItem, _
       excApp As Object, _
       excWkb As Object, _
       excWks As Object, _
       intRow As Integer, _
       strFilename As String
   strFilename = InputBox("Enter a filename (including path) to save the exported messages to.", "Export Messages to Excel")
   If strFilename <> "" Then
       Set excApp = CreateObject("Excel.Application")
       Set excWkb = excApp.Workbooks.Add()
       Set excWks = excWkb.ActiveSheet
       'Write Excel Column Headers
       With excWks
           .Cells(1, 1) = "Subject"
           .Cells(1, 2) = "Received"
           .Cells(1, 3) = "Sender"
       End With
       intRow = 2
       'Write messages to spreadsheet
       For Each olkMsg In Application.ActiveExplorer.Selection
           'Only export messages, not receipts or appointment requests, etc.
           If olkMsg.Class = olMail Then
               'Add a row for each field in the message you want to export
               excWks.Cells(intRow, 1) = olkMsg.Subject
               excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
               excWks.Cells(intRow, 3) = olkMsg.SenderEmailAddress
               intRow = intRow + 1
           End If
       Next
       Set olkMsg = Nothing
       excWkb.SaveAs strFilename
       excWkb.Close
   End If
   Set excWks = Nothing
   Set excWkb = Nothing
   Set excApp = Nothing
   MsgBox "Process complete.  A total of " & intRow - 2 & " messages were exported.", vbInformation + vbOKOnly, "Export messages to Excel"
End Sub

Using the Code.

1. With Outlook open select one or more messages from any folder.


2. Run the macro.


3. When prompted enter a filename to save the export to. You can cancel the export by not entering anything.


4. The macro will display a dialog-box when it’s finished. The dialog-box includes a count of the number of messages exported.

Notes.

• This code can easily be modified to export a different set of fields. To do that, change the headings written to the spreadsheet (lines 15-17) and the message fields (lines 25-27).


• If you don’t want the macro to prompt for a filename each time, then you can change line 7 to strFilename = “Path_and_File_Name”

Filed under: Outlook, Scripting Tagged: Outlook, VBA

VBA code to handle Access Imports and Query

From ExeclExperts.com

VBA code to handle Access Imports and Query:

Paste the following code in a general module

Public g_objConnection As ADODB.Connection
Public Const gc_strDBPath As String = "C:\Test.mdb"
Function blnConnectDatabase(strPath As String, strDBPass As String) As Boolean
'    If blnFileExists(strPath) = False Then
'        GoTo ErrH
'        Exit Function
'    End If
    Set g_objConnection = New ADODB.Connection
   On Error GoTo ErrH
   g_objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
       strPath & ";Jet OLEDB:Database Password=" & strDBPass & ";"
   On Error GoTo 0
   blnConnectDatabase = True
   GoTo ExitH
ErrH:
   blnConnectDatabase = False
   Set g_objConnection = Nothing
ExitH:
   Application.StatusBar = False
End Function
Function blnTableExistsInDB(strTableName As String) As Boolean
   Dim rst         As ADODB.Recordset
   Dim strTbl      As String
   strTbl = strTableName
   Call blnConnectDB
   Set rst = g_objConnection.OpenSchema(adSchemaTables)
   If Left(strTbl, 1) = "[" And Right(strTbl, 1) = "]" Then
       strTbl = Mid(strTbl, 2, Len(strTbl) - 2)
   End If
   rst.Filter = "TABLE_TYPE='TABLE' and TABLE_NAME='" & strTbl & "'"
   On Error Resume Next
   blnTableExistsInDB = (UCase(rst.Fields("TABLE_NAME").Value) = UCase(strTbl))
   On Error GoTo 0
   If Err.Number <> 0 Then blnTableExistsInDB = False
   Set rst = Nothing
End Function
Function ExecuteDBQuery(strQuery As String, Optional rngTarget As Range, Optional blnHeader As Boolean) As ADODB.Recordset
   Dim objRecordset                As ADODB.Recordset
   Dim intColIndex                 As Integer
   Dim lngRowOffset                As Long
   On Error GoTo ErrH
   Call blnConnectDB
   If Not rngTarget Is Nothing Then
       Set rngTarget = rngTarget.Cells(1, 1)
   End If
   Set objRecordset = New ADODB.Recordset
   With objRecordset
       .CursorLocation = adUseClient
       '.Open strQuery, g_objConnection, adOpenForwardOnly, adLockReadOnly ', adCmdText
        .Open strQuery, g_objConnection, adOpenDynamic, adLockOptimistic ', adCmdText

       If Not rngTarget Is Nothing Then
           If blnHeader = True Then
               For intColIndex = 0 To objRecordset.Fields.Count - 1 'field names
                    rngTarget.Cells(1, intColIndex + 1).NumberFormat = "@"
                   rngTarget.Cells(1, intColIndex + 1).Value = .Fields(intColIndex).Name
                   rngTarget.Cells(1, intColIndex + 1).Font.Bold = True
               Next intColIndex
               lngRowOffset = 1
           Else  'Without field names
                lngRowOffset = 0
           End If
           If Application.Version < 12 And .RecordCount + rngTarget.Cells(lngRowOffset + 1, 1).Row > 65535 Then
               MsgBox "Records upto row number 65535 can be accommodated. Rest will be ignored.", vbInformation, "Import"
           ElseIf Application.Version >= 12 And objRecordset.RecordCount + rngTarget.Cells(lngRowOffset + 1, 1).Row > 1048576 Then
               MsgBox "Records upto row number 1048576 can be accommodated. Rest will be ignored.", vbInformation, "Import"
           End If
           rngTarget.Cells(lngRowOffset + 1, 1).CopyFromRecordset objRecordset ' the recordset data
        End If
   End With
   Set ExecuteDBQuery = objRecordset
ErrH:
   Set objRecordset = Nothing
   If Err.Number <> 0 Then
       'MsgBox Err.Description, vbCritical, "Error"
        'MsgBox "Database Query Error"
    End If
End Function
Sub DropTable(ParamArray strTableName() As Variant)
   Dim x As Integer
   For x = LBound(strTableName) To UBound(strTableName)
       If blnTableExistsInDB(CStr(strTableName(x))) = True Then
           Call ExecuteDBQuery("Drop Table " & CStr(strTableName(x)))
       End If
   Next x
End Sub
Function blnConnectDB() As Boolean
   Dim blnCon As Boolean
   blnCon = True
   If g_objConnection Is Nothing Then
       blnCon = blnConnectDatabase(gc_strDBPath, "")
   ElseIf Not g_objConnection.State = 1 Then
       blnCon = blnConnectDatabase(gc_strDBPath, "")
   End If
   blnConnectDB = blnCon
End Function
Sub CompactDB()
   Dim lngRes As Long
   Call CloseDB
   lngRes = DatabaseCompact(gc_strDBPath)
   If lngRes = 0 Then
       'MsgBox "Succeeded in compacting database...", vbInformation
    Else
       'MsgBox Error(lngRes)
        Application.StatusBar = "Unable to clean database..."
   End If
End Sub
Function DatabaseCompact(strDBPath As String, Optional strDBPass As String = "") As Long
On Error GoTo ErrFailed
   'Delete the existing temp database
    If Len(Dir$(strDBPath & ".tmp")) Then
       VBA.Kill strDBPath & ".tmp"
   End If
   With CreateObject("JRO.JetEngine")
       If strDBPass = "" Then 'DB without password
            .CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ".tmp;Jet OLEDB:Encrypt Database=True"
       Else             'Password protected db
            .CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ";Jet OLEDB:Database Password=" & strDBPass, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath & ".tmp;Jet OLEDB:Encrypt Database=True;Jet OLEDB:Database Password=" & strDBPass
       End If
   End With
On Error GoTo 0
   VBA.Kill strDBPath 'Delete the existing database
    Name strDBPath & ".tmp" As strDBPath 'Rename the compacted database
ErrFailed:
   DatabaseCompact = Err.Number
End Function
Sub CloseDB()
   If Not g_objConnection Is Nothing Then
       If g_objConnection.State = 1 Then g_objConnection.Close
   End If
   Set g_objConnection = Nothing
End Sub