loop next again

[rplim2016]

hello mr. excel users,

just trying to create a simple add function but below code is missing

Public Function myAdd(num1 As Range) As Long
Dim temp_total As Range
Dim final_total As Long

For Each temp_total In num1
If IsNumeric(temp_total.Value) = False Then ((("MISSING CODE"))
GoTo nextloop
End If

final_total = final_total + temp_total.Value
Next temp_total

myAdd = final_total


End Function

above red font, should do Next temp_total, how could it work?



thanks!

 

[DanteAmor]

Try:

Public Function myAdd(num1 As Range) As Long
    Dim temp_total As Range
    Dim final_total As Long
    
    For Each temp_total In num1
        If IsNumeric(temp_total.Value) Then
            final_total = final_total + temp_total.Value
        End If
    Next temp_total
    myAdd = final_total
End Function

 

[rplim2016]

Thanks it works!

 

[Rick Rothstein]

You have made your function much more complicated than necessary. Here is a simpler looping version (note I have changed the function's argument and variable names to something more self descriptive than the names you used (note how the For Each statement reads like a meaningful sentence now)...

Public Function myAdd(RangeToAdd As Range) As Long
  Dim Cell As Range
  For Each Cell In RangeToAdd
    myAdd = myAdd + Val(Cell.Value)
  Next
End Function

Note that the Val function returns the leading number from text values that start with a numeric value (as well as the actual number for real numeric values)... if the text does not start with a number, Val returns 0; hence, no separate test is needed (so long as your text values will never start with a number; for example 3M for which Val would return 3).

 

[Rick Rothstein]

And, just so you know, your function can be done without a loop using just a single line of code...

Public Function myAdd(RangeToAdd As Range) As Long
  myAdd = WorksheetFunction.Sum(RangeToAdd)
End Function

 

[DanteAmor]

Thanks it works!

The Sum function already exists, but I guess you're trying to learn how to cycle a function and perform an operation. Good job. That's how you continue to learn.

I'm glad to help you. Thanks for the feedback.

 

[rplim2016]

And, just so you know, your function can be done without a loop using just a single line of code...

Public Function myAdd(RangeToAdd As Range) As Long
  myAdd = WorksheetFunction.Sum(RangeToAdd)
End Function

Hey Rick, thanks for making it easy, I can do so much UDF now with that Worksheetfunction code. cheers.

 

[rplim2016]

You have made your function much more complicated than necessary. Here is a simpler looping version (note I have changed the function's argument and variable names to something more self descriptive than the names you used (note how the For Each statement reads like a meaningful sentence now)...

Public Function myAdd(RangeToAdd As Range) As Long
  Dim Cell As Range
  For Each Cell In RangeToAdd
    myAdd = myAdd + Val(Cell.Value)
  Next
End Function

Note that the Val function returns the leading number from text values that start with a numeric value (as well as the actual number for real numeric values)... if the text does not start with a number, Val returns 0; hence, no separate test is needed (so long as your text values will never start with a number; for example 3M for which Val would return 3).

i appreciate your effort explaining this to me. I just replicate the existing UDF that I already have but i never thought that there is an easy and less complicated way to make UDF.


By any chance, do you know how to reiterate a Loop, For, Do? without doing adding goto function "Handler:"

sample

For i = 1 to 10
if activecell.value = "" then *code is to restart the next loop*

Code continues

Next i