Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Incorrect formulaR1C1 when activesheet is chart
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.Cou nt) ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Nam e = 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(xlCellTypeB lanks).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.Cou nt - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart Math incorrect | Charts and Charting in Excel | |||
Pie Chart Percentage incorrect | Charts and Charting in Excel | |||
Pie Chart labels seem incorrect | Charts and Charting in Excel | |||
activesheet: chart or worksheet? | Excel Programming | |||
Activecell.FormulaR1C1 Versus ActiveSheet.cells(column, row) | Excel Programming |