Changing Characters within Formula

[embi_]

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

 

[JustynaMK]

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.

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_]

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]

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]

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?

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_]

@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?

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]

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.

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_]

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.