Convert string to number with minimal cost 2

[snuv]

I am populating a worksheet from a database and need to convert the numeric strings to numbers so I can use them in graphs but skip over anyother strings such as "N/A"

I'm using

Private Sub ConvertData(ByVal startRow As Integer,ByVal startCol As Integer,ByVal endRow As Integer,ByVal endCol As Integer)

    Dim i As Integer
    Dim j As Integer

    For i = startCol To endCol

        For j = startRow To endRow

            Dim x As String
            x = Cells(j, i).Value

            Dim nVal As Double

            If IsNumeric(x) Then
                Cells(j, i).Value = Val(x)
            End If

        Next
    Next

End Sub

I need it to convert strings like "12.37 %" but as the grid could be huge eg 255 cols by 1000 rows and the sheet may be updated frequently, I dont want to use slow string manipulation.

does anyone have any suggestions?

Cheers
Snuv





"If it could have gone wrong earlier and it didn't, it ultimately would have been beneficial for it to have." : Murphy's Ultimate Corollary

 

[Bong]

Maybe you could use On Error GoTo line . In each cell, you can multiply by 1: cells(j,i)=1*cells(j,i).
Then, on error, you can either resume or skip, or whatever you want.

_________________
Bob Rashkin

 

[SkipVought]

Hi,

You could use the PasteSpecial - MULTIPLY method. If you take the product of a numeric string and 1, Excel coerces the result as a number.

Turn on your macro recorder, copy a cell containing 1, select the target area and Edit/PasteSpecial - Multiply.

Skip,

_[red][/red]

 

[snuv]

Thanks

Either of your solutions should fix the problem

Cheers
Snuv

"If it could have gone wrong earlier and it didn't, it ultimately would have been beneficial for it to have." : Murphy's Ultimate Corollary