![]() |
Problem with code
I'm in the process of creating an excel spreadsheet that interacts
with Outlook via VBA. A user is complaining of an error on the following line in VBA qcount = Application.WorksheetFunction.CountIf(qrange, qLookfor) I have qrange defined as a range and qLookfor as a Lookup string that I am counting in the sheet. Can anybody see what could be causing the problem? Here is the full snippet of code for a feature which deletes Appointments in Outlook based on data in the spreadsheet ------------------------------------------ Sub DeleteAppt() Dim qrange As Range Dim qLookfor As String '// Ensure you write to the correct sheet Sheets("Get Appointments").Select '// The boring stuff Dim olApp As Outlook.Application Dim olNs As Namespace Dim olFldr As MAPIFolder Dim olApt As AppointmentItem Set olApp = New Outlook.Application Set olNs = olApp.GetNamespace("MAPI") Set olFldr = olNs.GetDefaultFolder(olFolderCalendar) '// Lookup range in sheet (unique ID range) Set qrange = Range("L:L") Set qdatabase = Range("L:N") For Each olApt In olFldr.Items '// Pick up and translate variables // With olApt qTask = .Subject qDesc = .Body qStartDay = DateValue(.Start) qStartTime = TimeValue(.Start) qEndDay = DateValue(.End) qEndTime = TimeValue(.End) 'qLabel = .Categories qShowAs = .BusyStatus '// Convert to Desc // If qShowAs = 0 Then xShowAs = "Busy" If qShowAs = 1 Then xShowAs = "Free" If qShowAs = 2 Then xShowAs = "Tentative" If qShowAs = 3 Then xShowAs = "Out of office" qLocation = .Location qResource = .Resources qTo = .OptionalAttendees qLookfor = qStartDay + qStartTime & "/" & qEndDay + qEndTime & "/" & qTask & "/" & qShowAs '// Condition 1: qLookfor is in qrange // On Error Resume Next qcount = Application.WorksheetFunction.CountIf(qrange, qLookfor) If Err.Number 0 Then MsgBox "Error Counting in " & " Column L", vbCritical, "ExcelToOutlookTaskSynch" On Error GoTo 0 If qcount 0 Then '// Condition 2: Corresponding Delete Flag = Y // qDelete = Application.WorksheetFunction.Index(qdatabase, WorksheetFunction.Match(qLookfor, qrange, 0), 3) If qDelete = "Y" Then .Delete End If End If End With Next olApt Set olApt = Nothing Set olFldr = Nothing Set olNs = Nothing Set olApp = Nothing Call GetAppt End Sub ------------------------------------------ Any help appreciated, Thanks, Anthony |
Problem with code
Since that's an Excel issue, I'd suggest you ask in an Excel forum. Be sure to give details of any error message you're getting and your Excel version.
-- Sue Mosher, Outlook MVP Author of Microsoft Outlook 2007 Programming: Jumpstart for Power Users and Administrators http://www.outlookcode.com/article.aspx?id=54 "somethinglikeant" wrote in message ... I'm in the process of creating an excel spreadsheet that interacts with Outlook via VBA. A user is complaining of an error on the following line in VBA qcount = Application.WorksheetFunction.CountIf(qrange, qLookfor) I have qrange defined as a range and qLookfor as a Lookup string that I am counting in the sheet. Can anybody see what could be causing the problem? |
All times are GMT +1. The time now is 03:36 PM. |
Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Search Engine Friendly URLs by vBSEO 2.4.0
Copyright ©2004-2006 OutlookBanter.com