excel表格公式转VBA A:A=IF(D:D="收货","-1","") A:A=IF(D:D="订货","1","")转VBA代码？

Question

excel表格公式转VBA A:A=IF(D:D="收货","-1","")
A:A=IF(D:D="订货","1","")转VBA代码？ 有没有那个大神帮我转成VBA代码

Answer 1

程序：

效果：

代码：

Option Explicit

Sub 宏1()

    Dim arrA(), arrD(), n&, i&

    n = ActiveSheet.UsedRange.Rows.Count

    arrD = Range("d1").Resize(n, 1)

    ReDim arrA(1 To n, 1 To 1)

    For i = 1 To n

        Select Case arrD(i, 1)

            Case "收货"

                arrA(i, 1) = -1

            Case "订货"

                arrA(i, 1) = 1

        End Select

    Next i

    Range("a1").Resize(n, 1) = arrA

End Sub

Answer 2

你确定数字1、-1要加引号？
代码类似于
For i = 1 To Range("D65536").End(xlUp).Row
If Range("D" & i) = "收货" Then
Print Range("A" & i) = "-1"
ElseIf Range("A" & i) = "订货" Then
Range("A" & i) = "1"
Else
Range("A" & i) = ""
End If
Next