CODEWITHSUNDEEP
vbaexcel
Samir112
Samir112

Reputation: 135

Excel VBA running a function on every cell in a row with a dynamic range

I would like to run a function called ToNum on every cell in a row with a value that will convert a cell contents to a string. Here is my code below but I am getting a compile error, can anyone assist and let me know what I am doing wrong?

Sub ConvRows()
    Dim rng As Range
    Dim cell As Variant

    Set rng = Range("A8:A" & Range("A" & Rows.Count).End(xlUp).Row).Select

    For Each cell In rng
        cell.Value = ToNum(cell.Value)
    Next

End Sub
Function ToNum(X As Variant) As String
    Dim A As String
    A = Trim(Str(X))
    ToNum = A
End Function

Upvotes: 0

Views: 1334

Answers (2)

Ben Lindsay
Ben Lindsay

Reputation: 1796

I would add this as a comment, but I don't have enough reputation points to comment. To avoid the type mismatch error on cells that already contain a string, you could put your call to ToNum in an if statement in your subroutine like this:

If IsNumeric(cell.Value) Then
  cell.Value = ToNum(cell.Value)
End If

That should keep the subroutine from touching cells that are already strings.

Upvotes: 2

Gary's Student
Gary's Student

Reputation: 96791

Consider:

Function ToNum(X As Variant) As String
    Dim A As String
    A = Trim(Str(X))
    ToNum = "'" & A
End Function

Upvotes: 2

Related Questions