Error using Application.WorksheetFunction
 

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 procedu

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

Error using Application.WorksheetFunction
 

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
--
Gary''s Student
gsnu200707


"Cabaco" wrote:

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 procedu

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

Error using Application.WorksheetFunction
 

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


"Gary''s Student" wrote:

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
--
Gary''s Student
gsnu200707


"Cabaco" wrote:

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 procedu

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