Number stored as text

[Katy Jordan]

Hello people,

When the macro is executed, all my values is stored as text, is there a simple code that can convert the numbers stored as text into values, although i can see they are values i cannot use the sum function since the values are stored as text and each cell which has a value is flagged green.

 

[KWMSeattle]

Would you mind posting your code? We can help you add to it to format numbers as numbers.

 

[Katy Jordan]

here is my code

Sub Example()
GetCSV Date, 2, 4
End Sub
 
Sub GetCSV(ForMonth As Date, Column1 As Integer, Column2 As Integer)
Dim ws As Worksheet, myDir As String, fn As String, temp As String, wsName As String
Dim fso As Object, x, y, a() As String, i As Long, n As Long, t As Long
 
Column1 = Column1 - 1
Column2 = Column2 - 1
 
Set fso = CreateObject("Scripting.FileSystemObject")
myDir = "U:\NRFF"
wsName = "Interest-" & MonthName(Month(ForMonth) - 1, False) & ".xls"
On Error Resume Next
Set ws = Workbooks(wsName).Sheets("downloads")
If ws Is Nothing Then _
    Set ws = Workbooks.Open(myDir & "\" & wsName).Sheets("downloads")
On Error GoTo 0
If ws Is Nothing Then
    MsgBox myDir & "\" & wsName & " is not found"
    Exit Sub
End If
t = 1
fn = Dir(myDir & "\*.csv")
Do While fn <> ""
    Set myTxt = fso.OpenTextFile(myDir & "\" & fn)
    temp = myTxt.ReadAll: myTxt.Close
    x = Split(temp, vbCrLf)
    ReDim a(1 To UBound(x) + 1, 1 To 2)
    For i = 0 To UBound(x)
        y = Split(x(i), ",")
        If UBound(y) > 0 Then
            n = n + 1: a(n, 1) = y(Column1): a(n, 2) = Val(y(Column2))
        End If
    Next
    With ws.Cells(1, t)
        .Value = fn
        .Offset(1).Resize(n, 2).Value = a
    End With
    t = t + 2: n = 0
    fn = Dir
Loop
Set fso = Nothing: Set ws = Nothing
End Sub

Would you mind posting your code? We can help you add to it to format numbers as numbers.