CODEWITHSUNDEEP
vbaexcel
chrnit
chrnit

Reputation: 21

vba - properly passing range value to a function

in my application i call a function and pass 4 values to calculate a cell adress:

nettowertadresse = searchAdress(Dateiname, CurrentSheet, "Nettowert", Range("A1:C60"))

function:

Function searchAdress(inputworkbook As String, inputsheet As String, inputsearchtext As String, inputrange As Range) As Range

  With Workbooks(inputworkbook).Sheets(inputsheet).Range(inputrange)
      Set searchAdress = .Find(inputsearchtext, LookIn:=xlValues)
  End With

End Function

now the problem is that i get error 1004 "application defined or object defined error" and i think that maybe the range is not properly passed because the debugger shows no value for the variable "inputrange" when jumping to the function. please give some advice on how to make this function work. thanks.

Upvotes: 1

Views: 1002

Answers (2)

Richard Le Mesurier
Richard Le Mesurier

Reputation: 29713

Your function, searchAddress, declares inputrange as Range. This means that the object inside your function is a Range object.

So you should not be using it as .Range(inputrange). Instead try using this code, which treats it correctly as a Range object:

Function searchAdress(inputworkbook As String, inputsheet As String, inputsearchtext As String, inputrange As Range) As Range

    With Workbooks(inputworkbook).Sheets(inputsheet)
        Set searchAdress = inputrange.Find(inputsearchtext, LookIn:=xlValues)
    End With

End Function

Also note that there is another bug in your code that calls this function. You need to use the keyword Set when assigning the return value to your nettowertadresse variable:

Set nettowertadresse = searchAdress(Dateiname, CurrentSheet, "Nettowert", Range("A1:BA2"))

Otherwise you will experience Run-time error '91', which you mention in your follow-up question.

Upvotes: 3

Goos van den Bekerom
Goos van den Bekerom

Reputation: 1493

you add a Range to a rangeobject in your function, your function looks like this then:

  With Workbooks(inputworkbook).Sheets(inputsheet).Range(Range("A1:C60"))
      Set searchAdress = .Find(inputsearchtext, LookIn:=xlValues)
  End With

You should change this:

Function searchAdress(inputworkbook As String, inputsheet As String, inputsearchtext As String, inputrange As String) As Range

  With Workbooks(inputworkbook).Sheets(inputsheet).Range(inputrange)
      Set searchAdress = .Find(inputsearchtext, LookIn:=xlValues)
  End With

End Function

And then pass this to the function:

nettowertadresse = searchAdress(Dateiname, CurrentSheet, "Nettowert", "A1:C60")

Upvotes: 2

Related Questions