I am posting weekly average data from one sheet to another to create a graph over time, by week number. To sue the values from another sheet the reference needs to contain the sheet name (SrcSht). Msgbox shows (what appears) to be a properly constructed range. Using this in the
WkAve.Formula shows the string greyed after the first single quote(’). I have tried using “’” but still get the string greyed out like the remaining is a remark
REM ***** BASIC *****
Sub Main
Dim Doc, Asheet, AllSheets, Dest, DestSheet, SourceSht as Object
Dim FirstDOW, LastDOW, FirstROW, LastROW as Object 'a range for First and last rows scanned
Dim DateCel, CalCel as Object
Dim oFA as Object
Dim SrcSht, DestSht, Mstr, WkAveFstr as String
Dim i, Srow, WNum, ThisWk as Integer
SrcSht = "Data"
DestSht = "Average Values"
Doc = ThisComponent
AllSheets = Doc.Sheets
' Find First and last days of the first week
Src = AllSheets.getbyName(SrcSht)
Dest = AllSheets.getbyName(DestSht)
FirstDOW = Dest.getCellRangeByName("Z1")
LastDOW = Dest.getCellRangeByName("Z2")
FirstROW = Dest.getCellRangeByName("Z3")
NextROW = Dest.getCellRangeByName("Z4")
DateCel = Src.getCellRangeByName("A2" )
CalCel = Src.getCellRangeByName("G2")
WkDate = Dest.getCellRangeByName("A2")
Wkn = Dest.getCellRangeByName("B2")
WkAve = Dest.getCellRangeByName("C2")
ThisWk = WkNum(DateCel)
FirstDOW.value = DateCel.value
LastDOW.value = DateCel.value
Mstr = "Date " & " Calories " & Chr(13)
If FirstROW.Value = 0 Then
Srow = 1
FirstROW.value = Srow
Else
Srow = NextROW.value
'FirstROW.value = Srow
End If
For i = Srow to 100 'Row numbers. Start at row 2
WNum = WkNum(DateCel)
'Get values for week
' Loop thru rows finding the last day of this week
Do Until WkNum(DateCel) <> ThisWk
Mstr = Mstr & CDate(DateCel.value) & " " & CalCel.value & " for week " & WNum & CHR(13)
NextROW.value = i
i = i + 1
DateCel = Src.getCellRangeByName("A" & i)
CalCel = Src.getCellRangeByName("G" & i)
Loop
WkDate.value = LastDOW.value
Wkn.value = WNum
WkAveFstr = "'" & SrcSht &"'.$G$" & FirstROW.value & ":'" & SrcSht & "':$G$" & NextROW.value
WkAve.Formula = "=Average(WkAveFstr)"
End Sub