CODEWITHSUNDEEP
vbaexcel
Karl Goddard
Karl Goddard

Reputation: 3

Searching for a value from a specific cell on another sheet

I am using userforms to input data, at a certain point part of the data is copied to one of the sheets in the workbook.

My code then needs to use one of the values in the sheet to check if this value apears on another sheet, if it does it copies values linked to that value to the original sheet and then populates the userform so that further info can be captured.

If I activate on error resume next everything works except the tab function stops working on the userform, if I run it without on error resume next, I get run-time error:

'91' Object variable or with block variable not set.

How do i fix this?

Sub Find_7_day()

        Dim vfind

        Dim rng As Range

        Sheets("Test Data").Select
        Sheets("Test Data").Range("$E$3").Select
        vfind = ActiveCell

        'On Error Resume Next
        Call Sheet
        Set rng = Cells.Find(What:=vfind, After:=ActiveCell,        LookIn:=xlValues, _
             LookAt:=xlWhole, SearchOrder:=xlByRows,     SearchDirection:=xlNext, _
             MatchCase:=True, SearchFormat:=False).Activate

       If ActiveCell = vfind Then
            Call Old_7_day
            Call Form_7_day_fill
        Else
            Sheets("Test Data").Select
        End If


End Sub

Upvotes: 0

Views: 79

Answers (1)

warner_sc
warner_sc

Reputation: 848

You can't declare and .Activate a variable range at the same time:

    Dim rng As Range

    Set rng = Cells.Find(What:=vfind, After:=ActiveCell, LookIn:=xlValues, _
         LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
         MatchCase:=True, SearchFormat:=False).Activate

suggestion, avoid using .Select and Activate, explanation here How to Avoid the Select Method in VBA & Why

Code:

Sub Find_7_day()

        Dim vfind As String
        Dim rng As Range

        vfind = Sheets("Test Data").Range("$E$3").Value

        Call Sheet

        Set rng = Cells.Find(What:=vfind, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=True, _
        SearchFormat:=False)

        If rng Is Nothing Then
        MsgBox vfind & " " & "dont exist"
        Exit Sub
        End If

       If rng.Value = vfind Then
            Call Old_7_day
            Call Form_7_day_fill
        Else
            Sheets("Test Data").Select
        End If

        Exit Sub

End Sub

Upvotes: 1

Related Questions