Help with VBA Code
 

Hello,

I was looking for some help with a vba example. I think what I want is
easy, I am just real new at this and could use the help, both because i
need the functionality, and second as an example for me to learn from.

From the spreadsheet I want to call a function counthours in which i
pass in a range of cells.

What i want the vba code to do is for each cell, check if it is
formatted as a date. if it is, add the contents of the cell one row
below to the tally.

Would this be complicated code to come up with?

thanks,
Scott

Help with VBA Code
 

One way:

Option Explicit
Function myDateSum(rng As Range) As Double

Application.Volatile True

Dim myCell As Range
Dim myTotal As Double

myTotal = 0
For Each myCell In rng.Cells
If IsDate(myCell.Value) Then
If IsNumeric(myCell.Offset(1, 0).Value) Then
myTotal = myTotal + myCell.Offset(1, 0).Value
End If
End If
Next myCell

myDateSum = myTotal

End Function

And then use a formula like:
=myDateSum(A1:A10)
or
=mydatesum((a1,a3,a5,a7,a9))
(note the inside ()'s. They are required.)

Another way:

Option Explicit
Function myDateSum2(ParamArray myParms()) As Double

Application.Volatile True

Dim myCell As Range
Dim myElement As Variant
Dim myTotal As Double

myTotal = 0
For Each myElement In myParms
If TypeOf myElement Is Range Then
For Each myCell In myElement.Cells
If IsDate(myCell.Value) Then
If IsNumeric(myCell.Offset(1, 0).Value) Then
myTotal = myTotal + myCell.Offset(1, 0).Value
End If
End If
Next myCell
End If
Next myElement

myDateSum2 = myTotal

End Function

Then you could use a formula like:
=mydatesum(a1:d1,e1,f1,h1)

Notice that both these UDFs have an "application.volatile true" line. Changing
the format of a cell isn't enough to make excel recalculate. So your function
may not be accurate/trustworthy until you force a recalculation.

And this is a problem on two levels. One because it relies on the format of the
top cell. And the other problem is that excel doesn't know to recalculate if
the bottom cell changes. We didn't pass those ranges to the UDF.

wrote:

Hello,

I was looking for some help with a vba example. I think what I want is
easy, I am just real new at this and could use the help, both because i
need the functionality, and second as an example for me to learn from.

From the spreadsheet I want to call a function counthours in which i
pass in a range of cells.

What i want the vba code to do is for each cell, check if it is
formatted as a date. if it is, add the contents of the cell one row
below to the tally.

Would this be complicated code to come up with?

thanks,
Scott

--

Dave Peterson

Help with VBA Code
 

Dave,

Thank you very much! This is really great

Scott

Dave Peterson wrote:
One way:

Option Explicit
Function myDateSum(rng As Range) As Double

Application.Volatile True

Dim myCell As Range
Dim myTotal As Double

myTotal = 0
For Each myCell In rng.Cells
If IsDate(myCell.Value) Then
If IsNumeric(myCell.Offset(1, 0).Value) Then
myTotal = myTotal + myCell.Offset(1, 0).Value
End If
End If
Next myCell

myDateSum = myTotal

End Function

And then use a formula like:
=myDateSum(A1:A10)
or
=mydatesum((a1,a3,a5,a7,a9))
(note the inside ()'s. They are required.)

Another way:

Option Explicit
Function myDateSum2(ParamArray myParms()) As Double

Application.Volatile True

Dim myCell As Range
Dim myElement As Variant
Dim myTotal As Double

myTotal = 0
For Each myElement In myParms
If TypeOf myElement Is Range Then
For Each myCell In myElement.Cells
If IsDate(myCell.Value) Then
If IsNumeric(myCell.Offset(1, 0).Value) Then
myTotal = myTotal + myCell.Offset(1, 0).Value
End If
End If
Next myCell
End If
Next myElement

myDateSum2 = myTotal

End Function

Then you could use a formula like:
=mydatesum(a1:d1,e1,f1,h1)

Notice that both these UDFs have an "application.volatile true" line. Changing
the format of a cell isn't enough to make excel recalculate. So your function
may not be accurate/trustworthy until you force a recalculation.

And this is a problem on two levels. One because it relies on the format of the
top cell. And the other problem is that excel doesn't know to recalculate if
the bottom cell changes. We didn't pass those ranges to the UDF.

wrote:

Hello,

I was looking for some help with a vba example. I think what I want is
easy, I am just real new at this and could use the help, both because i
need the functionality, and second as an example for me to learn from.

From the spreadsheet I want to call a function counthours in which i
pass in a range of cells.

What i want the vba code to do is for each cell, check if it is
formatted as a date. if it is, add the contents of the cell one row
below to the tally.

Would this be complicated code to come up with?

thanks,
Scott

--

Dave Peterson