changing (and understand) the inside of a formula with a macro

[excelNewbie22]

hi,
i asked the same here:
macro which can change the inside of a formula
but when i tried to apply it in different cells, with try and error,
i managed partially

i need to subtract 1 from:

=COUNTIF($BY$684608:$BY$784608,CU684694)

to

=COUNTIF($BY$684608:$BY$784608,CU684693)

with the macro peter helped me in the mention thread

Sub Change_Formula()
  Dim Bits As Variant
 
  With Range("BU684608")
    Bits = Split(.Formula, ":")
    .Formula2 = Left(Bits(0), 2) & Mid(Bits(0), 3) - 1 & ":" & Bits(1)
  End With
End Sub

and the same to the neighboring cells
at: BV684608
from: =COUNTIF($CF$684608:$CF$734608,CU684694)
to: =COUNTIF($CF$684608:$CF$734608,CU684693)
at: BW684608
from: =SUM(COUNTIF($BZ$684608:$CE$784608,CV684694:DA684694))
to: =SUM(COUNTIF($BZ$684608:$CE$784608,CV684693:DA684693))
and last one:
at: BX684608
from: =SUM(COUNTIF($CG$684608:$CL$734608,CV684694:DA684694))
to: =SUM(COUNTIF($CG$684608:$CL$734608,CV684693:DA684693))

it would be great helped if someone can also explain this bit here:

Formula2 = Left(Bits(0), 2) & Mid(Bits(0), 3) - 1 & ":" & Bits(1)

so i can, if needed in the future, edit it to my needs,

also, can it be all in one macro?

 

[myall_blues]

Your situation is quite different here than the original. In @Peter_SSs original code, you were changing the formula =a90:$g$90. To break down the original code Peter supplied:
Bits = Split(.Formula, ":") gives an array with two elements, Bits(0) and Bits(1). The Split command broke the formula into two halves at the ":", so
Bits(0) now contains =a90
Bits(1) contains $g$90
Then it gets split up and recombined, so
=Left(Bits(0),2) gives the first two characters of Bits(0) =a
Mid(Bits(0),3)-1 takes the remainder of Bits(0) from the third character onward and subtracts 1, so 90-1 or 89
Together with ":" and Bits(1) and put all back together you get =a89:$g$90

Now your formula is =COUNTIF($BY$684608:$BY$784608,CU684694) and this code is what's needed:

Sub Change_Formula()
  Dim Bits As Variant
 
  With Range("BU684608")
    Bits = Split(.Formula, ",")
    .Formula2 = Bits(0) & "," & Left(Bits(1), 2) & Mid(Bits(1), 3, 6) - 1 & ")"
  End With
End Sub

This time you want to change the final number, so split at the comma so that
Bits(0) contains =COUNTIF($BY$684608:$BY$784608
Bits(1) contains CU684694)
Left(Bits(1),2)contains "CU"
Mid(Bits(1),3,6)contains 684694, subtract 1 gives 684693
Then recombine again
=COUNTIF($BY$684608:$BY$784608,CU684693)

The code for the formula in BV will be the same.
The code for the formulas in BW and BX will be a little different, but have a go and if you can't work it out come back.

Finally, yes it can all be in one macro. If you don't have too many the easiest is probably just to keep adding With...End With constructs.
e.g.

With Range("BU684608")
     ' do things
End With
With Range("BV684608")
    ' do things
End With

and so on.

 

[excelNewbie22]

thanks blues,
well explained,
but still, try several assumptions how to do
at: BW684608

=SUM(COUNTIF($BZ$684608:$CE$784608,CV684694:DA684694))

like:

Sub Change_Formula()
  Dim Bits As Variant
 
  With Range("BW684608")
    Bits = Split(.Formula, ",")
    .Formula2 = Bits(0) & "," & Left(Bits(1), 2) & Mid(Bits(1), 3, 6) - 1 & Left(Bits(1), 9, 11) & Mid(Bits(1), 12, 17) - 1 & ")"

  End With
End Sub

but it gave me compile error
what am i doing wrong?

 

[myall_blues]

Left and Mid (and Right) are string handling functions.
Left starts from the beginning of a string and returns the number of characters provided in the second argument. In your case above you've provided a third argument as well (11), which gives a compile error. You would do well to consult the VBA language reference.

In the case of the formula in BW, it's probably easiest to split Bits(1) again at the colon and then manipulate the new sub-array.

Sub Change_Formula()
  Dim Bits As Variant
  Dim Bits2 As Variant
 
  With Range("BW684608")
    Bits = Split(.Formula, ",")
    Bits2 = Split(Bits(1), ":")
    .Formula2 = Bits(0) & "," & Left(Bits2(0), 2) & Mid(Bits2(0), 3, 6) - 1 & ":" & Left(Bits2(1), 2) & Mid(Bits2(1), 3, 6) - 1 & "))"

  End With
End Sub

 

[excelNewbie22]

thanks blues, works well,
although i didn't catch all, i learned a bit
thank you!

 

[myall_blues]

You’re welcome. We’re here to help.