CODEWITHSUNDEEP
vbaexcelfind
Janine W.
Janine W.

Reputation: 1

Using Find method to search for a string from another worksheet

Perhaps I just haven't used the right search terms, I am still new to VBA, but I just can't find the solution to my problem:

I am trying to find a value (format 'yyyy-ww') from one worksheet in the row of another worksheet, and then select the cell (the next step would then be to then select and copy the respective column, and then paste the values).

I have the following code:

Private Sub Button5_Click()


'Define previous week and the search range

Dim prevwk As Object
Dim SrchRng As Range

Set prevwk = ActiveWorkbook.Worksheets("Values").Range("B1")
Set SrchRng = ActiveWorkbook.Worksheets("DE").Rows(1)


'If previous week is found, select the cell

With SrchRng

Dim prevwkf As Range
Set prevwkf = SrchRng.Find(What:=prevwk)

prevwkf.Select   '<----- Error is here

End Sub

I keep receiving the error message:

'Run-time error '91': Object variable or With block variable not set'.

I have tried many changes but it keeps coming down to this error message.

Many thanks for your help!

Upvotes: 0

Views: 57

Answers (1)

DisplayName
DisplayName

Reputation: 13386

before selecting a cell you have to activate the sheet, just as you would do manually:

SrchRng.parent.Activate
prevwkf.Select

BTW you don't need that With SrchRng, and you could check for actual match found

Private Sub Button5_Click()
    'Define previous week and the search range
    Dim prevwk As Object
    Dim SrchRng As Range

    Set prevwk = ActiveWorkbook.Worksheets("Values").Range("B1")
    Set SrchRng = ActiveWorkbook.Worksheets("DE").Rows(1)

    'If previous week is found, select the cell
    Dim prevwkf As Range
    Set prevwkf = SrchRng.Find(What:=prevwk)
    If Not prevwkf Is Nothing Then ' check you actually found something
        SrchRng.Parent.Activate
        prevwkf.Select '<----- Error is here
    End If
End Sub

Upvotes: 1

Related Questions