PDA

View Full Version : [SOLVED] VBA TO Correct Date format



malleshg24
10-31-2017, 12:18 PM
Hi Team,
I am new in VBA, please assist me in below code , :help
I want to put LR (last Row) in colulmn array .
One Single LR for all columns and where to add that line in looping.
Also Can someone expalin me the line what it indicates : - Worksheets(SheetNames(X)).Columns(Cols(X)).Replace " *", "", xlPart

Sub Correct_DateFormat_ineach_sheets_Column()
Dim wbk As Workbook
Dim lr As Long
Set wbk = Workbooks.Open(Sheets("sheet1").Range("b5").Value)

lr = Cells(Rows.Count, 1).End(xlUp).Row

Dim X As Long, SheetNames As Variant, Cols As Variant
SheetNames = Array("Sheet1", "Sheet2", "Sheet4", "Sheet5")
Cols = Array("J2:J" & lr, "L2:L" & lr, "P2:P" & lr, "R2:R" & lr)
For X = LBound(SheetNames) To UBound(SheetNames)
Worksheets(SheetNames(X)).Columns(Cols(X)).Replace " *", "", xlPart
Worksheets(SheetNames(X)).Columns(Cols(X)).NumberFormat = "yyyy-mm-dd"
Next
End Sub

Thanks in Advance for your Precious time !!

Regards,
Mallesh

mancubus
10-31-2017, 01:46 PM
you may be a vba newbie, but definitely not a vbaexpress newbie.
please use code tags when posting your code.

for replace and date format, you can use whole column.



Sub vbax_61213_correct_cate_format_sheets_cols()

Dim wbk As Workbook
Dim x As Long
Dim SheetNames As Variant, Cols As Variant

Set wbk = Workbooks.Open(Sheets("Sheet1").Range("B5").Value)

SheetNames = Array("Sheet1", "Sheet2", "Sheet4", "Sheet5")
Cols = Array("J:J", "L:L", "P:P", "R:R")

With ActiveWorkbook
For x = LBound(SheetNames) To UBound(SheetNames)
.Worksheets(SheetNames(x)).Columns(Cols(x)).Replace " *", "", xlPart
.Worksheets(SheetNames(x)).Columns(Cols(x)).NumberFormat = "yyyy-mm-dd"
Next
End With

End Sub

mancubus
10-31-2017, 01:52 PM
.Replace " *", "", xlPart

partially replaces the stings which start with a space in cells' values with null string, ie removes them.

"ssss" becomes "ssss" (doe not change)
"ddd ff" becomes "dd"
"12 345" becomes "12"
" qwerty" becomes "" (first character is a space)
"qwerty " becomes "qwerty" (last character is a space)

malleshg24
11-01-2017, 03:54 AM
Hi Mancubus,

Thanks for you quick reply and your information on .Replace " *", "", xlPart

However, I am still facing an issue, Subscript out of Range , for below line,

I would like to mention I have header in each column. I want the date format till lastrow.

.Worksheets(SheetNames(x)).Columns(Cols(x)).Replace " *", "", xlPart
.Worksheets(SheetNames(x)).Columns(Cols(x)).NumberFormat = "yyyy-mm-dd"

Please can someone assist.

Thanks in Advance
Mallesh

mancubus
11-01-2017, 05:18 AM
post your workbook pls

malleshg24
11-02-2017, 11:18 AM
Hi, Mancubus

The above code worked for me , When I put sheetName in array. Thanks a log !

Regards,
Mallesh