Macro to change row number within formula
 

Greetings,

Currently I am trying to create a module that would allow users to
specify the range of rows for a chart that uses the results of a
formula.
When using the module the user types numbers into two textboxes the 1.
starting row in sheet1 and 2. the ending row in sheet1.
These numbers should then be implemented in the formula in sheet two to
calculate the sum between the specified rows.
The formula is as follows:

=SUM(Database!E1134:E1207)

where E1134 should be changed to the first entry in the modules textbox
(i.e. E2054) and E1207 to the second entry.

I tried to use something like:

worksheets("sheet2").Range("M25").Formula =
"=sum(database!E"&x&":E"&y&")"

where x and y represent the textbox values. VBA unfortunately does not
understand this command. Any suggestions?

Marco.


--
mpot4
------------------------------------------------------------------------
mpot4's Profile: http://www.excelforum.com/member.php...o&userid=27418
View this thread: http://www.excelforum.com/showthread...hreadid=469343

Macro to change row number within formula
 

You need to add some spaces before the & so that VBA knows you are not
using it as a type declaration character. So:

Worksheets("sheet2").Range("M25").Formula = _
"=sum(database!E" & x & ":E" & y & ")"

should work.

Hope this helps
Rowan

mpot4 wrote:
Greetings,

Currently I am trying to create a module that would allow users to
specify the range of rows for a chart that uses the results of a
formula.
When using the module the user types numbers into two textboxes the 1.
starting row in sheet1 and 2. the ending row in sheet1.
These numbers should then be implemented in the formula in sheet two to
calculate the sum between the specified rows.
The formula is as follows:

=SUM(Database!E1134:E1207)

where E1134 should be changed to the first entry in the modules textbox
(i.e. E2054) and E1207 to the second entry.

I tried to use something like:

worksheets("sheet2").Range("M25").Formula =
"=sum(database!E"&x&":E"&y&")"

where x and y represent the textbox values. VBA unfortunately does not
understand this command. Any suggestions?

Marco.

Macro to change row number within formula
 

You may have a "Type" conflict:
The Textbox is yielding TEXT wheras
your formula wants a NUMBER for x and y

try changing this and see if it works

"mpot4" wrote:


Greetings,

Currently I am trying to create a module that would allow users to
specify the range of rows for a chart that uses the results of a
formula.
When using the module the user types numbers into two textboxes the 1.
starting row in sheet1 and 2. the ending row in sheet1.
These numbers should then be implemented in the formula in sheet two to
calculate the sum between the specified rows.
The formula is as follows:

=SUM(Database!E1134:E1207)

where E1134 should be changed to the first entry in the modules textbox
(i.e. E2054) and E1207 to the second entry.

I tried to use something like:

worksheets("sheet2").Range("M25").Formula =
"=sum(database!E"&x&":E"&y&")"

where x and y represent the textbox values. VBA unfortunately does not
understand this command. Any suggestions?

Marco.


--
mpot4
------------------------------------------------------------------------
mpot4's Profile: http://www.excelforum.com/member.php...o&userid=27418
View this thread: http://www.excelforum.com/showthread...hreadid=469343