Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
WeekNum Trouble
I am trying to return a simple week number based on the value input on a user
form and place the value in a work sheet column. I have tried the WeekNum formula but it gives me "Compile Error: Sub or Function Not defined" I have the Analysis ToolPak installed. I am using a "pop-up" calendar to input the date for this field. Would that cause the problem perhaps???? The Macro posts all the values of the userform to the last row of the spread sheet. I want a column that simple adds in a week number. Nothing fancy. The code that gives me the error is as follows: 'Determine the next empty row NextRow = Range("A" & Rows.Count).End(xlUp).Offset(1).Row 'Transfer Data Cells(NextRow, 5) = QCMonitor.RevLast.Value Cells(NextRow, 6) = QCMonitor.RevFirst.Value Cells(NextRow, 7) = QCMonitor.Dept.Value Cells(NextRow, 8) = QCMonitor.RevDate.Value Cells(NextRow, 9) = QCMonitor.AcctNumber.Value 'Input values for house-keeping cells Cells(NextRow, 1) = 1 Cells(NextRow, 2) = Format(QCMonitor.RevDate.Value, "mmm") Cells(NextRow, 3) = WeekNum(QCMonitor.RevDate.Value, 1) Cells(NextRow, 4) = Year(QCMonitor.RevDate.Value) Everything works but the WeekNum Formula, the answer is probably very simple, but I have tried numberous other formulas without success. Ideas anybody? WillRn I |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
WeekNum Trouble
Hi will
Tools Add-ins and also install Analysis ToolPak VBA In the VBA editor go to ToolsReferences and select atpvbaen.xla -- Regards Ron de Bruin http://www.rondebruin.nl "WillRn" wrote in message ... I am trying to return a simple week number based on the value input on a user form and place the value in a work sheet column. I have tried the WeekNum formula but it gives me "Compile Error: Sub or Function Not defined" I have the Analysis ToolPak installed. I am using a "pop-up" calendar to input the date for this field. Would that cause the problem perhaps???? The Macro posts all the values of the userform to the last row of the spread sheet. I want a column that simple adds in a week number. Nothing fancy. The code that gives me the error is as follows: 'Determine the next empty row NextRow = Range("A" & Rows.Count).End(xlUp).Offset(1).Row 'Transfer Data Cells(NextRow, 5) = QCMonitor.RevLast.Value Cells(NextRow, 6) = QCMonitor.RevFirst.Value Cells(NextRow, 7) = QCMonitor.Dept.Value Cells(NextRow, 8) = QCMonitor.RevDate.Value Cells(NextRow, 9) = QCMonitor.AcctNumber.Value 'Input values for house-keeping cells Cells(NextRow, 1) = 1 Cells(NextRow, 2) = Format(QCMonitor.RevDate.Value, "mmm") Cells(NextRow, 3) = WeekNum(QCMonitor.RevDate.Value, 1) Cells(NextRow, 4) = Year(QCMonitor.RevDate.Value) Everything works but the WeekNum Formula, the answer is probably very simple, but I have tried numberous other formulas without success. Ideas anybody? WillRn I |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
WeekNum Trouble
I have that one installed also . . . : (
"Ron de Bruin" wrote: Hi will Tools Add-ins and also install Analysis ToolPak VBA In the VBA editor go to ToolsReferences and select atpvbaen.xla -- Regards Ron de Bruin http://www.rondebruin.nl "WillRn" wrote in message ... I am trying to return a simple week number based on the value input on a user form and place the value in a work sheet column. I have tried the WeekNum formula but it gives me "Compile Error: Sub or Function Not defined" I have the Analysis ToolPak installed. I am using a "pop-up" calendar to input the date for this field. Would that cause the problem perhaps???? The Macro posts all the values of the userform to the last row of the spread sheet. I want a column that simple adds in a week number. Nothing fancy. The code that gives me the error is as follows: 'Determine the next empty row NextRow = Range("A" & Rows.Count).End(xlUp).Offset(1).Row 'Transfer Data Cells(NextRow, 5) = QCMonitor.RevLast.Value Cells(NextRow, 6) = QCMonitor.RevFirst.Value Cells(NextRow, 7) = QCMonitor.Dept.Value Cells(NextRow, 8) = QCMonitor.RevDate.Value Cells(NextRow, 9) = QCMonitor.AcctNumber.Value 'Input values for house-keeping cells Cells(NextRow, 1) = 1 Cells(NextRow, 2) = Format(QCMonitor.RevDate.Value, "mmm") Cells(NextRow, 3) = WeekNum(QCMonitor.RevDate.Value, 1) Cells(NextRow, 4) = Year(QCMonitor.RevDate.Value) Everything works but the WeekNum Formula, the answer is probably very simple, but I have tried numberous other formulas without success. Ideas anybody? WillRn I |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
WeekNum Trouble
Well, . . .Now that i've decided to read the post, . . . I saw the part about
adding in the atpvbaen.xla. It works now, Much thanks! "Ron de Bruin" wrote: Hi will Tools Add-ins and also install Analysis ToolPak VBA In the VBA editor go to ToolsReferences and select atpvbaen.xla -- Regards Ron de Bruin http://www.rondebruin.nl "WillRn" wrote in message ... I am trying to return a simple week number based on the value input on a user form and place the value in a work sheet column. I have tried the WeekNum formula but it gives me "Compile Error: Sub or Function Not defined" I have the Analysis ToolPak installed. I am using a "pop-up" calendar to input the date for this field. Would that cause the problem perhaps???? The Macro posts all the values of the userform to the last row of the spread sheet. I want a column that simple adds in a week number. Nothing fancy. The code that gives me the error is as follows: 'Determine the next empty row NextRow = Range("A" & Rows.Count).End(xlUp).Offset(1).Row 'Transfer Data Cells(NextRow, 5) = QCMonitor.RevLast.Value Cells(NextRow, 6) = QCMonitor.RevFirst.Value Cells(NextRow, 7) = QCMonitor.Dept.Value Cells(NextRow, 8) = QCMonitor.RevDate.Value Cells(NextRow, 9) = QCMonitor.AcctNumber.Value 'Input values for house-keeping cells Cells(NextRow, 1) = 1 Cells(NextRow, 2) = Format(QCMonitor.RevDate.Value, "mmm") Cells(NextRow, 3) = WeekNum(QCMonitor.RevDate.Value, 1) Cells(NextRow, 4) = Year(QCMonitor.RevDate.Value) Everything works but the WeekNum Formula, the answer is probably very simple, but I have tried numberous other formulas without success. Ideas anybody? WillRn I |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
weeknum | Excel Discussion (Misc queries) | |||
weeknum | Excel Discussion (Misc queries) | |||
WEEKNUM | Excel Worksheet Functions | |||
Weeknum | Excel Worksheet Functions | |||
WEEKNUM() | Excel Discussion (Misc queries) |