VBA to set formula to multiple column in different worksheets

Question

I am trying to fix the dates in multiple worksheets under different columns but I can't seen to figure out how to.

Sub FixDate()
Dim i As Long

Sheets("Sheet1").Select

For i = 2 To Range("N" & Rows.Count).End(xlUp).Row
    With Range("N" & i)
        .NumberFormat = "mm/dd/yy hh:mm"
        .Value = DateValue(.Value)
    End With
Next i

For i = 2 To Range("O" & Rows.Count).End(xlUp).Row
    With Range("O" & i)
        .NumberFormat = "mm/dd/yy hh:mm"
        .Value = DateValue(.Value)
    End With

Next i


Sheets("Sheet2").Select

For i = 2 To Range("F" & Rows.Count).End(xlUp).Row
    With Range("F" & i)
        .NumberFormat = "mm/dd/yy hh:mm"
        .Value = DateValue(.Value)
    End With
Next i

End Sub

I have 4 worksheets that needs to fix the dates under different columns. Please advise.

So what is your question? You didn't ask one. Please read [ask]. — Pᴇʜ, Oct 23 '17 at 07:52

score 1 · Accepted Answer · answered Oct 23 '17 at 07:55

You can split your code to have a Sub that will modify all cells format to "mm/dd/yy hh:mm", and then call it multiple times and pass the relevant column (as String) and the Worksheet object.

Note: there's no need to Select the worksheets in order to modify their cells format.

Code

Option Explicit

Sub FixDate()

' call sub, pass column "N" and worksheet object "Sheet1"
FixDatesInCol "N", ThisWorkbook.Worksheets("Sheet1")

FixDatesInCol "O", ThisWorkbook.Worksheets("Sheet1")

FixDatesInCol "F", ThisWorkbook.Worksheets("Sheet2")

End Sub

Sub FixDatesInCol(Col As String, ws As Worksheet)

Dim i As Long

With ws
    For i = 2 To .Range(Col & .Rows.Count).End(xlUp).Row
        With .Range(Col & i)
            .NumberFormat = "mm/dd/yy hh:mm"
            .Value = DateValue(.Value)
        End With
    Next i
End With

End Sub

score 0 · Answer 2 · answered Oct 23 '17 at 08:27

or like this:

Sub dates()
Dim sShts As String
Dim sCols As String
Dim i As Integer
Dim j As Integer

sCols = "N;O;F"
sShts = "1;1;2"

For j = 0 To UBound(Split(sShts, ";"))
    Debug.Print j
    With Worksheets("sheet" & Split(sShts, ";")(j))
        For i = 1 To .Range(Split(sCols, ";")(j) & Rows.Count).End(xlUp).Row
        With .Range(Split(sCols, ";")(j) & i)
            .NumberFormat = "mm/dd/yy hh:mm"
            .Value = DateValue(.Value)
        End With

    Next i
    End With
Next j
End Sub

you can adjust/add worksheets/ column just by editing sCols and sShts.

Note: Never use `Integer` especially for row counting, Excel has more rows than `Integer` can handle, [always use `Long` instead](https://stackoverflow.com/a/26409520/3219613). — Pᴇʜ, Oct 23 '17 at 09:44

VBA to set formula to multiple column in different worksheets

2 Answers2