VBA add $ to text numbers in cell-CodePudding

Looking for some help please.

I am trying to add the "$" to column C in a sheet with contains numbers as text n VBA. . I know I can just format it into currency but it need to be in text format. Don't Ask long story.

CodePudding user response：

Use a helper column, then

="$"&C1

Copy down or drag down, then copy paste.special values.

If you need them back in col c the move them from the helper column.

CodePudding user response：

You can call this Sub:

Public Sub AddDollarToColumnC()
    Dim rThisCell As Range, rThisRange As Range
    
    Set rThisRange = ActiveSheet.Range("C:C")
    Set rThisRange = rThisRange.Resize(rThisRange.Cells(1, 1).End(xlDown).Row, 1)
    For Each rThisCell In rThisRange.Cells
        rThisCell = "$" & rThisCell
    Next rThisCell
    Set rThisRange = Nothing
    Set rThisCell = Nothing
End Sub

To select the non-blank cells in column C of the activesheet, starting from C1, and concatenates a dollar sign in front of each, up to (but excluding) the first blank cell.

You might like to add a check if the cell really is a number in a text formatted cell, check if the first cell has a column header etc.