function need to return value and update one cell value

[moon]

Below function can return non-holiday date, I want to return how many
holiday found.
Variable HolidayResult to be update as long integer. But , I try can not
able to return the non-holiday by coding HolidayResult.Address.Value =
cntHoliday

HolidayResult. Address is "$F$4' in my test case.

Holiday Sheet format
Column A - Date value
Column B - Yes = Holiday, No = Non-Holiday

Function xHoliday(HolidayResult As Range, loDate As Date, ByVal loOffset As
Long) As Date
'~~ if input value no changed, this function will not call.
Dim HolidaySheet As Variant
Dim i As Long
Dim found As Boolean
Dim cntHoliday As Long
cntHoliday = 0
found = False
xHoliday = loDate - loOffset
Set HolidaySheet = Sheets("Holiday")
i = 1001
Do

If HolidaySheet.Range("A" & i) = xHoliday Then
found = True
Else
i = i - 1
End If

Loop Until found = True

'~~ When return date still holiday, skip it , until Holiday = "no"
found = False
Do

If Trim(UCase(HolidaySheet.Range("B" & i))) = "NO" Then
found = True
Else
xHoliday = xHoliday - 1
cntHoliday = cntHoliday + 1
i = i - 1
End If

Loop Until found = True
' MsgBox HolidayResult.Address
HolidayResult.Address.Value = cntHoliday
End Function

 

[Patrick Molloy]

you really don't need code for this.
You can use MATCH() and COUNTIF() functions quite easily

eg for all hilidays
=COUNTIF(holiday!B:B,"Yes")

if A7 has the latest date, and you want all hoilday up and including that date
=COUNTIF(OFFSET(holiday!B1,0,0,MATCH(A7,holiday!A:A,FALSE)),"Yes")

Other stuff
if you want to set a variable to a cell on a sheet
DIM wsHoliday as Worksheet
DIM cell as Range
SET wsHoliday = Worksheets("Holiday")
SET cell = Worksheets("Main").Range("F4")

you don't need to use 'address' at all
cell.Value= 10


---
with functions, you return a value to the caller
cell = Myfuncttion(par1,par2)

where

function MyFunction(par1 as long, par2 as long) as Date
MyFunction = Date + par2 - par1
End Function

 

[moon]

Thank, I will test using excel function.

But below code still have error , error Number 1004 , Application-defined or
object-defined error.
It is seem that function can not allow update cells value.

I want input formula in a cell, other cell also update.

Function xHoliday(HolidayResult As Range, loDate As Date, ByVal loOffset As
Long) As Date

'~~ if input value no changed, this function will not call.
On Error GoTo xerr
Dim holidaySheet As Variant
Dim i As Long
Dim found As Boolean
Dim cntHoliday As Long
Dim cell As Range
cntHoliday = 0
found = False

xHoliday = loDate - loOffset
Set holidaySheet = Sheets("Holiday")
i = 1001
Do

If holidaySheet.Range("A" & i) = xHoliday Then
found = True
Else
i = i - 1
End If

Loop Until found = True

'~~ When return date still holiday, skip it , until Holiday = "no"
found = False
Do

If Trim(UCase(holidaySheet.Range("B" & i))) = "NO" Then
found = True
Else
xHoliday = xHoliday - 1
cntHoliday = cntHoliday + 1
i = i - 1
End If

Loop Until found = True
' Call Update_cell(Str(cntHoliday))
Set cell = Worksheets("Holiday").Range("F2")
cell.Value = 10
Exit Function
xerr:
MsgBox Err.Number & " " & Err.Description

End Function

 

[moon]

Hi Oartick
It is ok using sub to update cell value, but function seem not.
Sub Update_cell()
Dim holidaySheet As Variant
Set holidaySheet = Sheets("Holiday")
holidaySheet.Range("i2").Value = "TTT"

End Sub