Ok, that works, thanks.
Problem though is this. If my (dateFrom) is a Monday and (dateto) is a Saturday, and there is no record for (dateTo), it shows me Friday which is perfect. However because the number of days (nod.text) is 6 this formula will then sum from (Sunday to Friday) where it needs to still sum from (Monday to Friday)
So my question is. How can I change this formula, to instead of using (-nod.value) as a starting point. Use the (FromDate).
Code: Select all
Dbtarget = Application.WorksheetFunction.Sum(target.Offset(-nod.value).Resize(nod.value + 1))
Code: Select all
strSiteID = Me.site1.Text
On Error Resume Next
Set ws = Worksheets("" & strSiteID & "")
On Error GoTo 0
ws.Activate
'********Gross Revenue
'Find date value or close to it in selected sheet
irow = Application.WorksheetFunction.Match(CLng(CDate(Me.dateto.Text)), ws.Range("A:A"))
'Find Metric value in selected sheet
Set rng = ws.Rows(1).Find(Me.met1.Text)
If rng Is Nothing Then
MsgBox "Metric not found!", vbExclamation
Exit Sub
End If
icol = rng.Column
Dim target As Range
Set target = ws.Cells(irow, icol)
'Determine Dashboard Target and insert value from site sheet target
Set Db = Worksheets("Dashboard")
Db.Activate
'Find site in dashboard
Set rngFound = Db.Columns("A:A").Find(Me.site1.Text)
If rngFound Is Nothing Then
MsgBox "Site not found!", vbExclamation
Exit Sub
End If
irow = rngFound.Row
'Find Metric value in dashboard
Set rng = Db.Rows(5).Find(Me.met1.Text)
If rng Is Nothing Then
MsgBox "Metric not found!", vbExclamation
Exit Sub
End If
icol = rng.Column
Dim Dbtarget As Range
Set Dbtarget = Db.Cells(irow, icol)
Dbtarget = Application.WorksheetFunction.Sum(target.Offset(-nod.value).Resize(nod.value + 1))
I hope this isn't too confusing...