select case, according to find text

Question

I want to update a sheet with new data about every 15 minutes. the code I'm looking for is something like:

case data is not exist in the sheet "do something" case else "do somthing else"

The code i wrote is:

Dim LastRow1 As Long
With Sheets("Stocks")
    LastRow1 = .Cells(.rows.count, "A").End(xlUp).row
End With

Dim e As String
Dim Cell As Range
Dim rRng As Range
Dim find As Range
Dim stock as string
Set rRng = Sheets("results").Range("D2:F" & LastRow1)


For Each Cell In rRng
    If Cell.Value >= (-0.01) And Cell.Value <= 0.01 Then
      stock = Sheets("results").Cells(Cell.row, 1)
      Set find = Sheets("Signal").Columns(1).find(what:=stock, MatchCase:=True, Lookat:=xlWhole)


    Select Case find      '> > > I have an error here, when find is nothing 
     Case Is = "nothing"
      MsgBox "add new data"
     Case Else
      MsgBox "Update data"
    End Select

'copy new data

     End If

error number is 97 object variable or with block variable not set

If you are using set x = something then you are assigning an object. so it looks like find is not set to an object. The right way to check an object is the reference to nothing. try: if find is nothing then ... else ..... endif — dgorti, Jan 22 '17 at 03:28
in this code first i use an " If " statement and i think without " End IF" it's not possible to use an " If " into another. @dgorti Am I right? — Mahyar Mohammadi, Jan 22 '17 at 03:35
It is absolutely possible to do this if...then ...if then else end if 'inner end if.... end if 'outer endif — dgorti, Jan 22 '17 at 03:42

score 1 · Answer 1 · answered Jan 22 '17 at 04:01

I believe what you are after is

If Cell.Value >= (-0.01) And Cell.Value <= 0.01 Then
    stock = Sheets("results").Cells(Cell.row, 1)
    Set find = Sheets("Signal").Columns(1).find(what:=stock, MatchCase:=True, Lookat:=xlWhole)

    If find Is Nothing Then
        MsgBox "add new data"
    Else
        MsgBox "Update data"
    End If

End If

select case, according to find text

1 Answers1