-1

I have 2 excel worksheets (XLpicsWB.Sheets(sMinMaxWs) & ws.) where the cell value in the 1st worksheet may or may not exist in 2nd worksheet. I want to set a check condition if the value (opcTagItem.Value) does not exist in the 2nd worksheet. The "If" statement throws the exception error and I'm not sure how to properly set the check property:

Select Case AnnunciatorBlkSizeCount
    Case 1
        If XLpicsWB.Sheets(sMinMaxWS).Range("A:A").Find(opcTagItem.Value).Row.Equals(vbNull) Then
            ws.Rows(iCurrentRow).Replace(What:=sOldAnnunciatorName, Replacement:=sBaseTagItemName, LookAt:=XlLookAt.xlPart,
                                     SearchOrder:=XlSearchOrder.xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False)
            iCurrentRow = iCurrentRow + 1
            Exit Select     ' Some tag names don't have Min/Max data
        End If
Martin
  • 16,093
  • 1
  • 29
  • 48
C C
  • 1
  • 4
  • 1
    This is probably because `.Row` is NULL when `Find` cannot make a match, and therefore throws the `NullReferenceException`. – Martin Dec 04 '18 at 22:31
  • In fact, referring to https://learn.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.range.find?view=excel-pia for `Find`, we see that _This method returns Nothing if no match is found._ – Martin Dec 04 '18 at 22:32
  • Thanks Marin! I dropped the object property ".Row.Equals(vbNull)" to "Is Nothing" and object condition statement works. – C C Dec 04 '18 at 23:16
  • I've added my comments as an answer for future visitors to the page. Glad it solved your issue. – Martin Dec 04 '18 at 23:22

1 Answers1

0

This is probably because .Row is NULL when Find cannot make a match, and therefore throws the NullReferenceException.

Referring to https://learn.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.range.find?view=excel-pia for Find shows us that This method returns Nothing if no match is found., therefore you will be required to either remove .Row or check that Find has a match before utilising the output.

Martin
  • 16,093
  • 1
  • 29
  • 48