AaronBanker
asked on
Added a column screws up code
Hello,
I have downloaded a roster template from Online PC Learning - see below link for understanding how things are supposed to work - I added a column to the to the "Codes" tab but the 'HourTotal' function is no longer works
How it is supposed to work is
open the 'Winter Ops' Tab
select a cell (Q10)
Click on the "Control" Button and frmShifts will pop up
Select data from the cboIce
double click on the cboIce and the data is transferred to the cell Q10
what is supposed to happen is, in cells G10 and X10 the total hour is supposed to be displayed. It doesn't. ( It did before I added the column). I did change the all the Name Manager codes to account for the extra column
I assumed incorrectly that all i would need to do is change the "4" to a "5" in the below line. but i do not get the value in cells G10 and X10
Set aCell = Sheet2.Range("Combined").F ind(What:= strSearch, LookIn:=xlValues).Offset(0 , 4)
Below is the complete function
'function with 2 arguments
'/ 1.range /
'2. shift type
Function HourTotal(Days As Range, ShiftType As Range)
'dim variables
Dim c As Range
Dim strSearch As String
Dim TotalHours As Double
Dim aCell As Range
Dim sType As String
'set variable for start of hours count
TotalHours = 0
'loop though first argument
For Each c In Days
'only check if data is in range and trim cell
If Trim(c.Value) = "" Then
Else
'set variable for find criteria
strSearch = Trim(c.Value)
'find the value
Set aCell = Sheet2.Range("Combined").F ind(What:= strSearch, LookIn:=xlValues).Offset(0 , 4)
'offset to shift type
sType = aCell.Cells(1, -3).Value
'check if the 2nd argument is meet
If sType = ShiftType Then
'alternatively you can offset//////
'—-If aCell.Offset(0, -3) = ShiftType Then—
'add hours if both arguments are meet
TotalHours = TotalHours + aCell
End If
End If
'move to next staff members range
Next c
'result
HourTotal = TotalHours '——————–
End Function
Why is this not working??
Below is a link to the tutorial
(http://www.onlinepclearning.com/excel-roster-hours-and-overtime-roster-system-excel-vba/ )
Ops-Schedule-4.xlsm
I have downloaded a roster template from Online PC Learning - see below link for understanding how things are supposed to work - I added a column to the to the "Codes" tab but the 'HourTotal' function is no longer works
How it is supposed to work is
open the 'Winter Ops' Tab
select a cell (Q10)
Click on the "Control" Button and frmShifts will pop up
Select data from the cboIce
double click on the cboIce and the data is transferred to the cell Q10
what is supposed to happen is, in cells G10 and X10 the total hour is supposed to be displayed. It doesn't. ( It did before I added the column). I did change the all the Name Manager codes to account for the extra column
I assumed incorrectly that all i would need to do is change the "4" to a "5" in the below line. but i do not get the value in cells G10 and X10
Set aCell = Sheet2.Range("Combined").F
Below is the complete function
'function with 2 arguments
'/ 1.range /
'2. shift type
Function HourTotal(Days As Range, ShiftType As Range)
'dim variables
Dim c As Range
Dim strSearch As String
Dim TotalHours As Double
Dim aCell As Range
Dim sType As String
'set variable for start of hours count
TotalHours = 0
'loop though first argument
For Each c In Days
'only check if data is in range and trim cell
If Trim(c.Value) = "" Then
Else
'set variable for find criteria
strSearch = Trim(c.Value)
'find the value
Set aCell = Sheet2.Range("Combined").F
'offset to shift type
sType = aCell.Cells(1, -3).Value
'check if the 2nd argument is meet
If sType = ShiftType Then
'alternatively you can offset//////
'—-If aCell.Offset(0, -3) = ShiftType Then—
'add hours if both arguments are meet
TotalHours = TotalHours + aCell
End If
End If
'move to next staff members range
Next c
'result
HourTotal = TotalHours '——————–
End Function
Why is this not working??
Below is a link to the tutorial
(http://www.onlinepclearning.com/excel-roster-hours-and-overtime-roster-system-excel-vba/ )
Ops-Schedule-4.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I think there might be a problem with the Named Range "Combined", do you know what it is actually supposed to refert to?
Much of the code can be improved and using all those named ranges in the code will cause problems when changes are made to the layout of the sheets, I would always set the ranges in the code using range variables.
Also, running debug on the code throws up several errors
Much of the code can be improved and using all those named ranges in the code will cause problems when changes are made to the layout of the sheets, I would always set the ranges in the code using range variables.
Also, running debug on the code throws up several errors
ASKER
Worked perfectly. Thanks for the help
You're welcome and I'm glad I was able to help.
If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.
Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.
Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
Open in new window
Where are you using the Function?