Goodafternoon,
I have written a vba code that copies a list from one workbook to another workbook. This all works perfectly.
But the list contains numbers that are written as text values. (The list is big most of the times over 1000 rows), I need the text values converted into numbers.
The numbers(Displayed as text) give an error message:
Number is stored as text
I can choose to convert the text to numbers this is exactly what I need vba to do!
My code:
Note: This works, but there is one problem and another thing I would like to fix.
1. The column J:J contains text values like 1 X 30 or 1 + 1/2 and text like 3. When Vba converts this to general numbers 1 X 30 becomes 1, 1 + 1/2 becomes 1 and 3 becomes 3. But 1 X 30 needs to be 1 X 30 and 1 + 1/2 to be 1 + 1/2 after converting.
When you convert yourself trough the error message it stays the same.. I don't get why this is changing all my numbers.
2. My code takes every cell apart and fills 0 in the empty cells, it would be better if it just leaves the cells empty. (This proces is time consuming)
Thank you very much!
Hope someone have an answer
I have written a vba code that copies a list from one workbook to another workbook. This all works perfectly.
But the list contains numbers that are written as text values. (The list is big most of the times over 1000 rows), I need the text values converted into numbers.
The numbers(Displayed as text) give an error message:
Number is stored as text
I can choose to convert the text to numbers this is exactly what I need vba to do!
My code:
Code:
Private Sub CMD_GetWeight_Click()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim wbk As Workbook
Dim wbk2 As Workbook
Dim strFirstFile As String
Dim strSecondFile As String
strFirstFile = Me.L_ListLocation.Caption
strSecondFile = "test.xls"
Set wbk = Workbooks.Open(strFirstFile)
With wbk.Sheets("Sheet1")
Range("A9:Q65536").Copy
End With
Set wbk2 = Workbooks.Open(strSecondFile)
With wbk2.Sheets("List")
Range("A2:Q65536").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("J:J").Select
For Each cell In Selection
cell.Value = Val(cell.Value)
Next
Selection.NumberFormat = "General"
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = TrueNote: This works, but there is one problem and another thing I would like to fix.
1. The column J:J contains text values like 1 X 30 or 1 + 1/2 and text like 3. When Vba converts this to general numbers 1 X 30 becomes 1, 1 + 1/2 becomes 1 and 3 becomes 3. But 1 X 30 needs to be 1 X 30 and 1 + 1/2 to be 1 + 1/2 after converting.
When you convert yourself trough the error message it stays the same.. I don't get why this is changing all my numbers.
2. My code takes every cell apart and fills 0 in the empty cells, it would be better if it just leaves the cells empty. (This proces is time consuming)
Thank you very much!
Hope someone have an answer
