Incorrect formulaR1C1 when activesheet is chart

K

Kalon

I have some code that manipulates the FormulaR1C1 value of some
cells. It works when the activesheet is not a chart and doesn't
work when it is.
The problem is that the FormulaR1C1 is offset by 1 row when the
active sheet it a chart. Normally resulting cells would contain
"=RC26*RC29*RC25", but if the activesheet is a chart the
resulting cells contain "=R[-1]C26*R[-1]C29*R[-]1C25" even though
I never add the [-1] in my code. Excel seems to do that by itself
when the activesheet is a chart.
The exact same code is used in both cases. Yeah an easy fix is to
make sure that the active sheet is not a chart but I shouldn't have
to do that, right?
The code produces no VBA errors.
Can anyone tell me what is going on?

Thanks,
Kalon


Here is the Code:

Const DataWorksheetName = "Data"
Const TableDataWorksheetName = "myTableData"

Sub CreateTableData()
Dim mySheet As Variant, lastColumn As Integer

DeleteSheet (TableDataWorksheetName) 'if the sheet already
exists delete it

Set mySheet = ActiveSheet 'remember the current active sheet
'copy the data worksheet and move to the end
ThisWorkbook.Sheets(DataWorksheetName).Copy
After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name =
TableDataWorksheetName 'rename the worksheet
mySheet.Activate 'return to the original activesheet
Set mySheet = ThisWorkbook.Sheets(TableDataWorksheetName)

'this is to make sure that there aren't any blanks in the column headers

mySheet.Rows(1).EntireRow.SpecialCells(xlCellTypeBlanks).EntireColumn.Delete

lastColumn = mySheet.Range("A1",
mySheet.Range("IV1").End(xlToLeft)).Columns.Count
'Create 'NH' Column
With mySheet.Cells(lastColumn + 1)
.Value = "NH"
'NH = CN * WCT * JR
If mySheet.Range("A1").CurrentRegion.Rows.Count > 1 Then 'make
sure there are data rows to work with
On Error Resume Next
.Resize(mySheet.Range("A1").CurrentRegion.Rows.Count - 1,
1).Offset(1).FormulaR1C1 = _
"=RC" & mySheet.Rows(1).Find("CN").Column & _
"*RC" & mySheet.Rows(1).Find("WCT").Column & _
"*RC" & mySheet.Rows(1).Find("JR").Column
If Err = 91 Then
MsgBox "Cannot create TableData. Either some or all of the
following columns are missing: " _
& vbNewLine & " CN " _
& vbNewLine & " WCT " _
& vbNewLine & " JR", vbExclamation, "Missing
Columns"
ElseIf Err <> 0 Then
MsgBox "Cannot create TableData. Error: " &
Err.Description, vbExclamation, "Error Making TableData"
End If
On Error GoTo 0
End If
.EntireColumn.AutoFit
End With
End Sub
 
Top