Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference the previous Worksheet in a Formula
Hello
The following code is supposed to always reference the same cell in the previous worksheet. Unfortunately, I can't figure out the sheet name with the index number for the previous sheet. The line ActiveCell.FormulaR1C1 = "=Sheets(x-1)!RC+2" doesn't work. Any suggestions? Dim WeekStep As Integer Dim TotalSheets As Integer Dim x As Integer WeekStep = InputBox("Number of weeks per worksheet:") TotalSheets = InputBox("Number of Worksheets:") For x = 2 To TotalSheets Sheets(x).Select Range("A5").Select ActiveCell.FormulaR1C1 = "=Sheets(x-1)!RC+2" Next x -- Thanks very much. KDJ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference the previous Worksheet in a Formula
When you use the .FormulaR1C1 property, you are directly setting the cell's
formula to the text within the quotes. This means that any variables/methods/properties used in VBA (such as x in this example). You must convert these to text that makes sense in a worksheet formula, so: ActiveCell.FormulaR1C1 = "=Sheets(x-1)!RC+2" becomes: ActiveCell.FormulaR1C1 = "=" & Sheets(x-1).Name & "!RC+2" Note also that you do not have to select a worksheet or cell in VBA before you can manipulate it, so your code can be replace simply with : For x = 2 To TotalSheets Sheets(x).Range("A5").FormulaR1C1 = "=Sheets(x-1)!RC+2" Next x Cheers, Dave "KDJ" wrote: Hello The following code is supposed to always reference the same cell in the previous worksheet. Unfortunately, I can't figure out the sheet name with the index number for the previous sheet. The line ActiveCell.FormulaR1C1 = "=Sheets(x-1)!RC+2" doesn't work. Any suggestions? Dim WeekStep As Integer Dim TotalSheets As Integer Dim x As Integer WeekStep = InputBox("Number of weeks per worksheet:") TotalSheets = InputBox("Number of Worksheets:") For x = 2 To TotalSheets Sheets(x).Select Range("A5").Select ActiveCell.FormulaR1C1 = "=Sheets(x-1)!RC+2" Next x -- Thanks very much. KDJ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference the previous Worksheet in a Formula
Try
For x = 2 To TotalSheet Sheets(x).Range("A5").FormulaR1C1 = "=" & Sheets(x - 1).Name & "!RC+2" Next x -- Jacob "KDJ" wrote: Hello The following code is supposed to always reference the same cell in the previous worksheet. Unfortunately, I can't figure out the sheet name with the index number for the previous sheet. The line ActiveCell.FormulaR1C1 = "=Sheets(x-1)!RC+2" doesn't work. Any suggestions? Dim WeekStep As Integer Dim TotalSheets As Integer Dim x As Integer WeekStep = InputBox("Number of weeks per worksheet:") TotalSheets = InputBox("Number of Worksheets:") For x = 2 To TotalSheets Sheets(x).Select Range("A5").Select ActiveCell.FormulaR1C1 = "=Sheets(x-1)!RC+2" Next x -- Thanks very much. KDJ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference the previous Worksheet in a Formula
Thank you to both of you.
KDJ "Jacob Skaria" wrote: Try For x = 2 To TotalSheet Sheets(x).Range("A5").FormulaR1C1 = "=" & Sheets(x - 1).Name & "!RC+2" Next x -- Jacob "KDJ" wrote: Hello The following code is supposed to always reference the same cell in the previous worksheet. Unfortunately, I can't figure out the sheet name with the index number for the previous sheet. The line ActiveCell.FormulaR1C1 = "=Sheets(x-1)!RC+2" doesn't work. Any suggestions? Dim WeekStep As Integer Dim TotalSheets As Integer Dim x As Integer WeekStep = InputBox("Number of weeks per worksheet:") TotalSheets = InputBox("Number of Worksheets:") For x = 2 To TotalSheets Sheets(x).Select Range("A5").Select ActiveCell.FormulaR1C1 = "=Sheets(x-1)!RC+2" Next x -- Thanks very much. KDJ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reference previous worksheet within VLOOKUP | Excel Worksheet Functions | |||
formula to reference cell on previous worksheet | Excel Worksheet Functions | |||
need formula to reference a cell in previous worksheet | Excel Worksheet Functions | |||
How to reference to the previous worksheet regardless of title? | Excel Worksheet Functions | |||
reference to previous worksheet | Excel Programming |