CODEWITHSUNDEEP
excelvbaruntime-erroruserformworksheet
open0121
open0121

Reputation: 105

VBA userform: application-defined or object-defined error

I copied a code that worked well in VBA module to UserForm since I need to combine some actions defined in userform. Now I got stuck when executing the following part, with error message 'Application-defined or object-defined error'

Sub test()
    Dim Myworkbook As workbook
    Dim Myid As Variant

    Set Myworkbook = ThisWorkbook

    Myid=1

    Myref=FinRef(Myworkbook, "Ref", Myid)
End Sub

    Function FinRef(myfile As Workbook, InputSheet As String, Ref_ID As Variant) 

        Dim I, k, LinkFrom, Description As Integer
        Dim Linkdescrip As String

        FinRef = ""

        LinkFrom = 1           
        Description = 8             
        Linkdescrip = 7             
        For I = 2 To 3000
            k = myfile.Sheets(InputSheet).Cells(I, LinkFrom)

            If k = Ref_ID Then
                FinRef = FinRef & myfile.Sheets(InputSheet).Cells(I, Linkdescrip) & myfile.Sheets(InputSheet).Cells(I, Description)
            End If
        Next I

    End Function

Though there are lots of ppl who experienced the same problem, I couldn't find solution fit for me. Any tips?

Thanks a lot!

Upvotes: 0

Views: 798

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19847

As John said - your code as posted doesn't produce the error you're reporting.

I think the problem with your code is:
Dim Linkdescrip As String should be Dim Linkdescrip As Long.

Turned into working code:
?thisworkbook.Worksheets("Ref").cells(2,"7") returns your error.
?thisworkbook.Worksheets("Ref").cells(2,7) returns the value from range G2.

Having said that, I think I can see what you're after:
Column A of the Ref sheet is populated with numbers, you want to search that column for a specific number and if it's found find return the values from columns G & H on that row.

This code will find the numeric value 1 within column A of the sheet named Ref in the workbook that contains the code (ThisWorkbook). It will then return the values from columns G:H of that row, or it will return an empty string. 

Sub Test()

    Dim MyRef As String

    MyRef = FinRef(ThisWorkbook.Worksheets("Ref"), 1)

End Sub

Public Function FinRef(InputSheet As Worksheet, Ref_ID As Long) As String

    Dim rFound As Range

    With InputSheet
        Set rFound = .Columns(1).Find( _
            What:=Ref_ID, After:=.Cells(1, 1), _
            LookIn:=xlValues, LookAt:=xlWhole, _
            SearchOrder:=xlByRows, SearchDirection:=xlNext)

        If Not rFound Is Nothing Then
            FinRef = rFound.Offset(, 6) & rFound.Offset(, 7)
        End If

    End With

End Function

Upvotes: 1

Related Questions