Changing Characters within Formula

embi_

New Member
Joined
Jul 4, 2019
Messages
6
Hey all,

I have a sheet where I insert data each day and I want to generate a monthly overview. The Formula in the cell says:

=IF(COUNTIF(Sheet11!$CKO13:$CTT13;E$2)=0;"";COUNTIF(Sheet11!$CKO13:$CTT13;E$2))

I want to change the characters in red color according to a certain month to later choose from a dropdown menu.
The only thing I could manage is to change the characters and switch through each month with:

Private Sub CommandButton15_Click()
Sheets("Sheet11").Range("E5:BF103").Select
Selection.Replace What:="CKO", Replacement:="CKT", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

But here I cannot jump from Jan to Aug. Is there a function i can use to change the **th character of a formula in a cell with formatting the FormulaText as String, use the Replace Function and then insert it back again with the changed characters?

Thanks very much for the help!

Greetings
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Maybe something like that? I'm assuming that the formula you want to modify is in Sheet1, cell A1. We're leaving the first 29 character unaffected and we apply the change (replace) starting from the 30th position.

Code:
Sub ConvertFormula()
    Dim strFormula As String
    
    With Sheet1.Range("F4")
        strFormula = .Formula
        .Formula = Left(strFormula, 29) & Replace(strFormula, "CKO", "CKT", 30)
    End With
End Sub
 
Upvote 0
Thank you very much for your reply!

The Sub works fine, but I think it works like mine above, that I always have to know what characters are from the 26th-28th. position.

I am looking for a Sub that says change character 26 until 28, no matter what characters the formula contained before. Maybe i did not get it, but I think with your code you have to know what characters the formula contains in order to change them.

Thanks
 
Upvote 0
Try using Indirect function in the formula and you can replace the characters either from a dropdown or any other defined location.

Regards,

Sanjeev
 
Upvote 0
I am looking for a Sub that says change character 26 until 28, no matter what characters the formula contained before.
Would something like this be what you are after?

Code:
Private Sub CommandButton15_Click()
  Const sNew As String = "CKT"
  
  With Sheets("Sheet11").Range("E5:BF103")
    .Replace What:=Mid(.Cells(1).Formula, 26, 3), Replacement:=sNew, LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
  End With
End Sub
 
Upvote 0
@Peter_SSs

Yes that looks very promising! I inserted that into my code and it changed the 3 characters in the Formula in each cell.

Thank you for that!

If I want to do change 2 different areas (Character 19-21 and 26-28 can I just copy the code, define a new String "sNew2" and change the counting numbers?

Code:
Private Sub CommandButton15_Click()
  Const sNew1 As String = "CAZ"
  
  With Sheets("Sheet11").Range("E5:BF103")
    .Replace What:=Mid(.Cells(1).Formula, 26, 3), Replacement:=sNew1, LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
  End With
  
    Const sNew2 As String = "BRU"
    With Sheets("Sheet11").Range("E5:BF103")
    .Replace What:=Mid(.Cells(1).Formula, 19, 3), Replacement:=sNew2, LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
  End With


End Sub

If I run it, only the second part of the code is applied and the characters 26-28 do not change. Where is the mistake in this code?
 
Upvote 0
I don't have your sheet/formulas to test with but that code looks okay to me, though you don't have to repeat all of it. This works for my test sheet.

Code:
Private Sub CommandButton15_Click()
  Const sNew1 As String = "CAZ"
  Const sNew2 As String = "BRU"
  
  With Sheets("Sheet11").Range("E5:BF103")
    .Replace What:=Mid(.Cells(1).Formula, 26, 3), Replacement:=sNew1, LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    .Replace What:=Mid(.Cells(1).Formula, 19, 3), Replacement:=sNew2, LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
  End With
End Sub
 
Upvote 0
I created a new document with the same tables and there it works! I can now inlclude it in my dropdown list!
Thank you very much for your help!

I cannot explain why it it not working in the original sheet, but then I will just transfer the data in the new sheet.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top