Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
code to 'put' calc displayed in message box in cells
All
Apologies about the title - hard to describe I have borrowed a terrific bit of code to count emails in specific folders; however the count result is only displayed in a message box - how do i get the result 'put' to a specific cell. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
code to 'put' calc displayed in message box in cells
Without seeing the code.................shrug shoulders<g
Your code does a count and that count is displayed in a msgbox. Just redirect the count to a cell like count = Range("A1").Value Example code............. Sub sheets_num() MsgBox ThisWorkbook.Sheets.Count 'will display in msgbox Range("A1").Value = ThisWorkbook.Sheets.Count 'will be entered in A1 End Sub Gord Dibben MS Excel MVP On Wed, 4 Feb 2009 14:42:31 -0800, shaz0503 wrote: All Apologies about the title - hard to describe I have borrowed a terrific bit of code to count emails in specific folders; however the count result is only displayed in a message box - how do i get the result 'put' to a specific cell. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
code to 'put' calc displayed in message box in cells
If you're working on the activesheet, do something like this
=activesheet.range("C1").value = ... enter your value -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "shaz0503" wrote: All Apologies about the title - hard to describe I have borrowed a terrific bit of code to count emails in specific folders; however the count result is only displayed in a message box - how do i get the result 'put' to a specific cell. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
code to 'put' calc displayed in message box in cells
Gord
thanks heaps for this - I have included the code (minus your code) for you - have tried your suggestion but am obviously doing something wrong with editing code to 'put' value in msg box in cell d1. Again many thanks to those who have helped with this (includes those from other posts) shaz Sub HowManyDatedEmails_S() Dim objOutlook As Object, objnSpace As Object, objFolder As Object Dim EmailCount As Long Set objOutlook = CreateObject("Outlook.Application") Set objnSpace = objOutlook.GetNamespace("MAPI") On Error Resume Next Set objFolder = objnSpace.Folders("Mailbox - Sharon Hickox").Folders("Personal") If Err.Number < 0 Then Err.Clear MsgBox "No such folder." Exit Sub End If Dim iCount As Integer Dim DateCount As Integer Dim myFirstDate As Date Dim myLastDate As Date EmailCount = objFolder.Items.Count DateCount = 0 myFirstDate = Sheets("Sheet1").Range("A1").Value myLastDate = Sheets("Sheet1").Range("A2").Value For iCount = 1 To EmailCount With objFolder.Items(iCount) If DateSerial(Year(.ReceivedTime), _ Month(.ReceivedTime), _ Day(.ReceivedTime)) = myFirstDate And _ DateSerial(Year(.ReceivedTime), _ Month(.ReceivedTime), _ Day(.ReceivedTime)) <= myLastDate Then DateCount = DateCount + 1 End If End With Next iCount Set objFolder = Nothing Set objnSpace = Nothing Set objOutlook = Nothing MsgBox "Number of emails in Personal folder with matching date: " & DateCount, , "Personal date count" End Sub "Gord Dibben" wrote: Without seeing the code.................shrug shoulders<g Your code does a count and that count is displayed in a msgbox. Just redirect the count to a cell like count = Range("A1").Value Example code............. Sub sheets_num() MsgBox ThisWorkbook.Sheets.Count 'will display in msgbox Range("A1").Value = ThisWorkbook.Sheets.Count 'will be entered in A1 End Sub Gord Dibben MS Excel MVP On Wed, 4 Feb 2009 14:42:31 -0800, shaz0503 wrote: All Apologies about the title - hard to describe I have borrowed a terrific bit of code to count emails in specific folders; however the count result is only displayed in a message box - how do i get the result 'put' to a specific cell. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
code to 'put' calc displayed in message box in cells
Gord and Barb
with some fiddling I got it to do exaclty what I wanted...yeh... ended up using Barb's code thanks heaps Shaz "shaz0503" wrote: Gord thanks heaps for this - I have included the code (minus your code) for you - have tried your suggestion but am obviously doing something wrong with editing code to 'put' value in msg box in cell d1. Again many thanks to those who have helped with this (includes those from other posts) shaz Sub HowManyDatedEmails_S() Dim objOutlook As Object, objnSpace As Object, objFolder As Object Dim EmailCount As Long Set objOutlook = CreateObject("Outlook.Application") Set objnSpace = objOutlook.GetNamespace("MAPI") On Error Resume Next Set objFolder = objnSpace.Folders("Mailbox - Sharon Hickox").Folders("Personal") If Err.Number < 0 Then Err.Clear MsgBox "No such folder." Exit Sub End If Dim iCount As Integer Dim DateCount As Integer Dim myFirstDate As Date Dim myLastDate As Date EmailCount = objFolder.Items.Count DateCount = 0 myFirstDate = Sheets("Sheet1").Range("A1").Value myLastDate = Sheets("Sheet1").Range("A2").Value For iCount = 1 To EmailCount With objFolder.Items(iCount) If DateSerial(Year(.ReceivedTime), _ Month(.ReceivedTime), _ Day(.ReceivedTime)) = myFirstDate And _ DateSerial(Year(.ReceivedTime), _ Month(.ReceivedTime), _ Day(.ReceivedTime)) <= myLastDate Then DateCount = DateCount + 1 End If End With Next iCount Set objFolder = Nothing Set objnSpace = Nothing Set objOutlook = Nothing MsgBox "Number of emails in Personal folder with matching date: " & DateCount, , "Personal date count" End Sub "Gord Dibben" wrote: Without seeing the code.................shrug shoulders<g Your code does a count and that count is displayed in a msgbox. Just redirect the count to a cell like count = Range("A1").Value Example code............. Sub sheets_num() MsgBox ThisWorkbook.Sheets.Count 'will display in msgbox Range("A1").Value = ThisWorkbook.Sheets.Count 'will be entered in A1 End Sub Gord Dibben MS Excel MVP On Wed, 4 Feb 2009 14:42:31 -0800, shaz0503 wrote: All Apologies about the title - hard to describe I have borrowed a terrific bit of code to count emails in specific folders; however the count result is only displayed in a message box - how do i get the result 'put' to a specific cell. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
code to 'put' calc displayed in message box in cells
After or replacing the line.....................
MsgBox "Number of emails in Personal folder with matching date: " & _ DateCount, , "Personal date count" Add this line................................ ActiveSheet.Range("D1").Value = "Number of emails etc. is " & DateCount Gord On Thu, 5 Feb 2009 15:24:51 -0800, shaz0503 wrote: Gord thanks heaps for this - I have included the code (minus your code) for you - have tried your suggestion but am obviously doing something wrong with editing code to 'put' value in msg box in cell d1. Again many thanks to those who have helped with this (includes those from other posts) shaz Sub HowManyDatedEmails_S() Dim objOutlook As Object, objnSpace As Object, objFolder As Object Dim EmailCount As Long Set objOutlook = CreateObject("Outlook.Application") Set objnSpace = objOutlook.GetNamespace("MAPI") On Error Resume Next Set objFolder = objnSpace.Folders("Mailbox - Sharon Hickox").Folders("Personal") If Err.Number < 0 Then Err.Clear MsgBox "No such folder." Exit Sub End If Dim iCount As Integer Dim DateCount As Integer Dim myFirstDate As Date Dim myLastDate As Date EmailCount = objFolder.Items.Count DateCount = 0 myFirstDate = Sheets("Sheet1").Range("A1").Value myLastDate = Sheets("Sheet1").Range("A2").Value For iCount = 1 To EmailCount With objFolder.Items(iCount) If DateSerial(Year(.ReceivedTime), _ Month(.ReceivedTime), _ Day(.ReceivedTime)) = myFirstDate And _ DateSerial(Year(.ReceivedTime), _ Month(.ReceivedTime), _ Day(.ReceivedTime)) <= myLastDate Then DateCount = DateCount + 1 End If End With Next iCount Set objFolder = Nothing Set objnSpace = Nothing Set objOutlook = Nothing MsgBox "Number of emails in Personal folder with matching date: " & DateCount, , "Personal date count" End Sub "Gord Dibben" wrote: Without seeing the code.................shrug shoulders<g Your code does a count and that count is displayed in a msgbox. Just redirect the count to a cell like count = Range("A1").Value Example code............. Sub sheets_num() MsgBox ThisWorkbook.Sheets.Count 'will display in msgbox Range("A1").Value = ThisWorkbook.Sheets.Count 'will be entered in A1 End Sub Gord Dibben MS Excel MVP On Wed, 4 Feb 2009 14:42:31 -0800, shaz0503 wrote: All Apologies about the title - hard to describe I have borrowed a terrific bit of code to count emails in specific folders; however the count result is only displayed in a message box - how do i get the result 'put' to a specific cell. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I have a message displayed when a spreedsheet is opened ? | Excel Discussion (Misc queries) | |||
How can I get a leading zero (ZIP code) to show in a LEFT() calc? | Excel Worksheet Functions | |||
Code to calc | Excel Discussion (Misc queries) | |||
auto calc on, but have to edit (f2) cells to force re-calc..help! | Excel Worksheet Functions | |||
No message box displayed | Excel Discussion (Misc queries) |