VBA userform: application-defined or object-defined error

Question

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!

What procedure are the first 5 lines in? They'd be invalid outside of a procedure. — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Dec 13, 2018 at 10:59
@Dave It's the line ' FinRef = FinRef & myfile.Sheets(InputSheet).Cells(I, Linkdescrip) & myfile.Sheets(InputSheet).Cells(I, Description)' — open0121
– open0121, Commented Dec 13, 2018 at 11:22
@DarrenBartrup-Cook The first 5 lines are defined within a sub procedure and the function is defined outside the sub procedure. — open0121
– open0121, Commented Dec 13, 2018 at 11:24
Dim I, k, LinkFrom, Description As Integer declares 3 variants and only 1 integer. I doubt that was you intent. Also, please post a minimal reproducible example which people can actually run to see your exact error. There seems to be a disconnect between your question and your actual problem. — John Coleman
– John Coleman, Commented Dec 13, 2018 at 11:40

Darren Bartrup-Cook · Accepted Answer · 2018-12-13 12:03:32Z

1

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

edited Dec 13, 2018 at 12:03

answered Dec 13, 2018 at 11:56

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

open0121 Over a year ago

Thanks a lot! I have declared Linkdescrip As Long and it works!

Collectives™ on Stack Overflow

VBA userform: application-defined or object-defined error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related