Search code examples
excelvbanull

How do I make an integer to null in Excel VBA?

I am trying to detect whether an integer was set, and if not, skip most of the code in the loop (using an if statement). Here is what I have so for.

Do While hws.Cells(r, 9).Value <> ""
    On Error Resume Next
    ar = Null
    ar = aws.Range("A:A").Find(hws.Cells(r, 2).Value).Row
    If Not IsNull(ar) Then
  'work with ar'
    End If
    r = r + 1
Loop

However, when I run it, ar = Null has problems. It says "Invalid use of null".

Solution

  • Find returns a range:

    Dim rf As Range
With aws.Range("A:A")
    Set rf = .Find(hws.Cells(r, 2).Value)
    If Not rf Is Nothing Then
        Debug.Print "Found : " & rf.Address
    End If
End With

    -- http://msdn.microsoft.com/en-us/library/aa195730(office.11).aspx