asked on

Expanding existing macro to include several ranges

The macro below converts a column of false dates in Column B to real dates in a spreadsheet. I would like to expand the functionality of this macro to convert several columns of false dates in a spreadsheet.

Sub Metrics_PubWOs_RealDates()
Dim cel As Range, rg As Range
Application.ScreenUpdating = False
With ActiveSheet
    Set rg = .Range("b2")
    Set rg = Range(rg, .Cells(.Rows.Count, rg.Column).End(xlUp))
    For Each cel In rg.Cells
        If Application.IsText(cel.Value) Then
            If IsDate(cel.Value) Then cel.Value = CDate(cel.Value)
        End If
    Next
End With
End Sub

Open in new window

So in addition to converting false dates in Column B, I would like this macro to also convert dates in Columns D, F, H, J, L, N, P, R, T, V, X, Z, AB, AD, AF, AH, AJ, AL, and AN. (Note that all columns requiring this macro to be run has a column header with the text string "Col", in case it's easier to write VBA code using this criteria instead).

I have attached an example of the spreadsheet.

Thanks,
Andrea
EE_MultipleRanges.xlsm

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andreamary

ASKER

Terrific...thanks, Hainkurt!

HainKurt

no it is not terrific!

but this one is...

Sub Metrics_PubWOs_RealDate(rg)
    For Each cel In rg.Cells
        If Application.IsText(cel.Value) Then
            If IsDate(cel.Value) Then cel.Value = CDate(cel.Value)
        End If
    Next
End Sub

Sub Metrics_PubWOs_RealDates()
    Application.ScreenUpdating = False
    Range("A2").Select
    Selection.End(xlDown).Select
    
    Dim r As Integer
    Dim rg As Range
    r = ActiveCell.Row
    
    Dim cols As Variant
    cols = Array("D", "F", "H", "J", "L", "N", "P", "R", "T", "V", "X", "Z", "AB", "AD", "AF", "AH", "AJ", "AL", "AN")
    For Each col In cols
        Set rg = Range(col & 2 & ":" & col & r)
        Metrics_PubWOs_RealDate rg
    Next
End Sub

Open in new window

above one checks every cell, but this one is just those columns...

:)

Roy Cox

There's no reason to select a range and you are using a Table so use the parts of that Table, i.e. the Data is the DatBodyRange.

Try this

Option Explicit

Sub Metrics_PubWOs_RealDates()
    Dim cel As Range

    For Each cel In ActiveSheet.ListObjects(1).DataBodyRange.SpecialCells(xlCellTypeConstants).Cells
        On Error Resume Next
        If IsDate(cel.Value) Then cel.Value = CDate(cel.Value)
        On Error GoTo 0
    Next cel

End Sub

Open in new window

Andreamary

ASKER

Thanks to you both for your follow-up - I hadn't realized there were further responses until tonight.

HainKurt, I appreciated your updated solution but when I tried it I got the following error in Line 20: For Each col In cols:
Compile Error: Variable not defined

Roy, thanks for your streamlined code. I tried your solution and noticed a couple of issues:
1. It took 10 to 15 minutes for the macro to complete.
2. A work order # in column A was converted to a date.

Thanks again! BTW, if you think I should be starting this as a new question, given I've already awarded the points, please let me know...

Cheers,
Andrea

Roy Cox

I do not believe the code too that long to work, I tested it on your example workbook an it was instantaneous, can you attach the actual workbook that took so long.

What was the actual order number?

Andreamary

ASKER

Hi Roy,

Thanks for your note. Attached is the actual workbook (confidential information removed) so you can view how the macro performs.

The actual work order numbers are located in Col A, called WO. Interestingly, there was only one WO number, that appears 3 times in Col A, that converted to a date — 2017-0000004.

Thanks for your continued assistance!

Andrea
EE_TextDates.xlsx

Roy Cox

I've just run the code and it takes seconds to complete. I'll take a look at the WO now

Roy Cox

Excel for some reason thinks that number is a date. I've excluded the column from the check because it's not necessary to check it.

This runs in seconds for me. I've highlighted the problem cells in yellow so you can find them easily.
EE_TextDates.xlsm