User Defined Function that reads data from another woorksheet within the same workbook

Question

This is the structure of my workbook.

One sheet called "export" has all the roomKeys per hotel (they can be repeated) (2k lines).

One sheet called "delete" has all the roomkeys to be deleted (100 lines).

What I want is from sheet "delete" create a function where you pass as a parameter the "roomkey" and it will tell you how many times that value is repeated in the column roomkey from the "export" sheet.

My idea is to filter by roomkey and then count how many lines in the selection and return that number. I think I can't call another worksheet inside a function.

Public Function HowManyRatePlansPerRoom(Roomkey As String) As Integer

Dim SheetName As String
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim LastRow As Integer
Dim Rowz As Integer

On Error GoTo errorHandler

'Application.ScreenUpdating = True

Set ws1 = Sheets("Export")
ws1.Activate
ws1.Cells(1, 1).Select

'Find the last row
LastRow = ws1.Range("A1").CurrentRegion.Rows.Count

'select the data set
ws1.Range("A1:BI" & CStr(LastRow)).Select

'filter table by roomkey
Selection.AutoFilter Field:=1, Criteria1:=Roomkey

'this counts the data in the filter and -1 means minus the header
Rowz = ws1.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1

HowManyRatePlansPerRoom = Rowz

errorHandler:
    MsgBox "The following error occurred: " & Err.Description
    Err.Clear

End Function

The context for that idea is if the roomkey is 5 times repeated on "export" and I have 5 "roomkeys" on "delete" I need to delete that room. On the other hand if I have 5 roomkeys on "delete" but only two on "export" I must leave the room.

Accessing data in other sheets works just fine in a UDF. You just can't perform UI actions on them. See [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/4088852). — Comintern, Feb 16 '19 at 02:51

score 0 · Answer 1 · answered Feb 16 '19 at 02:52

Try this:

Public Function Roomkey_Count(sRoomkey As String) As Byte
Dim bOutput As Byte
Dim rg As Range, lRow As Long

    With ThisWorkbook.Sheets("Export")
        lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set rg = .Cells(1).Resize(lRow)
    End With
    bOutput = WorksheetFunction.CountIf(rg, sRoomkey)

    Roomkey_Count = bOutput

    End Function

User Defined Function that reads data from another woorksheet within the same workbook

1 Answers1