Error in calling function

[suddenflash]

Hi!

I wrote a little code. Because repeated action which is needed I decided to wrtite a function komponendid. I have not found the right way to call a user defined function.

But the code itself works ...

[VBA]Sub otsibrh()

Dim rhsheet As Worksheet
Dim muutuja As Worksheet
Dim algmaterjal As Worksheet
Dim i1 As Integer
Dim j1 As Integer
Dim i As Integer
Dim otsi As Range
Dim rida As String
Dim Cat1
i = 1
j = 1
Set rhsheet = Sheets("Rhsheet")
Set algmaterjal = Sheets("algmaterjal")

' primary loop for searshing necessary letter combo
End Sub
Function komponendid(rida As String, muutuja As Worksheet)

Do While algmaterjal.Cells(i, 2) <> 0


rida = " Rh"

Set otsi = ActiveSheet.Cells(i, 2).Find(rida, MatchCase:=False)

If Not otsi Is Nothing Then
algmaterjal.Cells(i, 10) = "leidsin"

Cat1 = algmaterjal.Rows(i).Copy
muutuja.Rows(j).Insert Shift:=xlDown ', CopyOrigin:=xlFormatFromLeftOrAbove

j = j + 1
End If

i = i + 1
Loop ' end of primary loop
End Function

End Function

Sub programm()

konponendid(" Rh",rhsheet)

End Sub[/VBA]


komponendid shos an error massage

Many thanks ahead

Suddenflash

[Bob Phillips]

[VBA]Sub programm()

Call konponendid(" Rh", rhsheet)

End Sub
[/VBA]

[Teeroy]

Hi suddenflash,

You usually use a function where you want to return a result

i.e.
result = konponendid(" Rh", rhsheet)

where konponendid is a variable within the function.

Here's some pointers about your code:

The way your code is written you should probably use a sub rather than a function then call it as XLD has posted.
You are using objects within the function that aren't passed to the function such as "algmaterjal" which can cause problems that can be difficult to debug. You've passed the variable rida to the function, then redefined it within the function. In this case you wouldn't see a problem but if you passed a test a value other than " Rh" then you'd get an unusual response.

So your code in a cleaner form would be:
[vba]
Sub otsibrh()


Dim i1 As Integer
Dim j1 As Integer





' primary loop for searshing necessary letter combo
End Sub
Sub komponendid(rida As String)

Dim rhsheet As Worksheet
Dim muutuja As Worksheet
Dim algmaterjal As Worksheet

Dim otsi As Range

Dim i As Integer
Dim j As Integer

Dim Cat1 As Variant

Set rhsheet = Sheets("Rhsheet")
Set algmaterjal = Sheets("algmaterjal")
Set muutuja = Sheets("muutuja")

i = 1
j = 1

Do While algmaterjal.Cells(i, 2) <> 0

Set otsi = ActiveSheet.Cells(i, 2).Find(rida, MatchCase:=False)

If Not otsi Is Nothing Then
algmaterjal.Cells(i, 10) = "leidsin"

Cat1 = algmaterjal.Rows(i).Copy
muutuja.Rows(j).Insert Shift:=xlDown ', CopyOrigin:=xlFormatFromLeftOrAbove

j = j + 1
End If

i = i + 1
Loop ' end of primary loop
End Sub


Sub programm()

Call komponendid(" Rh")

End Sub[/vba]
I'd also recommend using a sheet name rather than ActiveSheet in the komponendid sub when setting "otsi" otherwise your find range can be dependent on what sheet you were on when you activated the procedure.

I didn't test the operation of the code as there was no test .xls supplied but good luck with the coding.

[suddenflash]

I will have to use despite Rh at least 8 more strings. So I think I have to redo may code for user function compability

Thanks for advice

[snb]

It looks as if you are reinventing autofilter.
Did you test autofilter on your material ?