Results 1 to 8 of 8

Thread: Changing Characters within Formula
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Location
    Stockholm
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Changing Characters within Formula

    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

  2. #2
    Board Regular JustynaMK's Avatar
    Join Date
    Aug 2016
    Location
    London, UK
    Posts
    436
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Changing Characters within Formula

    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

  3. #3
    New Member
    Join Date
    Jul 2019
    Location
    Stockholm
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Changing Characters within Formula

    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

  4. #4
    Board Regular Sanjeev1976's Avatar
    Join Date
    Dec 2008
    Location
    Dubai
    Posts
    245
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Changing Characters within Formula

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

    Regards,

    Sanjeev

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,975
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Changing Characters within Formula

    Quote Originally Posted by embi_ View Post
    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
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  6. #6
    New Member
    Join Date
    Jul 2019
    Location
    Stockholm
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Changing Characters within Formula

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

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,975
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Changing Characters within Formula

    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
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  8. #8
    New Member
    Join Date
    Jul 2019
    Location
    Stockholm
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Changing Characters within Formula

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •