CODEWITHSUNDEEP
excelvbafinduser-defined-functions
yannk
yannk

Reputation: 51

Using the .find method in a user defined function

I want to create a function that takes two input variable InputVal, which will always be numeric and RefCell, which will always be a word. The function should then use the word in RefCell to search for a certain cell in another Sheet of the workbook. Then move four spaces to the right from the “found cell” and take what is in that’s inside (also always numeric) and multiply it with the InputVal and return the result.

Function FindAndCalc(InputVal As Range, RefCell As Range)

    Dim FindCell As Range, NewCell As Range

    FindCell = Worksheets("Sheet1").Cells.Find(what:=RefCell.Value)
    NewCell = Worksheets("Sheet1").FindCell.Offset(0, 4)

    FindAndCalc = NewCell.Value * InputVal.Value

End Function

Currently it simply returns me an error “#VALUE!”, while it should return a numeric value.

Upvotes: 1

Views: 52

Answers (1)

Kubie
Kubie

Reputation: 1571

You might have been missing some Set's.. also just added some logic

Function FindAndCalc(InputVal As Range, RefCell As Range)

    Dim FindCell As Range, NewCell As Range

    Set FindCell = Worksheets("Sheet1").Cells.Find(RefCell.Value)
    If Not FindCell is Nothing Then
        Set NewCell = FindCell.Offset(0, 4)
    End If

    FindAndCalc = NewCell.Value * InputVal.Value

End Function

Upvotes: 3

Related Questions