Search code examples
vbamatchworksheet-function

Match a value in VBA based on a condition

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.

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.

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

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

Thank you !

Solution

  • 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")
    '...