Error using Application.WorksheetFunction

G

Guest

Hi,

I need your help!!!
I'm using MS Office Pro 2007 and in a VBA procedure I use a
Application.WorksheetFunction.VLookup.... that doesn't work - Just returns
error no 400. Am I missing any library?

Thanks.
Cabaco

Here is the procedure:

Sub ListSchedule()
'DEFINITIONS:
Dim sShtData, sShtLayout As Worksheet
Dim MyXlFunc As WorksheetFunction
Dim lMes, lAno, lFunc1, lFunc9, lCountr As Long
Dim rEmployees, rMonthTable, rWeekdays As Range
Dim rMes, rAno, rPeriod As Range
Dim rEmployeeName, rEmployeeNo, rEmployeeTest As Range
'DATA:
Set MyXlFunc = Application.WorksheetFunction
Set sShtData = Application.Worksheets("Funcion")
Set sShtLayout = Application.Worksheets("FichaPonto")
Set rEmployees = sShtData.Range("A1").CurrentRegion
Set rMonthTable = sShtLayout.Range("X7:Y19")
Set rWeekdays = sShtLayout.Range("X21:AA28")
Set rMes = sShtLayout.Range("V3")
Set rAno = sShtLayout.Range("V4")
Set rPeriod = sShtLayout.Range("V2")
Set rEmployeeName = sShtLayout.Range("E4")
Set rEmployeeNo = sShtLayout.Range("P4")
Set rEmployeeTest = sShtLayout.Range("U3")
lMes = Month(Date)
lAno = Year(Date)
lCountr = 1
'RUN:
lMes = InputBox("Mês:", "Indicar...", Month(Date))
lAno = InputBox("Ano:", "Indicar...", Year(Date))
lFunc1 = InputBox("Do funcionário:", "Parametros de impressão...")
lFunc9 = InputBox("Ao funcionário:", "Parametros de impressão...")
rMes.Value = lMes
rAno.Value = lAno
For lCountr = lFunc1 To lFunc9 Step 1
rEmployeeNo.Value = lCountr
rEmployeeTest.Value = MyXlFunc.VLookup(lCountr, rEmployees, 2, False)
If rEmployeeTest.Value = 1 Then
sShtLayout.PrintOut
Else
End If
Next
'CLEAN:
Set sShtData = Nothing
Set sShtLayout = Nothing
Set rEmployees = Nothing
Set rMonths = Nothing
Set rWeekdays = Nothing
Set rMes = Nothing
Set rAno = Nothing
Set rPeriod = Nothing
Set MyXlFunc = Nothing
lMes = 0
lAno = 0
lFunc1 = 0
lFunc9 = 0
lCountr = 0
End Sub
 
G

Guest

You have lCount as Long and use it as a loop index. Check to see if you need
a range. for example

Set MyXlFunc = Application.WorksheetFunction
x = MyXlFunc.Sum(Range("A1:A10"))

will work

x=MyXlFunc.Sum(A1:A10)

does not
 
G

Guest

Thanks for your answer,

lCountr is a number, an id, that that the function should look for in the
range rEmployees. Besides, I've tried to use other functions and they don't
work either.
Simply, I can't make the Application.WorksheetFunction work.

If you have any ideas, I'd appreciate.
Thanks,
Cabaco
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top