loop next again

rplim2016

Board Regular
Joined
Jun 28, 2016
Messages
76
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!
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Try:
Code:
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,334
Office Version
  1. 2010
Platform
  1. Windows
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)...
Code:
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).
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,334
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

And, just so you know, your function can be done without a loop using just a single line of code...
Code:
Public Function myAdd(RangeToAdd As Range) As Long
  myAdd = WorksheetFunction.Sum(RangeToAdd)
End Function
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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

Board Regular
Joined
Jun 28, 2016
Messages
76
And, just so you know, your function can be done without a loop using just a single line of code...
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

Board Regular
Joined
Jun 28, 2016
Messages
76
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)...
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,461
Messages
5,528,932
Members
409,847
Latest member
Foster034
Top