LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default VB-How can I use a variable in a sheet reference?

First understand that I make no claims to proficiency in VB, I just stumble
around going from recording macros to editing in VB until things work, so if
my structure and formats are goofy and violate good coding practice, I
apologize in advance. Let's move on.

I want to calculate the sum of values in a particular cell of three
worksheets. The names of the worksheets will be in the form of "Rnd x" . I
have a macro to add the three new worksheets that the user can invoke at any
time up to a total of 20 worksheets, resulting in "x" having a value between
2 and 20.

Following the insertion of these three special sheets, the user can then add
more sheets that would not be included in the calculation.

My goal is for the first sheet of the three to provide the value of the cell
on that worksheet alone, the second sheet to sum the cell value of the first
and second sheet, and the third sheet to sum the value for the first through
third sheets.

I have defined variables to use for the value of "x" but I don't know how to
format the VB code to accept the variables. Here is my code (don't laugh).


Application.Run "'NWSS Scoring.xls'!Add_Round"
Set FirstRound = Range("E3")
Set RoundsFlown = Worksheets("Pilot Registration").Range("W8")
Application.Goto Reference:="R3C84"
ActiveCell.FormulaR1C1 = _
"=IF(RC[-12]="""","""",SUM('Rnd [FirstRound]:Rnd
[RoundsFlown]'!RC[5]))"

So really the question is what is the proper format for VB to recognize a
variable used in a formula inserted into a cell?
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
SUM 3D Reference using variable sheet limits Loge Excel Worksheet Functions 6 December 31st 08 08:14 AM
How To make a sheet reference Variable (eq: sum(sheet!D2:H2)) John Linker Excel Discussion (Misc queries) 3 June 16th 08 11:29 PM
Copy rows to new sheet based on variable cell reference michaelberrier Excel Programming 1 December 26th 06 04:21 PM
Cell reference - for the sheet name, can I use a variable? Matt Lawson Excel Discussion (Misc queries) 4 December 13th 04 02:31 PM
make string variable reference VBA sheet name Tom Deiley Excel Programming 5 May 3rd 04 10:45 PM


All times are GMT +1. The time now is 10:59 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"