Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Formula with Macro
I currently am working on a spreadsheet that I am trying to automate
and extremely repetitive task on. Right now I have the following formula in each cell: =DCOUNTA(Query!$A$1:$C$50319,Query!$A$1,Calculatio ns!$DM$3:$DP$4). All I am trying to do is to be able to change the 3 and the 4 to another number using a macro. I have tried the find and replace but since I can only do one number at a time the numbers get screwed up. I would like a macro to prompt me for the two number to change it to and then click change and have it do it. I don't want anything else in the formula changed because each row has a different formula but they all have the same layout. Any help I can get is much appreciated. Chris Morton |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Formula with Macro
Sub ChangeFormula(MyRange as Range)
Dim ThisCell as Range, R1 as String, R2 as String R1 = Msgbox("Enter the first row:") R2 = MsgBox("Enter the final row:") For Each ThisCell in MyRange.Cells With ThisCell .Formula = Left(.Formula,InStr(.Formula,"$DM$")) & "DM$" & R1 & ":$DP$" & R2&")" End With Next ThisCell End Sub -- - K Dales " wrote: I currently am working on a spreadsheet that I am trying to automate and extremely repetitive task on. Right now I have the following formula in each cell: =DCOUNTA(Query!$A$1:$C$50319,Query!$A$1,Calculatio ns!$DM$3:$DP$4). All I am trying to do is to be able to change the 3 and the 4 to another number using a macro. I have tried the find and replace but since I can only do one number at a time the numbers get screwed up. I would like a macro to prompt me for the two number to change it to and then click change and have it do it. I don't want anything else in the formula changed because each row has a different formula but they all have the same layout. Any help I can get is much appreciated. Chris Morton |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Formula with Macro
Hi Chris,
maybe better solution: supposing, that you will store your two numbers in a1 (=3) and b1 (=4), you can re-write the formula to be: =DCOUNTA(Query!$A$1:$C$50319,Query!$A$1,indirect(" Calculations!$DM$"&a1&":$DP$"&b1)) Regards, Ivan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Formula with Macro
I like this code except that it comes up with an error when entering
it. It says Expected: End of Statement. The other problem with this is that the DM and DP do not stay the same for each row. Is there a way to just change the numbers? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Formula with Macro
I like this code except that it comes up with an error when entering
it. It says Expected: End of Statement. The other problem with this is that the DM and DP do not stay the same for each row. Is there a way to just change the numbers? Chris K Dales wrote: Sub ChangeFormula(MyRange as Range) Dim ThisCell as Range, R1 as String, R2 as String R1 = Msgbox("Enter the first row:") R2 = MsgBox("Enter the final row:") For Each ThisCell in MyRange.Cells With ThisCell .Formula = Left(.Formula,InStr(.Formula,"$DM$")) & "DM$" & R1 & ":$DP$" & R2&")" End With Next ThisCell End Sub -- - K Dales " wrote: I currently am working on a spreadsheet that I am trying to automate and extremely repetitive task on. Right now I have the following formula in each cell: =DCOUNTA(Query!$A$1:$C$50319,Query!$A$1,Calculatio ns!$DM$3:$DP$4). All I am trying to do is to be able to change the 3 and the 4 to another number using a macro. I have tried the find and replace but since I can only do one number at a time the numbers get screwed up. I would like a macro to prompt me for the two number to change it to and then click change and have it do it. I don't want anything else in the formula changed because each row has a different formula but they all have the same layout. Any help I can get is much appreciated. Chris Morton |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autorun a macro on change of cell value (having formula) | Excel Worksheet Functions | |||
Macro to change a formula to a value | Excel Worksheet Functions | |||
Macro To Change a Formula to A Value | Excel Worksheet Functions | |||
Macro to change row number within formula | Excel Programming | |||
Change Row Numbers in Formula with Macro | Excel Discussion (Misc queries) |