Use a variable in a formula in VBA

[[email protected]]

Evening, Gurus.

This will be an easy one for you guys, but try as I migh I can't get the syntax right.

I'm simply trying to usea the variable "AmtEnd" to specify the end of the range I am trying to sum. The first value is constant, "O7", but the last row varies, so I dime the variable as a range, and then set the variable, and am trying to use that variable as the end of my range to sum. I am getting a "#VALUE" error in teh cell instead of a sum. It is the last line of this script throwing the error:

Please Login or Register  to view this content.

Thanks in advance for any help you can offer.

[bakerman2]

Try this one.

Please Login or Register  to view this content.

[Richard Buttrey]

Is it vital to put a total at the foot of a column of numbers?

That was standard when we used pencil and paper but ever since spreadsheets were created it's always been easier to hold a total in a fixed cell above the data.
Either create a sum formula that includes as many rows as your data is ever likely to occupy, or create a dynamic range name for your data and use that.

There's also a lot of .Select and Activate stuff which you should try to avoid. It's rarely necessary.

[xlnitwit]

You don’t need to calculate it anyway as it’s just the cell above, so

Please Login or Register  to view this content.

which sums from row 7 of the current column to the cell above the one with the formula.

[[email protected]]

Morning, guys. Sorry for my slow reply. I cut out of the office right after my post yesterday.

xlnitwit - I see what you are saying, but the cell where my formula will be is not always the same. There are varying rows of data that will have to be summed, so that is why I was trying it using a variable.

Richard - Good to hear from you again. It has been awhile. You make a very good point. It would have been easier to select a constant cell address above the data and then simply select a range that would consistently encompass all the rows that would ever be used. I could then just reference that cell to retrieve the sum. Seems like I'm always doing things the hard way. I may still go with your example.

Bakerman2 - You nailed it. In my many attempts at changing the syntax of the bottom line I had tried the version you used, but the problem was that I had tried to dim my variable as a range rather than a string. I still don't completely understand why it is a string, since it was a cell address I was trying to set as a variable, but it seems to work.

Thanks to all of you for you help and expertise. I am always amazed by the helpful people involved in this forum. I will mark this thread as SOLVED. Have a good one.

[xlnitwit]

Is the formula cell not always immediately below the data then?

[bakerman2]

Glad to help. Thanks for rep+.

[[email protected]]

The formula cell is always right below the data, but the number of rows of data is dynamic. I decided to go with Richard's suggestion to put the formula in a static location and reference a static range like "(O7:O50000)". This eliminated the need to use a variable.

Thanks.

[xlnitwit]

If the data starts at row 7, then the formula I suggested will always work. No variable required, no excessive ranges.

[[email protected]]

I got you now. I had already selected the cell below the data. You're right that will work, too. Thanks.