How to turn text into a number using VBA?

[marlonsaveri]

This program runs through the worksheets in search of some values. To find them, it puts on a table to build a chart.
The following macro pastes a value in the table in the correct region.

The sub receives a sheet (which contains the table), a value that will be inserted in it and receives an index i refers to the worksheet where that value was taken.

The problem is:
Not always the number (variable what) is a number. Sometimes it can be a number between brackets or two numbers separated by hyphens.

If two numbers separated by hyphens, I use "Split" and print on two lines.
If a number in brackets, I remove them by "replace", but there is a problem: the number is saved as text, so the program can not build the chart.
How to turn text into a number using VBA? Or how to solve this problem?

Sub PasteOnTable(mysheet As Worksheet, what As String, i As Integer)
    Dim LastRow As Integer
    LastRow = Cells(Rows.count, 1).End(xlUp).Row 
    LastRow = LastRow + 1 
    
    If IsNumeric(what) Then 
        mysheet.Cells(LastRow, 1) = Sheets(i).Name 
        mysheet.Cells(LastRow, 2) = what
    Else
        correct = Replace(what, "[", "") 
        correct = Replace(what, "]", "")
        If IsNumeric(correct) Then 
            mysheet.Cells(LastRow, 1) = Sheets(i).Name 
            mysheet.Cells(LastRow, 2).FormulaR1C1 = correct
        Else 
            vet = Split(what, "-")
            mysheet.Cells(LastRow, 1) = Sheets(i).Name 
            mysheet.Cells(LastRow, 2) = vet(0) 
            mysheet.Cells(LastRow + 1, 1) = Sheets(i).Name 
            mysheet.Cells(LastRow + 1, 2) = vet(1) 
        End If
    End If
    
    mysheet.Range("B2", Cells(LastRow, 2)).NumberFormat = "0.00" 'it doesn't solve.
End Sub

 

[VoG]

Try

Sub PasteOnTable(mysheet As Worksheet, what As String, i As Integer)
    Dim LastRow As Integer
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    LastRow = LastRow + 1
    
    If IsNumeric(what) Then
        mysheet.Cells(LastRow, 1) = Sheets(i).Name
        mysheet.Cells(LastRow, 2) = what
    Else
        correct = Replace(what, "[", "")
        correct = Replace(what, "]", "")
        If IsNumeric(correct) Then
            mysheet.Cells(LastRow, 1) = Sheets(i).Name
            mysheet.Cells(LastRow, 2).Value = Val(correct)
        Else
            vet = Split(what, "-")
            mysheet.Cells(LastRow, 1) = Sheets(i).Name
            mysheet.Cells(LastRow, 2) = Val(vet(0))
            mysheet.Cells(LastRow + 1, 1) = Sheets(i).Name
            mysheet.Cells(LastRow + 1, 2) = Val(vet(1))
        End If
    End If
    
    mysheet.Range("B2", Cells(LastRow, 2)).NumberFormat = "0.00" 'it doesn't solve.
End Sub

 

[marlonsaveri]

WOw, it's working now, thanks!!!