CODEWITHSUNDEEP
excelvba
ahmad
ahmad

Reputation: 21

Find and select the offset cell of the result

enter image description here

How to write the code in VBA?

I need the Search command button to search for the E2 which contain date value in the B:B row which also contain date value and select the OFFSET(0,1) cell The result ?

I'm getting this error:

Object variable or with block variable not set

This is the code I used:

Private Sub btngo_Click()
    Dim thedate As String
    Dim fromlist As Range

    thedate = Range("E2").Value
    fromlist = Range("B6:B370")

    fromlist.Find(what:=thedate, after:=Range("B5"), LookIn:=xlValue, _
       lookat:=xlWhole, searchorder:=xlByRows, SearchDirection:=xlNext, _
       MatchCase:=False).Select

End Sub

Upvotes: 0

Views: 81

Answers (2)

ahmad
ahmad

Reputation: 21

Your suggestion helped me to find the code for what I want.

sub whatever()

Cells.Find(What:=Worksheets("main").Range("E2"),After:=ActiveCell, _
LookIn:=xlValues,LookAt:=xlWhole,SearchOrder:=xlByRows,SearchDirection:=xlNext, _
MatchCase:=True,SearchFormat:=False).Offset(1, 0).Activate

End Sub

Without Dim and Set it works perfectly.

Upvotes: 0

Skin
Skin

Reputation: 11197

First and foremost, fromlist = Range("B6:B370") needs to have SET in front of it.

Set fromlist = Range("B6:B370")

fromlist is a range, when you try and set that variable, it returns an object and therefore needs SET ... you need this at a minimum and that should eliminate the error you're seeing.

A tip though, you should do more work around your .Find approach. Again, it returns an object and if nothing is found, you'll get an error. You should check for that.

Upvotes: 1

Related Questions