Hi There,
I am writing a function for a project budget in excel using VBA .
I have a range of columns, each column represents one week and has a week-ending date in row 3 of each column in the range. (as extra weeks can be added, I have named the column range). each row represents a project resource (person) which could have a period (between date x and date z) where they are charged out to the client a a discounted rate. I am wanting to calculate the amount of days in which the resource has worked within the discount period so that I can accurately calculate the resources cost. to do this, I need to place a function in each row which queries the range of columns to find the discount period start date and hold the col number in a variable and then the same with the end date and then calculate all days that the resource has worked between start and finish dates on their row (same row that the function resides in)
I am having trouble getting this to work, could someone please help.
Sorry if this is a bit confusing.
Function DiscountDays()
Dim curRow, curCol, colDateA, colDateB, discFrom, discTo
curRow = Application.ThisCell.Row
curCol = Application.ThisCell.Column
discFrom = Application.ThisCell.Offset(0, -2).Value
discTo = Application.ThisCell.Offset(0, -1).Value
colDateA = Range("activeDate").Find(What:=discFrom) .Row 'Where activeDate id the named range
colDateB = Range("activeDate").Find(What:=discTo).Row
'sum values of cells on this row between these columns inclusive.
DiscountDays = Sum.curRow.Range(Columns(colDateA), Columns(colDateB)) 'output value
End Function
I am writing a function for a project budget in excel using VBA .
I have a range of columns, each column represents one week and has a week-ending date in row 3 of each column in the range. (as extra weeks can be added, I have named the column range). each row represents a project resource (person) which could have a period (between date x and date z) where they are charged out to the client a a discounted rate. I am wanting to calculate the amount of days in which the resource has worked within the discount period so that I can accurately calculate the resources cost. to do this, I need to place a function in each row which queries the range of columns to find the discount period start date and hold the col number in a variable and then the same with the end date and then calculate all days that the resource has worked between start and finish dates on their row (same row that the function resides in)
I am having trouble getting this to work, could someone please help.
Sorry if this is a bit confusing.
Function DiscountDays()
Dim curRow, curCol, colDateA, colDateB, discFrom, discTo
curRow = Application.ThisCell.Row
curCol = Application.ThisCell.Column
discFrom = Application.ThisCell.Offset(0, -2).Value
discTo = Application.ThisCell.Offset(0, -1).Value
colDateA = Range("activeDate").Find(What:=discFrom) .Row 'Where activeDate id the named range
colDateB = Range("activeDate").Find(What:=discTo).Row
'sum values of cells on this row between these columns inclusive.
DiscountDays = Sum.curRow.Range(Columns(colDateA), Columns(colDateB)) 'output value
End Function
Last edited:
