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

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

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

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?

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