Change part of formula by click.

[Phil Payne]

Hello All,

I have a formula and I want to change a part of that formula at the click of a 'button'!
For discussion say the formula is - =COUNTIFS(GPR_Data!$B:$B,"G",GPR_Data!Y:Y,">0")

The part I wish to change is “GPR_Data!Y:Y”.
On click I wish that part to change to “GPR_Data!Z:Z” and the next click would give “GPR_Data!AA:AA” and so on!

Firstly is this possible and secondly, if so, can anyone guide me to a solution?

Thanks for reading this.

 

[Peter_SSs]

When posting formulas in the forum, best to use the formula tags rather than underlining like that:

=COUNTIFS(GPR_Data!$B:$B,"G",GPR_Data!Y:Y,">0")

• What sheet is the formula on?
• What cell is the formula in?

 

[Anthony47]

With the given example you might try the following macro:

Sub ModiForm()
Dim tCell As Range, mySplit
Dim cForm As String, hShift As Long
'
Set tCell = Sheets("Sheet2").Range("F2")    '<<< the Cell
hShift = 1                                  '<<< horiz shift
'
cForm = tCell.Formula                       'The current formula
mySplit = Split(cForm, ",", , vbTextCompare)
'Modify the desired portion:
mySplit(2) = Replace(mySplit(2), Range(mySplit(2)).Address(0, 0), Range(mySplit(2)).Offset(0, hShift).Address(0, 0), , , vbTextCompare)
'set the new formula:
tCell.Formula = Application.WorksheetFunction.TextJoin(",", False, mySplit)
End Sub

The approach I used:
-parse the formula in its portions
-determine wich portion need to be modified (in this case it is element 3, i.e. mySplit(2), given that mySplit is a "zero based" array)
-modify the portion
-apply the new formula

Associate this macro to the button

 

[Phil Payne]

Hello Anthony,

Brilliant - this worked. I only needed to change sheet and range (obviously).

Thankyou for this solution and the lesson.

 

[Anthony47]

Thank you for the feedback

 

[Phil Payne]

Hello Anthony,

Hopefully this is not against Forum rules but I believe you would have an immediate answer to this!

I have been trying to understand how the parsing works in your "Modiform" code above so I can apply it to a different formula, without success.

How should I modify the "Modiform" code to do the same with this formula : =INDEX(Calculation_Sheet!W:W,6) i.e. change W:W to X:X

 

[Anthony47]

A possible solution:

Sub ModiForm2()
Dim tCell As Range, mySplit
Dim cForm As String, hShift As Long
'
Set tCell = Sheets("Sheet3").Range("F2")    '<<< the Cell
hShift = 1                                  '<<< horiz shift
'
cForm = tCell.Formula                       'The current formula
mySplit = Split(cForm, ",", , vbTextCompare)                        '=INDEX(Calculation_Sheet!W:W / 6)
my2Split = Split(mySplit(0), "(", , vbTextCompare)                  '=INDEX / Calculation_Sheet!W:W
'Modify the desired portion:
mySplit(0) = Replace(mySplit(0), Range(my2Split(1)).Address(0, 0), Range(my2Split(1)).Offset(0, hShift).Address(0, 0), , , vbTextCompare)
'set the new formula:
tCell.Formula = Application.WorksheetFunction.TextJoin(",", False, mySplit)
End Sub

You’ll see I used a double splitting to pinpoint the portion to modify and still using the approach I used in the first example.

However I suggest you evaluate a different approach to deal with your problem, i.e. don’t modify the formula but use a more complex one.
For example, instead than starting from INDEX(Calculation_Sheet!W:W,6), use

=INDEX(Calculation_Sheet!W:AH,6,1+F1))

Now, if F1=0, that will point to column W, if 1 to column X, and so on; if you need to span for more that 11 column then you need to expand the portion W:AH
If you wish, you may link F1 to a scoll bar whose min=0 and max=... (depending on the area the formula is prepared to deal with)

If you need you may display in G1 wich column is selected by using the formula

=SUBSTITUTE(ADDRESS(1,23+F1,4),1,"")

Or, if the column has a header, display the header by using the formula

=INDIRECT(ADDRESS(1,23+F1,4,,"Calculation_Sheet"))

This Index approach is also suitable for your original formula: if you use

=COUNTIFS(GPR_Data!$B:$B,"G",INDEX(GPR_Data!Y:AH,0,F1+1),">0")

then F1=0 will point to Y:Y, 1 will point to Z:Z, and so on

 

[Phil Payne]

Hello Anthony,

Brilliant.

Thank you for the solution and suggestions.

I have everything working the way I want right now thanks to you.

Best regards.