VBA public function returns 0 when random cell value is entered

0
votes

I'm using a public function to look up the last value in a range on a different worksheet.

The worksheet name consists of the target and batch (based on cell value entered by user)

 Public Function STlastvalue(target, batch, rng)
 Application.Volatile

 STlastvalue = Worksheets(target & " " & batch).Range("J" & WorksheetFunction.CountIf(ActiveSheet.Range(rng.Address), "<>" & "*")).Value
 End Function

the function works fine but when i change a random cell value in the sheet the function is used in, the function returns 0. When i change a random cell value in the targeted sheet the right value is returned.

What causes this and is it fixable?

Thanks in advance!

1
vba

1 Answers

0
votes

There's an Activesheet after countif!

Nevermind folks!