How to insert a specific value at the beginning of text in a Column using Excel VBA?

Question

I'm trying to insert this character "-" at the beginning of each text in column F.

My F column looks like this:

BP850
BP851
BT100
GP160
GP161

I tried this code:

Option Explicit

Sub test()

    Dim LastRow As Long, i As Long
    Dim str As String

'Change sheet if needed
    With ThisWorkbook.Worksheets("Sheet1")
        'Find the last row of column F
        LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row

'Loop column F
        For i = 1 To LastRow
            'Replace the first occurance
            str = Replace(.Range("F" & i).Value, "", "-", 1, 1)
            .Range("F" & i).Value = str
        Next i

    End With

End Sub

I expect:

-BP850
-BP851
-BT100
-GP160
-GP161

Answer 1

As an alternative, you could try to utilize .Evaluate. This prevents the need for any loop:

Option Explicit

Sub test()

    Dim LastRow As Long
    Dim rng As Range

'Change sheet if needed
    With ThisWorkbook.Worksheets("Sheet1")
        'Find the last row of column F
        LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
        Set rng = .Range("F1:F" & LastRow)
        rng.Value = .Evaluate("""'-""&" & rng.Address)
    End With

End Sub