Function Problem.... Return Value

[Schrades]

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

 

[Trevor Shuttleworth]

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

 

[James S]

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

 

[Dianne]

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.