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

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?