Excel VBA : Run a Formula as long as there is a value in Another Column

Question

我在 A 列中有数据。
我在 B 列中有一个公式。

=CONCAT("Test",":",LEFT(A2,FIND("-",A2)-1))

只要 A 列中有数据，我就希望公式运行。

Column A  Column B
3032-215  Test:3032
3031-158  Test:3031
4005-169  Test:4005 

我正在尝试编写此代码，以便只要 A 列中有数据，它就会自动填充 B 列。

Answer 1

如果您想要 VBA 解决方案，那么 Private Sub 就可以解决问题。就像是:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Range("A:A"), Target) Is Nothing Then

        Dim MyRange As Range, c As Range, LastRow As Integer

        LastRow = Cells(Rows.Count, 1).End(xlUp).Row
        Set MyRange = ActiveSheet.Range("A2:A" & LastRow)

        For Each c In MyRange

            If c.Value <> "" Then

                c.Offset(0, 1).FormulaR1C1 = "=concat(""Test"","":"",LEFT(RC1,4))"

            End If

        Next c

    End If

End Sub