CODEWITHSUNDEEP
excelvba
Kris Van den Bergh
Kris Van den Bergh

Reputation: 1152

Replace integer value excel sheet

I'm tyring to replace integer 7,6 in an excel by 8. However, following code is not really working as expected. It seems to subsitute strings. I have written a similar piece of code using the replace functionality but even that didn't produce the results I'm expecting. What am I doing wrong?

Sub MacroTest()
'
Dim rng As Range, cell As Range
Set rng = Sheets("Naomi").Range("H1:H10000")

For Each cell In rng
    If cell > 0 Then
    cell = WorksheetFunction.Substitute(cell, "7,6", "8")
    End If
Next

End Sub

Thanks for guiding me.

Upvotes: 0

Views: 1019

Answers (2)

L42
L42

Reputation: 19727

I guess Taosique provided the best way to do it.
This answers why it returns String instead of Number.
You already figure it out that Substitute returns a string when you try it inside WS.
Try using Val Function then to convert the value to a Number as in below.

With Application.WorksheetFunction
    For Each cell in rng
        If cell > 0 Then
            cell = Val(.Substitute(cell, 7.6, 8))
        End If
    Next
End With

Or you can use Evaluate as well like this:

If cell > 0 Then
    cell = Evaluate("Value(Substitute(" & cell & ",7.6,8))")
End If

No need to enclose 7.6 with "".
Substitute accepts numbers as arguments.

Upvotes: 1

ttaaoossuu
ttaaoossuu

Reputation: 7884

Use this:

If cell.Value = 7.6 Then
    cell.Value = 8
End If

Upvotes: 0

Related Questions