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
– dgorti, Commented Jan 22, 2017 at 3: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
– Mahyar Mohammadi, Commented Jan 22, 2017 at 3:35
It is absolutely possible to do this if...then ...if then else end if 'inner end if.... end if 'outer endif — dgorti
– dgorti, Commented Jan 22, 2017 at 3:42

YowE3K · Accepted Answer · 2017-01-22 04:01:21Z

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

Collectives™ on Stack Overflow

select case, according to find text

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related