Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Application.WorksheetFunction error | Excel Discussion (Misc queries) | |||
Need help with Application.WorksheetFunction | Excel Discussion (Misc queries) | |||
application.worksheetfunction | Excel Programming | |||
Application.worksheetfunction | Excel Programming | |||
Using Application.WorksheetFunction.Ln(...) in VBA | Excel Programming |