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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

JustynaMK

Well-known Member
Joined
Aug 28, 2016
Messages
647
Office Version
365, 2013
Platform
Windows
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
 

embi_

New Member
Joined
Jul 4, 2019
Messages
6
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
 

Sanjeev1976

Active Member
Joined
Dec 25, 2008
Messages
252
Try using Indirect function in the formula and you can replace the characters either from a dropdown or any other defined location.

Regards,

Sanjeev
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,221
Office Version
365
Platform
Windows
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
 

embi_

New Member
Joined
Jul 4, 2019
Messages
6
@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?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,221
Office Version
365
Platform
Windows
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
 

embi_

New Member
Joined
Jul 4, 2019
Messages
6
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,508
Messages
5,487,281
Members
407,590
Latest member
Grobler

This Week's Hot Topics

Top