Inserting a string into a cell with a repeatable macro

[Moonbeam111]

Hello. I have a cell "H27" and in that cell I would like to start off with a string of text using VBA. If that were all it would be easy but that particular cell has a formula in it through a macro that adds +1 repeatably. Here is what I have:

Sheets("Sheet1").Range("H27").Value = "Budget is equal to" & " " & Sheets("Sheet1").Range("H27").Value + 1

But I get a type mismatch when I run the code and it gets past 1. What am I doing wrong?

 

[Joe4]

So, the first time, you probably have just a number in there.
But then the second time, you have text like "Budget is equal to 2". So you cannot add 1 directly to that, as it is a string and not a number.
You need to split off the number and add one to that.

Try this:

Sub MyMacro()

    Dim arr() As String
    Dim i As Long
    
    arr() = Split(Sheets("Sheet1").Range("H27"), " ")
    i = UBound(arr)
    
    Sheets("Sheet1").Range("H27").Value = "Budget is equal to " & arr(i) + 1
    
End Sub

 

[Anthony47]

Another option could be formatting cell H27 with this custom format: "Budget is equal to " #.##0,00;"Budget is equal to "-###0,00:
-rightclick on the cell, select Format cell; tab Number, Category=Custom, insert the give string in the Type box and confirm with Ok

At this point the vba command would be:

Sheets("Sheet1").Range("H27").Value = Sheets("Sheet1").Range("H27").Value + 1

For Custom formatting, see Number format codes - Microsoft Support

 

[Moonbeam111]

Thanks Anthony47. That works for my purposes. Joe4's code worked as well when I had a value already in cell H27 but that cell starts off empty.

 

[Joe4]

Thanks Anthony47. That works for my purposes. Joe4's code worked as well when I had a value already in cell H27 but that cell starts off empty.

OK, I thought that you said that you were starting off with something in that cell, but if it may start out being blank, a simple edit to the code cn handle that:

Sub MyMacro()

    Dim arr() As String
    Dim i As Long
    
    If Sheets("Sheet1").Range("H27") = "" Then
        Sheets("Sheet1").Range("H27") = "Budget is equal to 1"
    Else
        arr() = Split(Sheets("Sheet1").Range("H27"), " ")
        i = UBound(arr)
        Sheets("Sheet1").Range("H27").Value = "Budget is equal to " & arr(i) + 1
    End If
    
End Sub