根据条件匹配VBA中的值 [英] Match a value in VBA based on a condition

问题描述

在工作表2中，我想要一个宏来运行表值，主要是在F列(最大入口浓度)和B列(操作)中，如您在下图中看到的工作表1 .基本上，它将找到与F列的0值相对应的引用操作.

In worksheet 2 I want a macro to run the table values, mainly in column F (maximum inlet concentration) and column B (operation) as you can see in the following picture Worksheet 1. Basically, it will find the reference operation which corresponds to 0 value of column F.

它将运行F列，并在找到0值时返回匹配操作.这样做直到表末尾.如果我有1A-0、2B-0和4C-0，它将始终选择宏查找到0值的第一个操作.在图片中，宏必须返回值1，这是第一个操作.

It will run the column F and when finds a 0 value, it returns the matching operation. Do this until the end of the table. If I have 1A - 0, 2B - 0 and 4C - 0, it will always select the first operation that the macro finds a 0 value. In the picture, the macro has to return the value 1, that is the first operation.

因此，我编写了以下代码，并给出了运行时错误"91":对象变量或With块变量未设置.

So I have written the following code and gives the run-time error '91': Object variable or With block variable not set.

Dim sh1 As Worksheet
Dim StartCellCin As Range
Dim StartCellO As Range
Dim startRow As String
Dim multiplication As Integer
Dim countRows As Integer
Dim lastRow As Long
Dim operation As Long

Set StartCellCin = sh1.Range("F13")
Set StartCellO = sh1.Range("B13")
startRow = StartCellCin.Row
multiplication = sh1.Range("D4").Value2 * sh1.Range("D6").Value2
countRows = multiplication - 1
lastRow = startRow + countRows

Do While startRow < lastRow
 If StartCellCin.Value = 0 Then
  operation = Application.WorksheetFunction.Index(sh1.Range("B13"), Application.WorksheetFunction.Match(0, sh1.Range("startRow:lastRow"),0),1)
  startRow = startRow + 1
 Else
  startRow = startRow + 1
  If StartCellCin.Offset(startRow).Value = 0 Then
   operation = Application.WorksheetFunction.Index(sh1.Range("B13").Offset(startRow), Application.WorksheetFunction.Match(0,sh1.Range("startRow:lastRow"),0),1)
   startRow = startRow + 1
  End If
 End If
Loop

当我使用Option Explicit运行时，它不会返回任何语法错误.有人可以帮助我吗?

When I run with Option Explicit it does not return any syntax error. Anyone can help me?

谢谢！

推荐答案

如果我没记错的话，您只需要再增加一行即可.在使用WorkSheet对象之前，您必须设置工作表. (尽管对于这样一个简单的循环，不需要所有这些对象)，但是，要回答您的问题，请参见此处的第一行:

If I'm not wrong, you need one more line only. You have to Set the sheet before you use your WorkSheet Object. (although you don't need all these objects for such a simple loop) But yet, to answer your question, see the first line here:

    Set sh1 = Sheets("Your Sheet's Name")
    Set StartCellCin = sh1.Range("F13")
    Set StartCellO = sh1.Range("B13")
    '...

这篇关于根据条件匹配VBA中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！