Hope i can get some help on this,

Below are two sub routines, lastfridays and ReturnsMissing the latter is in a
differant module called missing.

My problem is that i want the values of the variant array missed to be used
in returnsmissing. when i run this all is well with the cde until i try to
pass to missing.returnsmissing as i get a "type mismatch error array or
user defined expected" error

The code does open a db but error is produced before code runs?

Can someone enlighten me please I have looked at threads on this here and
tried some solutions suggested to no avail. I am reasonablly ok with VBA
though still a novice. Arrays are still a bit of a mystery to me which i am
making progress on but have a long way to go.

Thanks in advance


Paul

Option Explicit
Public Sub LastFridays()
Dim validationdate As Date
Dim weekending As Date
Dim fromdate As Date
Dim missingdate()
Dim todate
Dim misseddate() As Variant
Dim Trustcheck
Dim arraycount
Dim chkdate
Dim LastRowval
Dim datechk
Dim i As Long
Dim missedcount
Dim completed
With CheckReturns
fromdate = .ufFromDays & "/" & .UFFromMonths & "/" & .UFfromYear
todate = .ValDate
validationdate = todate
Trustcheck = .UFTrustNAme
arraycount = 0
missedcount = 0
ReDim misseddate(missedcount + 1)
ReDim missingdate(arraycount + 1)
For chkdate = fromdate To todate Step 7
missingdate(arraycount) = chkdate
arraycount = arraycount + 1
ReDim Preserve missingdate(arraycount + 1)

MsgBox missingdate(arraycount - 1) & " " & arraycount - 1

Next
MsgBox arraycount & " " & missingdate(arraycount - 1)
DataBase.openDB
LastRowval = LastRow()

For datechk = 0 To arraycount
MsgBox " Next Date " & missingdate(datechk)
completed = False
For i = 2 To LastRowval
weekending = Range("Week_Ending").Offset(rowoffset:=i - 1)
If StrComp(weekending, missingdate(datechk)) = 0 Then
If StrComp(Range("trust").Offset(rowoffset:=i - 1), Trustcheck) = 0
Then
completed = True
GoTo nextdate
End If
End If
Next
If completed = False Then
' Do the stuff
MsgBox "Whoops No return for " & Trustcheck & " " & " Validation
Date " & missingdate(datechk) & " " & " DB Date " & weekending
misseddate(missedcount) = missingdate(datechk)
missedcount = missedcount + 1
ReDim Preserve misseddate(missedcount + 1)
End If
nextdate:
Next
'put dates missing onto user form
'this produces error "Type Mismatch array or user defined expected"
missing.ReturnsMissing (misseddate())
End With
End Sub

Public Sub ReturnsMissing(ByRef misseddate() As Variant)
With CheckReturns
lowerb = LBound(misseddate)
upperb = UBound(misseddate)
For i = lowerb To upperb
MsgBox misseddate(i)
Next
End With
End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200809/1