Returning number of rows in table and adding to formula

[Jules]

Hi,

I am setting up a macro that allows users to add information into an
unspecified number of rows in a table. The user adds info and then is asked
if they wish to add more and this loops (addin a new row to the table) until
they no longer wish to add info.
One of the columns in the table is a figure and I want to have it
automatically total at the end. As I do not know how many rows there will be
I am unable to specify the formula. I have been able to return the number of
rows and have defined this as "NumRows", however cannot use this in the
formula - I have tried Sum(B1:BNumRows) but this does not work. Does anyone
know how I can get the result of NumRows into my formula. FYI, I cannot use
Sum(Above) as some cells may not contain any information and this would then
not work.

thanks,

 

[Cindy M -WordMVP-]

Hi Jules,

One of the columns in the table is a figure and I want to have it
automatically total at the end. As I do not know how many rows there will be
I am unable to specify the formula. I have been able to return the number of
rows and have defined this as "NumRows", however cannot use this in the
formula - I have tried Sum(B1:BNumRows) but this does not work.

The formula is in string form when you enter it using VBA code, so what you need
to do is concatenate the variable's content with the string:

Dim formula as string
Dim NumRows as Long

'Get the NumRows information here
'Now put it together
formula = "Sum(B1:B" & Trim(CStr(NumRows)) & ")"

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[Jules]

thanks very much, perfect.