Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
Create a newworksheet with VBA code and put VBA code in the new worksheet module | Excel Programming | |||
stubborn Excel crash when editing code with code, one solution | Excel Programming | |||
option buttons run Click code when value is changed via VBA code | Excel Programming |