如何使用OR和Excel VBA中的空单元格生成IF语句

I am trying to make an IF Statement in a VBA script. What it ultimately wants to achieve is to copy content from one sheet ("Database") to another sheet ("Search"), given the fulfilment of an IF statement.

我试图在VBA脚本中制作IF语句。它最终想要实现的是在满足IF语句的情况下将内容从一个工作表(“数据库”)复制到另一个工作表(“搜索”)。

The script starts by defining variables ("country", "category") as dependent on user input on cells "E5" and "E7" of the "Search" sheet. It also defines the final row for the If statement to run until that row:

该脚本首先定义变量(“country”,“category”),这取决于“Search”表单元格“E5”和“E7”上的用户输入。它还定义了If语句的最后一行,直到该行为止:

country = Sheets("Search").Range("E5").Value
category = Sheets("Search").Range("E7").Value
finalrow = Sheets("Database").Range("A200000").End(xlUp).Row

After this, the script establishes the condition: If the inputted values (in the cells "country" and "category") are matched by content on cells of the "Database" sheet, then the values on this sheet should be copied to the "Search" sheet:

在此之后,脚本建立条件:如果输入的值(在单元格“country”和“category”中)与“Database”表单元格上的内容匹配,则此表单上的值应复制到“搜索“表格:

For i = 2 To finalrow
    If Sheets("Database").Cells(i, 1) = country And _
    Sheets("Database").Cells(i, 3) = category Then
      With Sheets("Database")
        .Range(.Cells(i, 1), .Cells(i, 9)).Copy
      End With
      Sheets("Search").Range("B600").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats            
    End If                   
Next I

I would like to add an additional condition to the IF statement with an OR statement. I would like to make it so that if the user does not fill the "category" cell, the values are still copied from one sheet to another. In terms of code, I added the part between ** but it is not working:

我想用一个OR语句向IF语句添加一个附加条件。我想这样做,如果用户没有填写“类别”单元格,值仍然会从一个工作表复制到另一个工作表。在代码方面,我在**之间添加了部分,但它不起作用:

For i = 2 To finalrow
    If Sheets("Database").Cells(i, 1) = country And _
    Sheets("Database").Cells(i, 3) = category **Or category = ""** Then
      With Sheets("Database")
        .Range(.Cells(i, 1), .Cells(i, 9)).Copy
      End With
      Sheets("Search").Range("B600").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats           
    End If                   
Next i

Apart from this, everything is working out fine. Do you have any idea of what I might be doing wrong? Thank you!

除此之外,一切都很好。你知道我可能做错了什么吗?谢谢!

1 个解决方案

#1

I'd just avoid the Or entirely if it's giving you issues. also putting a space in " " is literally looking for space as a value, if you mean for it to be blank use "" or IsEmpty(Category):

如果它给你带来问题,我会完全避免使用Or。在“”中放置一个空格实际上是在寻找空间作为一个值,如果你的意思是空白使用“”或IsEmpty(类别):

For i = 2 To finalrow
    If Category = "" Then

        If Sheets("Database").Cells(i, 1) = country Then

            With Sheets("Database")
            .Range(.Cells(i, 1), .Cells(i, 9)).Copy
        End With
             Sheets("Search").Range("B600").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats

        End If

    Else

        If Sheets("Database").Cells(i, 1) = country And _
        Sheets("Database").Cells(i, 3) = Category Then

            With Sheets("Database")
            .Range(.Cells(i, 1), .Cells(i, 9)).Copy
        End With
             Sheets("Search").Range("B600").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats

        End If

Next i