插入带有动态行的公式 [英] Insert formula with dynamic row

问题描述

我有一个公式在A列中查找日期:"值，然后将公式粘贴到相邻的B单元格中，但是我不知道如何使该公式动态化.

I have a formula that looks for a "Date:" Value in column A and pastes a formula in the adjacent B cell, but I can't figure out how to make the formula dynamic.

因此，如果我在A6和A8中都有一个值-我希望插入的公式具有相同的行号T6和T8.现在，它每次都使用相同的行号.

So if I have a value in A6 and in A8 - I would want the inserted formula to have the same row number T6 and T8. Right now it uses the same row number every time.

Sub Check()
Dim rng As Range
Dim i As Long
  

Set rng = Range("A8:A48")
   
For Each cell In rng
    'test if cell is empty
    If cell.Value = "Date:" Then
        'write to adjacent cell

        cell.Offset(0, 1).Formula = "=TEXT(T8,""mmm-dd-yyyy"")&"" | ""&V8&"" - ""&U8&"" | Dept: ""&W8"
       
    End If
Next
End Sub

推荐答案

使用R1C1格式的格式:

Use the R1C1 style of formatting:

Sub Check()
    Dim rng As Range
    Dim i As Long
    Dim cell As Range 'Remember to declare cell as a range.

    'Qualify your ranges with at least the sheet name.
    Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A8:A48")

    For Each cell In rng
        'test if cell is empty
        If cell.Value = "Date:" Then
            'write to adjacent cell

            'cell.Offset(0, 1).Formula = "=TEXT(T8,""mmm-dd-yyyy"")&"" | ""&V8&"" - ""&U8&"" | Dept: ""&W8"
            cell.Offset(0, 1).FormulaR1C1 = "=TEXT(RC20,""mmm-dd-yyyy"")&"" | ""&RC22&"" - ""&RC21&"" | Dept: ""&RC23"

        End If
    Next
End Sub

RC20表示此行，第20列.R1C20表示此行，第20列.RC[-1]表示此行，在左侧一列.
http://www.numeritas.co.uk/2013/09/the-%E2%80%98dark-art%E2%80%99-of-r1c1-notation/

RC20 means this row, column 20. R1C20 means row 1, column 20. RC[-1] means this row, one column to the left.
http://www.numeritas.co.uk/2013/09/the-%E2%80%98dark-art%E2%80%99-of-r1c1-notation/

这篇关于插入带有动态行的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！