Hii ..
I have a issue with this code..
When I run this code
Please Login or Register to view this content.
'07/17-18'
'08/17-18'
'09/17-18'
'10/17-18'
gets converted to
17/07/2018
17/08/2018
17/09/2018
17/10/2018
but when I manually replace apostrophe with blank using find & replace (Ctrl+H) it works fine..
PFA
The issue cells are highlighted in yellow colour..
help me to resolve the issue!!
Thank you!!
Try to format the column A as Custom >> Text before you run the code
< ----- Please click the little star * next to add reputation if my post helps you
Visit Forum : From Here
I tried converting to text & run the code but that also didn't work for me..
Hi,
may try this?
Please Login or Register to view this content.
Try this code
Please Login or Register to view this content.
Hi ! Try these …
As / - characters are often the date separators …
PHP Code:Sub Demo1()
Dim V, R&
With ActiveSheet.UsedRange.Columns(1)
V = Evaluate("IF({1},SUBSTITUTE(" & .Address & ",""'"",""""))")
For R = 1 To UBound(V)
If IsDate(V(R, 1)) Then V(R, 1) = "'" & V(R, 1)
Next
.Value2 = V
End With
End Sub
A variation :
PHP Code:Sub Demo2()
Dim V, R&
With ActiveSheet.UsedRange.Columns(1)
V = Evaluate("IF({1},SUBSTITUTE(" & .Address & ",""'"",""""))")
For R = 1 To UBound(V)
If IsDate(V(R, 1)) Then Cells(R, 1).NumberFormat = "@"
Next
.Value2 = V
End With
End Sub
Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
Last edited by Marc L; 08-01-2019 at 07:40 AM.
This is the one if …
Next demonstration well works on my side with Windows regional settings like DD/MM/YYYY
and under a local Excel version (not UK or US) :
PHP Code:Sub Demo()
With ActiveSheet.UsedRange.Columns(1)
.FormulaLocal = Evaluate("IF({1},SUBSTITUTE(" & .Address & ",""'"",""""))")
End With
End Sub
Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
Try
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks