0

i'm new here and to VBA and I need your assistance.

I have an excel spreadsheet with a dynamic table (Header is on Row 4). The data imported into the table contains a date values (5 September, 2018 6:11:17 PM EDT) that Excel cannot format to m-d-yyyy. the only way we can format the dates are to remove the 'comma', 'EDT' and 'EST' values. The macro runs and works as expected.

Now my challenge is to modify this macro (VBA) to look for the column header name instead of the whole column. As I keep getting asked to add a column to the table. The column names are 'Target Decomm Date', 'Actual Decomm Date', 'Created Date', 'Last Updated Date', 'Accreditation Date', and 'Accreditation Expiry Date'

All data is populated in row 5 and this is a dynamic table.

Here is my current code

Sub ConvertDateFormat()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+Shift+D
'
    Range("V:V,W:W,Z:Z,AA:AA,AC:AC,AD:AD").Select
    Range("V5").Activate
    Selection.NumberFormat = "m/d/yyyy"
    Selection.Replace What:=",", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="EDT", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="EST", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub
morin_luc
  • 1

2 Answers2

0

You can reference to the table and it's columns directly without using column letters.

Dim tbl As ListObject 'this will be the table
Dim rngData as Range

Set tbl = ActiveSheet.ListObjects("name_of_your_table")
Set rngData = tbl.ListColumns("Target Decomm Date").DataBodyRange
rngData.NumberFormat = "m/d/yyyy"
rngData.Replace [.....]

You can use an array to loop through all your different column names, but I won't cover that here, as it's well documented on the net, for example How can I use a for each loop on an array?

Rob
  • 101
  • 5
  • thanks for your assistance. it worked as expected. now I just need to figure out how to run the script for multiple columns. – morin_luc Oct 16 '18 at 16:20
  • Create a string array, fill it with the names of all columns, use the "for each" loop as described in the link I wrote above, and please don't forget to mark the answer if this has worked out for you. Thanks :-) Edit: thisone explains the array creation better: https://stackoverflow.com/questions/19369132/declare-and-initialize-string-array-in-vba – Rob Oct 17 '18 at 08:44
0

here is my end script and it worked well.

Sub FormatDate()
'
Dim tbl As ListObject 'this will be the table
Dim rng1, rng2, rng3, rng4, rng5, rng6, rngM As Range

Set tbl = ActiveSheet.ListObjects("tblProductList")
Set rng1 = tbl.ListColumns("Target Decomm Date").DataBodyRange
Set rng2 = tbl.ListColumns("Actual Decomm Date").DataBodyRange
Set rng3 = tbl.ListColumns("Created Date").DataBodyRange
Set rng4 = tbl.ListColumns("Last Updated Date").DataBodyRange
Set rng5 = tbl.ListColumns("Accreditation Date").DataBodyRange
Set rng6 = tbl.ListColumns("Accreditation expiry Date").DataBodyRange
Set rngM = Union(rng1, rng2, rng3, rng4, rng5, rng6)
rngM.NumberFormat = "m/d/yyyy"
rngM.Replace What:=",", Replacement:=" ", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
rngM.Replace What:="EDT", Replacement:=" ", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
rngM.Replace What:="EST", Replacement:=" ", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
End Sub
morin_luc
  • 1