excel 根据日期跨行拖动公式

bvhaajcl  于 2023-03-04  发布在  其他
关注(0)|答案(3)|浏览(142)

我写了一段代码,用一个公式填充一个单元格区域,该公式应该在特定的月份列停止。宏将自动找到与当前月份对应的列,并在该列停止填充公式。
目前,我使用它搜索第4行,这是我的日期行。但是,我希望它更动态一点,并在该行的开头查找字母"D",以表示它是日期行。
你知道我做错了什么吗?
谢谢!
下面是原始代码:

Sub DragFormulaToMonth()

Dim mdate As Date

For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    If Cells(i, 1) = "m" Or Cells(i, 1) = "M" Then
        mdate = Worksheets("Input").Range("B2").Value
        mcol = Application.WorksheetFunction.Match(CDbl(mdate), Range("4:4"), 0)
        Range(Cells(i, "AQ"), Cells(i, mcol)).FillRight
    End If
Next i

End Sub

下面是我为使日期行规范更具动态性所做的尝试:
以下是我试图让它更有活力的方法:

Sub DragFormulaToMonth()

Dim mdate As Date

For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row

    If Cells(i, 1) = "m" Or Cells(i, 1) = "M" Then
        mdate = Worksheets("Input").Range("B2").Value
        If Cells(i, 1) = "D" Then
            mcol = Application.Match(mdate, Range(Cells(i, 5), Cells(i, Columns.Count)), 0)
            If Not IsError(mcol) Then
                Range(Cells(i, "AQ"), Cells(i, mcol)).FillRight
            End If
        End If
        Range(Cells(i, "AQ"), Cells(i, mcol)).FillRight
     End If
 Next i

End Sub
yjghlzjz

yjghlzjz1#

如果您在immediate window中执行Debug.print Cells(i, 1)print Cells(i, 1),您会注意到结果为空
实际上,您需要的是Cells(i, 1).Value返回实际的单元格内容,因此需要将If语句修改为Cells(i, 1).Value
此外,您可以使用If UCase(Cells(i, 1).Value) = "M" Then,而不是使用两个单独的If语句来表示“M”和“m

iszxjhcz

iszxjhcz2#

代码首先检查是否为Cells(i, 1) = "m" Or Cells(i, 1) = "M",如果是,则查看If Cells(i, 1) = "D" Then。显然,如果Cells(i,1)M,则它不可能是D
也许这能满足你的愿望

Sub DragFormulaToMonth()

Dim mdate As Date

For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row

    If Cells(i, 1).Value  = "m" Or Cells(i, 1) = "M" Then
        mdate = Worksheets("Input").Range("B2").Value
    End if
    If Cells(i, 1).Value = "D" Then
        mcol = Application.Match(mdate, Range(Cells(i, 5), Cells(i, Columns.Count)), 0)
        If Not IsError(mcol) Then
            Range(Cells(i, "AQ"), Cells(i, mcol)).FillRight
        End If
    End If
    Range(Cells(i, "AQ"), Cells(i, mcol)).FillRight
Next i

End Sub
pqwbnv8z

pqwbnv8z3#

感谢您的反馈意见。我能够使用您的建议来解决我的问题。以下是最后的代码,为我工作:

Sub StoreRowWithD()
Dim rowNum As Long
Dim lastRow As Long
Dim firstDRow As Long

' Get the last row in Column 1
lastRow = Cells(Rows.Count, 1).End(xlUp).Row

' Loop through each row in Column 1 until we find a row with "D"
For rowNum = 1 To lastRow
    If Left(Cells(rowNum, 1), 1) = "D" Then
        ' If the first character of the cell in Column 1 of this row is "D",
        ' store the row number and exit the loop
        firstDRow = rowNum
        Exit For
    End If
Next rowNum

Dim mdate As Date
Dim mcol As Long

For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    If UCase(Cells(i, 1).Value) = "M" Then
        mdate = Worksheets("Input").Range("B2").Value
        mcol = Application.WorksheetFunction.Match(CDbl(mdate), Cells(firstDRow, 1).EntireRow, 0)
        Range(Cells(i, "AQ"), Cells(i, mcol)).FillRight
    End If
Next i

末端子组件

相关问题