how to using Function update cell value

[moon]

Hi Reader
how to using Function update cell value ?

e.g.Update_cell work , but Function xHoliday now work.


Function xHoliday(HolidayResult As Range, loDate As Date, ByVal loOffset As
Long) As Date
'~~ 2005/08/04 eric.leung
'~~ 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

Sub Update_cell()
Dim holidaySheet As Variant
Set holidaySheet = Sheets("Holiday")
holidaySheet.Range("i2").Value = "TTT"

End Sub

 

[Norman Jones]

Hi Eric,

If the Holiday sheet included the requisite date in the A1:A1000 range and
the word 'NO' was present in the a determined column B range, your function
works for me and returns a value to cell F2.

If however, either of these conditions is not met, when i reaches 0 (as it
will in either loop), the procedure will fail, with a 1004 run-time, error
as neither A0 or B0 represent valid addressses

I have not otherwise sought to undestand the logic of your function.

BTW, why do you dim holidaySheet as Variant rather than as Worksheet?

 

[Niek Otten]

A Function, when called from a worksheet (directly or indirectly) can not
change anything in a worksheet. It can just return a value to replace its
call.
A Sub can, and functions called from VBA can, too.

--
Kind regards,

Niek Otten

Microsoft MVP - Excel