Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Problem.... Return Value
I have created a function in a seperate module file that works fine if
I increment through it. The total variable is incremented accuratley, but I can't get it to return the value. I have a feeling it is something simple, but am stumped. The function starts in cell A12 looks for a matching string than reads the value in the cell 5 columns over. I keep a running total and want the total displayed in the cell where I call the function from. I was also battling circular reference problems, but I think I fixed those. Here is the code. Public Function RunTotalKia1() Dim Total Range("A12").Select Do Until ActiveCell.Value = "" If ActiveCell.Value = "Kia 1" Then Total = Total + ActiveCell.Offset(0, 5).Value End If ActiveCell.Offset(1, 0).Activate Loop RunTotalKia1 = Total End Function Anyone have any ideas? Thanks in advance. -Adam |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Problem.... Return Value
Adam
maybe this will work for you: Function RunTotalKia1() Dim LastRow As Long LastRow = Range("A12").End(xlDown).Row RunTotalKia1 = Application.WorksheetFunction.SumIf _ (Range("A12:A" & LastRow), _ "Kia 1", _ Range("F12:F" & LastRow)) End Function Regards Trevor "Schrades" wrote in message om... I have created a function in a seperate module file that works fine if I increment through it. The total variable is incremented accuratley, but I can't get it to return the value. I have a feeling it is something simple, but am stumped. The function starts in cell A12 looks for a matching string than reads the value in the cell 5 columns over. I keep a running total and want the total displayed in the cell where I call the function from. I was also battling circular reference problems, but I think I fixed those. Here is the code. Public Function RunTotalKia1() Dim Total Range("A12").Select Do Until ActiveCell.Value = "" If ActiveCell.Value = "Kia 1" Then Total = Total + ActiveCell.Offset(0, 5).Value End If ActiveCell.Offset(1, 0).Activate Loop RunTotalKia1 = Total End Function Anyone have any ideas? Thanks in advance. -Adam |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Problem.... Return Value
Hi Adam,
You can try this too: Function TotalKia1() As Long Dim rng As Range Dim strValue As String Dim lngTotal As Long Set rng = ActiveSheet.Range("A12") strValue = "Kia 1" Do Until IsEmpty(rng) If UCase(rng.Text) = UCase(strValue) Then lngTotal = lngTotal + rng.Offset(0, 5).Value End If Set rng = rng.Offset(1, 0) Loop TotalKia1 = lngTotal End Function Hope that helps. Regards, James S -----Original Message----- I have created a function in a seperate module file that works fine if I increment through it. The total variable is incremented accuratley, but I can't get it to return the value. I have a feeling it is something simple, but am stumped. The function starts in cell A12 looks for a matching string than reads the value in the cell 5 columns over. I keep a running total and want the total displayed in the cell where I call the function from. I was also battling circular reference problems, but I think I fixed those. Here is the code. Public Function RunTotalKia1() Dim Total Range("A12").Select Do Until ActiveCell.Value = "" If ActiveCell.Value = "Kia 1" Then Total = Total + ActiveCell.Offset(0, 5).Value End If ActiveCell.Offset(1, 0).Activate Loop RunTotalKia1 = Total End Function Anyone have any ideas? Thanks in advance. -Adam |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Function Problem.... Return Value
How about another option (which may be overkill) -- here's a function
that will look for any string you want: Public Function RunTotal(strValue As String) As Currency Dim Total As Currency Dim rng As Range Set rng = Range("A12") Do Until rng.Value = "" If rng.Value = strValue Then Total = Total + rng.Offset(0, 5).Value End If Set rng = rng.Offset(1, 0) Loop RunTotal = Total Set rng = Nothing End Function So, in the cell where you want the total to appear, type =RunTotal("Kia 1") Just don't put this formula into column A below row 12, otherwise you'll get a circular reference. -- HTH, Dianne In om, Schrades typed: I have created a function in a seperate module file that works fine if I increment through it. The total variable is incremented accuratley, but I can't get it to return the value. I have a feeling it is something simple, but am stumped. The function starts in cell A12 looks for a matching string than reads the value in the cell 5 columns over. I keep a running total and want the total displayed in the cell where I call the function from. I was also battling circular reference problems, but I think I fixed those. Here is the code. Public Function RunTotalKia1() Dim Total Range("A12").Select Do Until ActiveCell.Value = "" If ActiveCell.Value = "Kia 1" Then Total = Total + ActiveCell.Offset(0, 5).Value End If ActiveCell.Offset(1, 0).Activate Loop RunTotalKia1 = Total End Function Anyone have any ideas? Thanks in advance. -Adam |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Carriage Return Problem | Excel Discussion (Misc queries) | |||
Return value for a function. | Excel Discussion (Misc queries) | |||
Why does this function NOT return a value? | Excel Discussion (Misc queries) | |||
Only do Sum function if not "0", IF"0" then return "0" | Excel Worksheet Functions | |||
Function to return value from each tab | Excel Worksheet Functions |