I haven't worked across applications in quite a while, but without walking
through the procedure, I would guess that the focus is still on the Outlook
file and not the
Excel sheet.

"excel-ant" wrote:

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