CODEWITHSUNDEEP
excelfindif-casevba
Mahyar Mohammadi
Mahyar Mohammadi

Reputation: 13

select case, according to find text

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

Upvotes: 0

Views: 182

Answers (1)

YowE3K
YowE3K

Reputation: 23974

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

Upvotes: 1

Related Questions