Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Set with Select Case (03)

    How would one pass the variable defined in a set statement to a case?

    Example: In this example oBUnit "1234" is not found in the range of business units ie the Set c returns 'Nothing or "c = Nothing" and an error message is returned indiciating that the "object variable or with block variable not set".

    oBUnit = "1234"

    With ActiveSheet.Range("R_BUnits")
    Set c = .Find("" & oBUnit & "", LookIn:=xlValues, LookAt:=xlWhole)
    Select Case c
    Case Nothing
    'Do nothing
    Case Else
    'Do something
    End Select
    End With

    Your thoughts are appreciated,
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Set with Select Case (03)

    You can't test for Nothing in a Select Case statement, you'll have to use If Then Else:

    If c Is Nothing Then
    ...
    Else
    ...
    End If

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set with Select Case (03)

    Hans,

    That's what I thought.

    Regards,
    John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •