function need to return value and update one cell value
 

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

function need to return value and update one cell value
 

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" wrote:

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

function need to return value and update one cell value
 

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

"Patrick Molloy" wrote in message
...
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" wrote:

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

function need to return value and update one cell value
 

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


"Patrick Molloy" wrote in message
...
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" wrote:

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