Calculate a Range

charlie79 · Mar 19th, 2002, 09:50 AM

I want to write vba code to calculate a specific range of cells instead of the whole sheet. What I have done so far is selected the range in xl, and in the name box given that range a name. I then am trying to reference that range name in my code. But it doesn't seem to be calculating the whole range? Should I be creating a range object in my code and using it to reference the cells. Or maybe it's a problem with my code:
Range("SimAvg").Calculate
"SimAvg" being the range name I gave the cells in xl. I'm using xl2k.

smozgur · Mar 19th, 2002, 10:04 AM

Are you sure you set Calculation to Manual from Tools_Options_Calculation? (assume you dont want auto calculation according to your explanations)

charlie79 · Mar 19th, 2002, 10:49 AM

Yes, I actually have it in my code to manual calc. This spreadsheet contains a lot of formulas and makes a lot of calls to another app server, which would cause calcs everytime a call is made... ?

NateO · Mar 19th, 2002, 10:52 AM

Hows about:

Range("A1:z40000").Calculate

charlie79 · Mar 19th, 2002, 11:28 AM

I realize I could hard code it that way, but I like to stay away from that. My question I guess is, can I reference a Cell Range I created in xl or do I have to create a Range object in my code?

NateO · Mar 19th, 2002, 11:33 AM

Maybe you need to reference the worksheet:

Worksheets(Range("simavg").Worksheet.Name).Range("SimAvg").Calculate

Does

Worksheets(Range("simavg").Worksheet.Name).Range("SimAvg").select

Take you to the appropriate range?

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-03-19 10:39 ]

charlie79 · Mar 19th, 2002, 11:35 AM

thanks Nate0
|

Mar 19th, 2002, 10:52 AM	#4
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	Hows about: Range("A1:z40000").Calculate __________________ Regards, Nate Oliver Microsoft Excel MVP Nate's Excel Blog

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 19th, 2002, 09:50 AM	#1
charlie79 Board Regular Join Date: Feb 2002 Posts: 97	I want to write vba code to calculate a specific range of cells instead of the whole sheet. What I have done so far is selected the range in xl, and in the name box given that range a name. I then am trying to reference that range name in my code. But it doesn't seem to be calculating the whole range? Should I be creating a range object in my code and using it to reference the cells. Or maybe it's a problem with my code: Range("SimAvg").Calculate "SimAvg" being the range name I gave the cells in xl. I'm using xl2k.

Mar 19th, 2002, 10:04 AM	#2
smozgur BatCoder Join Date: Feb 2002 Location: Turkey Posts: 764	Are you sure you set Calculation to Manual from Tools_Options_Calculation? (assume you dont want auto calculation according to your explanations)

Mar 19th, 2002, 10:49 AM	#3
charlie79 Board Regular Join Date: Feb 2002 Posts: 97	Yes, I actually have it in my code to manual calc. This spreadsheet contains a lot of formulas and makes a lot of calls to another app server, which would cause calcs everytime a call is made... ?

Mar 19th, 2002, 11:28 AM	#5
charlie79 Board Regular Join Date: Feb 2002 Posts: 97	I realize I could hard code it that way, but I like to stay away from that. My question I guess is, can I reference a Cell Range I created in xl or do I have to create a Range object in my code?

Mar 19th, 2002, 11:33 AM	#6
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	Maybe you need to reference the worksheet: Worksheets(Range("simavg").Worksheet.Name).Range("SimAvg").Calculate Does Worksheets(Range("simavg").Worksheet.Name).Range("SimAvg").select Take you to the appropriate range? _________________ Cheers, NateO [ This Message was edited by: NateO on 2002-03-19 10:39 ]

Mar 19th, 2002, 11:35 AM	#7
charlie79 Board Regular Join Date: Feb 2002 Posts: 97	thanks Nate0 \|