CODEWITHSUNDEEP
excelexcel-2010vba
yinka
yinka

Reputation: 134

VBA to convert texts to numbers except formula and non-numeric texts

I have a Range("B6:T10000")

Data in the range are a mixture of blanks,#'s ,numbers (formatted as texts), texts and most importantly formulas.

Can someone please help with a VBA macro to:

Thank you very much

Upvotes: 2

Views: 6670

Answers (4)

brettdj
brettdj

Reputation: 55692

You can do this without code, or with quicker code avoiding loops

Manual

  1. Copy a blank cell
  2. Select your range B6:T100001
  3. Press F5. Then Goto ... Special
  4. check Constants and then Text
  5. Paste Special Multiply and check Add

This converts text only cells with numbers into numbers, and leaves actual text or formulae alone

Code

Sub Update()
Dim rng1 As Range
On Error Resume Next
Set rng1 = Range("B6:T10000").SpecialCells(xlCellTypeConstants, 2)
On Error Resume Next
If rng1 Is Nothing Then Exit Sub
'presumes last cell in sheet is blank
Cells(Rows.Count, Columns.Count).Copy
rng1.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
End Sub

Upvotes: 5

ghasem76
ghasem76

Reputation: 9

ActiveSheet.Range("b5:b6004,h5:h6004").Select    
For Each xCell In Selection    
If IsNumeric(xCell) = False Then    
    xCell.Value = Val(xCell.Value)    
Else    
End If    
Next xCell

Upvotes: 0

L42
L42

Reputation: 19737

here's my version:

Sub Test()

Dim rng as Range, cel as Range

Set rng = Thisworkbook.Sheets("Sheet1").Range("B6:T10000")

For Each cel In rng
    If Not IsError(cel.Value) Then _
        If Len(cel.Value) <> 0 And cel.HasFormula = False And _
            IsNumeric(cel.Value) Then cel.Value = Val(cel.Value)
Next cel

End Sub

I've tested it, and works fine.
Hope this helps.

Upvotes: 3

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96773

Give this a try:

Sub Converter()
    Dim rBig As Range, r As Range, v As Variant
    Set rBig = Range("B6:T10000")
    For Each r In rBig
        v = r.Value
        If v <> "" And r.HasFormula = False Then
            If IsNumeric(v) Then
                r.Clear
                r.Value = v
            End If
        End If
    Next r
End Sub

EDIT#1:

This version ignores errors:

Sub Converter()
    Dim rBig As Range, r As Range, v As Variant
    Set rBig = Range("B6:T10000")
    For Each r In rBig
        v = r.Value
        If Not IsError(v) Then
            If v <> "" And r.HasFormula = False Then
                If IsNumeric(v) Then
                    r.Clear
                    r.Value = v
                End If
            End If
        End If
    Next r
End Sub

Upvotes: 2

Related Questions