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
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