时间戳

首页 > 解决方案 > 每个语句和 if 语句使用 2 个条件的范围

excel - 每个语句和 if 语句使用 2 个条件的范围

问题描述

我的发票生成器代码的一部分是将数据从发票模板移动到另一个工作表。但是，有些产品没有条形码，我需要单独运行代码。

到目前为止，下面的代码是我尝试过的，但似乎不起作用。它似乎只是不检查 range("B20:B32") 的单元格是否包含 a 或 b。

我希望每当 b20 到 b32 = a 之间的每个单元格时，它都会运行 codeA，否则如果 b20 到 b32 = b 之间的单元格，那么它将运行 codeB。

我的另一个选择是为 b20 到 b32 之间的每个单元格编写一个代码，但我的代码很长，我有 8 个单元格来检查它的值是 = a 还是 b。

请帮忙。谢谢

            Private Sub Testfor()
            Dim cell As Range
            Dim r As Long
            Dim pd As Range

            r = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
            Set pd = Sheet1.Range("B20:B32")
            For Each cell In pd

            'codeA
            If cell = "DPS" Or cell = "TS" Then

            Sheet2.Cells(r, 1) = "yes"
            Sheet2.Cells(r, 2) = "yes"
            Sheet2.Cells(r, 3) = "yes"

            'codeB
            ElseIf cell = "FMC" Or cell = "PM" Or cell = "FC" Then

            Sheet2.Cells(r, 1) = "K"
            Sheet2.Cells(r, 2) = "v"
            Sheet2.Cells(r, 3) = "c"

            End If

             Next cell



            End Sub

标签： excelvba

解决方案

试试这段代码，看看它是否在做你想要实现的目标。r如果您想在每次迭代中更改它，您应该将其放入循环中。

Private Sub Testfor()
Dim cell As Range
Dim r As Long
Dim pd As Range
Dim Sheet1 As Worksheet: Set Sheet1 = ThisWorkbook.Sheets("Sheet1")
Dim Sheet2 As Worksheet: Set Sheet2 = ThisWorkbook.Sheets("Sheet2")

Set pd = Sheet1.Range("B20:B32")
For Each cell In pd
r = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    If cell = "DPS" Or cell = "TS" Then
        Sheet2.Cells(r, 1) = "yes"
        Sheet2.Cells(r, 2) = "yes"
        Sheet2.Cells(r, 3) = "yes"
    ElseIf cell = "FMC" Or cell = "PM" Or cell = "FC" Then
        Sheet2.Cells(r, 1) = "K"
        Sheet2.Cells(r, 2) = "v"
        Sheet2.Cells(r, 3) = "c"
    End If
Next cell
End Sub

推荐阅读