Oryos
12-30-2016, 06:18 AM
Hi everyone,
I want to automate a daily PnL reporting with help of a macro.
In order to work with the data I export an excel file from another system. I cannot change the export format.
Within this file I have two columns with dates that have an apostrophe at the beginning of the value string. Now this apostrophe is causing a lot of problem for me because I want to work with these values in date format.
I read a lot about it and it seems those apostrophe are a common issue. I thought removing the apostrophe with this code
Sub test()
Dim wb As Worksheet
Dim rngSource As Range
Dim rngTarget As Range
Dim vSource As Variant
Dim rCell As Range
Dim vItem As Variant
Dim sTemp As String
Dim dTemp As Date
Dim aTemp() As String
ThisWorkbook.Activate
Set wb = ThisWorkbook.Worksheets(1)
Set rngSource = Range(wb.Range("A1"), wb.Range("B1").End(xlDown))
For Each rCell In rngSource
If rCell.PrefixCharacter = "'" Then
rCell.Value = rCell.Text
End If
Next
End Sub
That didnt help.
Than i tried:
sTemp = CStr(rCell.Value)
If Left(sTemp, 1) = "'" Then
sTemp = Right(sTemp, Len(sTemp) - 1)
rCell.Value = sTemp
End If
But the apostrophe still remain. Any idea how I remove the apostrophe with help of VBA?
Thanks and happy new year to you all!
I've attached the file for your reference.
17925
I want to automate a daily PnL reporting with help of a macro.
In order to work with the data I export an excel file from another system. I cannot change the export format.
Within this file I have two columns with dates that have an apostrophe at the beginning of the value string. Now this apostrophe is causing a lot of problem for me because I want to work with these values in date format.
I read a lot about it and it seems those apostrophe are a common issue. I thought removing the apostrophe with this code
Sub test()
Dim wb As Worksheet
Dim rngSource As Range
Dim rngTarget As Range
Dim vSource As Variant
Dim rCell As Range
Dim vItem As Variant
Dim sTemp As String
Dim dTemp As Date
Dim aTemp() As String
ThisWorkbook.Activate
Set wb = ThisWorkbook.Worksheets(1)
Set rngSource = Range(wb.Range("A1"), wb.Range("B1").End(xlDown))
For Each rCell In rngSource
If rCell.PrefixCharacter = "'" Then
rCell.Value = rCell.Text
End If
Next
End Sub
That didnt help.
Than i tried:
sTemp = CStr(rCell.Value)
If Left(sTemp, 1) = "'" Then
sTemp = Right(sTemp, Len(sTemp) - 1)
rCell.Value = sTemp
End If
But the apostrophe still remain. Any idea how I remove the apostrophe with help of VBA?
Thanks and happy new year to you all!
I've attached the file for your reference.
17925