Link to home
Start Free TrialLog in
Avatar of AaronBanker
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").Find(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").Find(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
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I would say that aCell is a single cell and the code is attempting to get a value one column to the left and two rows below

Set aCell = Sheet2.Range("Combined").Find(What:=strSearch, LookIn:=xlValues).Offset(0, 5)
'offset to shift type
sType = aCell.Offset(1, -2).Value

Open in new window


Where are you using the Function?
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
Avatar of AaronBanker
AaronBanker

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