Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to automatically change one number to another number | Excel Discussion (Misc queries) | |||
Formula to change scientific number to regular number or text | Excel Discussion (Misc queries) | |||
Macro to change a number to text | Excel Programming | |||
Macro to change a number to text | Excel Programming | |||
Macro to change a number to text | Excel Programming |