Excel VBA How to pass range from different sheet to function properly?

Question

I need to work with range of cells from different sheet in Excel VBA. Here is my code:

Function LastDayForClient(client As String, mnth As Integer, dates As range) As String

Dim dtRow As Date
Dim dt As range

dtRow = 0

For Each dt In dates
      ' If not that month that we are looking for - go next
     If month(dt.Value) = mnth Then
       ' If not that client - go next
        If Cells(dt.Row, dt.Column + 1).Value = client Then

          If dtRow = 0 Then
            dtRow = dt.Value
          ElseIf dtRow < dt.Value Then
            dtRow = dt.Value
          End If
        End If
      End If
Next dt

LastDayForClient = Format(dtRow, "dd.mm.yyyy")

End Function

That's how I want to use the formula:

=LastDayForClient("Client_name";5;Diff_sheet!G6:G297)

I guess that I'm using passed Range argument somehow in a wrong way, as I'm getting wrong value when using formula on different sheet. When the range is on the same sheet everything is fine.

why the use of Format, wouldn't month(dt.Value2) work without the need of the extra step? — Scott Craner
Thanks for your remark @ScottCraner! Not sure why I used it that way.. Anyways, what is about the Range? — sys463

Storax Storax · Accepted Answer · 2018-05-21T14:43:09

The issue is the line

If Cells(dt.Row, dt.Column + 1).Value = client Then

Cells refers to the ActiveSheet, so you need to tell Cells to refer to the right sheet.

Dim wks as worksheet
set wks = dates.parent

and then

If wks.Cells(dt.Row, dt.Column + 1).Value = client Then

Excel VBA How to pass range from different sheet to function properly?

1 Answers