vba 查找列并格式化为日期

Find Column and format as Date

提问by Nupur

I have a several columns in my excel worksheets which are all named as "Date". I want to write a code which will find all date columns in all worksheets and format the values in that column as "dd/mm/yyyy;@" .

我的 excel 工作表中有几列都命名为“日期”。我想编写一个代码来查找所有工作表中的所有日期列，并将该列中的值格式化为 "dd/mm/yyyy;@" 。

In the code below I tried writing Columns("Date").Select instead of Columns("E:E").Select but this is not working. Can anyone suggest me how to fix this?

在下面的代码中，我尝试编写 Columns("Date").Select 而不是 Columns("E:E").Select 但这不起作用。谁能建议我如何解决这个问题？

Thanks for your time and suggestions.

感谢您的时间和建议。

Sub dateformat()      
Dim ws As Worksheet     
For Each ws In Worksheets         
Columns("E:E").Select         
Selection.NumberFormat = "dd/mm/yyyy;@"             
Next ws  
End Sub

回答by Siddharth Rout

Nupur, Is this what you are trying?

Nupur，这是你正在尝试的吗？

Option Explicit

Sub Sample()
    Dim aCell As Range, bCell As Range
    Dim ws As Worksheet
    Dim ExitLoop As Boolean

    For Each ws In ThisWorkbook.Sheets

        Set aCell = ws.Rows(1).Find(what:="Date", LookIn:=xlValues, _
        lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

        ExitLoop = False

        If Not aCell Is Nothing Then
            Set bCell = aCell

            ws.Columns(aCell.Column).NumberFormat = "dd/mm/yyyy;@"
            Do While ExitLoop = False
                Set aCell = ws.Rows(1).FindNext(After:=aCell)

                If Not aCell Is Nothing Then
                    If aCell.Address = bCell.Address Then Exit Do
                    ws.Columns(aCell.Column).NumberFormat = "dd/mm/yyyy;@"
                Else
                    ExitLoop = True
                End If
            Loop
        End If
    Next
End Sub

FOLLOWUP

跟进

The reason you are getting those errors is because your column is formatted as text. Try this. This works :)

您收到这些错误的原因是您的列格式为文本。尝试这个。这有效:)

Option Explicit

Sub Sample()
    Dim aCell As Range, bCell As Range
    Dim ws As Worksheet
    Dim lastRow As Long, i As Long
    Dim ExitLoop As Boolean

    For Each ws In ThisWorkbook.Sheets
        Set aCell = ws.Rows(1).Find(what:="Date", LookIn:=xlValues, _
        lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

        ExitLoop = False

        If Not aCell Is Nothing Then
            Set bCell = aCell

            ws.Columns(aCell.Column).NumberFormat = "dd/mm/yyyy;@"

            lastRow = ws.Range(Split(ws.Cells(, aCell.Column).Address, "$")(1) & _
            ws.Rows.Count).End(xlUp).Row

            For i = 2 To lastRow
                With ws.Range(Split(ws.Cells(, aCell.Column).Address, "$")(1) & i)
                    .FormulaR1C1 = .Value
                End With
            Next i

            ws.Columns(aCell.Column).AutoFit

            Do While ExitLoop = False
                Set aCell = ws.Rows(1).FindNext(After:=aCell)

                If Not aCell Is Nothing Then
                    If aCell.Address = bCell.Address Then Exit Do

                    ws.Columns(aCell.Column).NumberFormat = "dd/mm/yyyy;@"

                    lastRow = ws.Range(Split(ws.Cells(, aCell.Column).Address, "$")(1) & _
                    ws.Rows.Count).End(xlUp).Row

                    For i = 2 To lastRow
                        ws.Range(Split(ws.Cells(, aCell.Column).Address, "$")(1) & i).FormulaR1C1 = _
                        ws.Range(Split(ws.Cells(, aCell.Column).Address, "$")(1) & i).Value
                    Next i
                Else
                    ExitLoop = True
                End If
            Loop
        End If
    Next
End Sub

HTH

HTH

Sid

锡德

回答by Andrew

Can you incorporate some of this code(modify to fit your req's)?

你能加入一些这样的代码吗（修改以适应你的要求）？

varLookFor1 = Format(Date - 1, "[$-409]mmmm-yy;@")
ActiveWorkbook.ActiveSheet.Cells.Find(What:=varLookFor1, LookIn:=xlValues).Activate