时间戳

首页 > 解决方案 > VBA 未输入 then 语句

excel - VBA 未输入 then 语句

问题描述

此代码旨在查看一系列单元格并将其值与参考单元格进行比较，然后根据 excel 的哪个部分与参考值匹配来更新某些单元格。我收到“无效的过程调用或参数”错误。当我使用 F8 浏览我的代码时，它似乎是在第二个 If 语句之后触发的。

Private Sub CommandButton1_Click()
Dim rng As Range, cell As Range
  Set rng = Range("A171:AJ171")
  For Each cell In rng
    If cell.Value = ActiveSheet.Cells(4, 4).Value Then
      If ActiveSheet.Cells((cell.Row + 1), cell.Column).Value = ActiveSheet.Cells("D3").Value Then
        ActiveSheet.Cells("M45").Value = ActiveSheet.Cells((cell.Row + 3), cell.Column).Value
        ActiveSheet.Cells("M46").Value = ActiveSheet.Cells((cell.Row + 4), cell.Column).Value
        ActiveSheet.Cells("M47").Value = ActiveSheet.Cells((cell.Row + 5), cell.Column).Value
        ActiveSheet.Cells("M48").Value = ActiveSheet.Cells((cell.Row + 6), cell.Column).Value
        ActiveSheet.Cells("M49").Value = ActiveSheet.Cells((cell.Row + 7), cell.Column).Value
        ActiveSheet.Cells("M26").Value = ActiveSheet.Cells((cell.Row + 8), cell.Column).Value
        ActiveSheet.Cells("M27").Value = ActiveSheet.Cells((cell.Row + 9), cell.Column).Value
        ActiveSheet.Cells("M28").Value = ActiveSheet.Cells((cell.Row + 10), cell.Column).Value
        ActiveSheet.Cells("M57").Value = ActiveSheet.Cells((cell.Row + 11), cell.Column).Value
        ActiveSheet.Cells("M59").Value = ActiveSheet.Cells((cell.Row + 12), cell.Column).Value
        ActiveSheet.Cells("M60").Value = ActiveSheet.Cells((cell.Row + 13), cell.Column).Value
      End If
    End If
  Next
End Sub

标签： excelvbaif-statement

解决方案

试试这个代码：

Private Sub CommandButton1_Click()
    Dim rng As Range, cell As Range
    Dim i As Long
    Dim d4 As Variant, d3 As Variant
    d3 = Range("D3").Value
    d4 = Range("D4").Value
    ' rng starts at A171 
    Set rng = Range("A171")
    ' loop through 36 columns to AJ171
    For i = 1 To 36
        If rng.Cells(1, i).Value = d4 Then
            If rng.Cells(2, i).Value = d3 Then
                'Copy values from M45:M49 to Axxx3:Axxx7
                Range("M45").Resize(5, 1).Value = rng.Cells(3, i).Resize(5, 1).Value
                'Copy values from M26:M28 to Axxx8:Axxx10
                Range("M26").Resize(3, 1).Value = rng.Cells(8, i).Resize(3, 1).Value
                'Copy values from M57 to Axxx11
                Range("M57").Value = rng.Cells(11, i).Value
                'Copy values from M59:M60 to Axxx12:Axxx13
                Range("M59").Resize(2, 1).Value = rng.Cells(12, i).Resize(2, 1).Value
            End If
        End If
    Next i
End Sub

一些显着的变化：

  • in 的值与 in.Cells(4,4)相同，.Range("D4")并且存储在 in 的循环之外d4。
  • in 的值.Range("D3")类似地存储在 in 的循环之外d3。
  • 使用For带索引的循环，i以便您可以在下面的表格中引用单元A171格rng.Cells(row,i)
  • Range(destination).Resize(n,m).Value = Range(source).Resize(n,m).Value使用该模式后置并写入多个连续的单元格。

推荐阅读