使用excel vba将单元格值格式化为“MMMM”

I have the below code that I need some assistance modifying.

我有以下代码,我需要一些帮助修改。

Sub CopyDataBasedOnTimeRangeMonth()
Dim i, LastRow
Dim Cell As Range


LastRow = Sheets("OPA").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Sheet2").Range("A3:U500").ClearContents
For i = 2 To LastRow
If Sheets("OPA").Cells(i, "G").Value >= Range("U1") And Sheets("OPA").Cells(i, "G").Value < Range("AC1") Then
Sheets("OPA").Cells(i, "R").EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
End Sub

For the calculation of "G" I want to use the format of "MMMM" of the value being calculated. In an excel formula I can use something like Text("G12","MMMM") and then continue with the formula but I don't know how to modify the about code to just to use the Month only value of "G".

对于“G”的计算,我想使用所计算值的“MMMM”格式。在excel公式中,我可以使用Text(“G12”,“MMMM”)之类的东西,然后继续使用公式,但我不知道如何修改about代码只是为了使用仅月值“G”。

Thanks in advance for any help you can provide.

提前感谢您提供的任何帮助。

2 个解决方案

#1

you can see below the variant of your code, which has been updated using variant provided by Davesexcel (+1), and also some correction from my side, just to simplify readability:

您可以在下面看到代码的变体,它已使用Davesexcel(+1)提供的变体进行了更新,还有一些来自我方的更正,只是为了简化可读性:

1) absolute reference to Range() replaced to [] shorthand method;
2) removed Destination:= as excessive, also destination range replaced by row, because when you copy the row then destination shall be the row;
3) applied with (object) method;
4) added type of the variables, e.g. i replaced by i& (means i as long)

1)绝对引用Range()替换为[]速记方法; 2)删除目的地:=过多,目的地范围也被行替换,因为当你复制行时,目的地应该是行; 3)应用(对象)方法; 4)添加的变量类型,例如,我用i替换(意思是我长)

Sub CopyDataBasedOnTimeRangeMonth()
    Dim i&, LastRow&, Cl As Range
    LastRow = Sheets("OPA").Range("A" & Rows.Count).End(xlUp).Row
    Sheets("Sheet2").[A3:U500].ClearContents
    With Sheets("OPA")
        For i = 2 To LastRow
            If Month(.Cells(i, "G")) >= Month(.[U1]) And _
               Month(.Cells(i, "G")) < Month(.[AC1]) Then
                .Rows(i).Copy Sheets("Sheet2").Rows(Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1)
            End If
        Next i
    End With
End Sub

tested, works fine.

测试,工作正常。

source:

destination:

#2

It would be best to have [U1] as a date as well, then formatted as month

最好将[U1]作为日期,然后格式化为月份

If Month(Sheets("OPA").Cells(i, "G")) >= Month(Range("U1"))

如果是月份(表格(“OPA”)。单元格(i,“G”))> =月份(范围(“U1”))

#1