How do I use a UDF?

[Guest]

A udf was made for me and I'm positive that it works, however when I try to
use it I get a "syntax error" and it highlights the first line of the code.
I've tried copying and pasting multiple UDF's and get the same result... what
am I doing wrong? Just to clarify I've included all of the information I can
think of below:

Here is the udf:

Function GetTotals(Source As String, Resource As Range, MatchDate As Range)
Dim i As Long
Dim iLastrow As Long
Dim iStart As Long
Dim iEnd As Long
Dim tmp

iLastrow = Worksheets(Source).Cells(Worksheets(Source).Rows.Count,
"A").End(xlUp).Row
On Error Resume Next
iStart = Application.Match(Resource.Value,
Worksheets(Source).Range("B:B"), 0)
If iStart > 0 Then
iEnd = Application.Match("Agent:", Worksheets(Source).Range("A" &
iStart + 1 & ":A" & Rows.Count), 0) + iStart
If iEnd = 0 Then
iEnd = iLastrow
End If
On Error GoTo 0
For i = iStart To iEnd
If Worksheets(Source).Cells(i, "A").Value = MatchDate.Value Then
tmp = CDate(Worksheets(Source).Cells(i, "G").Value)
End If

Here is the function I'm entering into B2:
=GetTotals("Sheet1",$A2,B$1)

The error pops up when I enter the above function into the spreadsheet.
Any help would be much appreciated!

 

[Peo Sjoblom]

Your function is not complete, are you sure that is all you have?

--
Regards,

Peo Sjoblom

(No private emails please)

 

[Guest]

Oops, the full function is the following (I made this mistake on the post,
not in my attempt to use it in excel so I'm still getting a syntax error)

Function GetTotals(Source As String, Resource As Range, MatchDate As Range)
Dim i As Long
Dim iLastrow As Long
Dim iStart As Long
Dim iEnd As Long
Dim tmp

iLastrow = Worksheets(Source).Cells(Worksheets(Source).Rows.Count,
"A").End(xlUp).Row
On Error Resume Next
iStart = Application.Match(Resource.Value,
Worksheets(Source).Range("B:B"), 0)
If iStart > 0 Then
iEnd = Application.Match("Agent:", Worksheets(Source).Range("A" &
iStart + 1 & ":A" & Rows.Count), 0) + iStart
If iEnd = 0 Then
iEnd = iLastrow
End If
On Error GoTo 0
For i = iStart To iEnd
If Worksheets(Source).Cells(i, "A").Value = MatchDate.Value Then
tmp = CDate(Worksheets(Source).Cells(i, "G").Value)
End If
Next i
End If
GetTotals = tmp

End Function

 

[Chip Pearson]

You are missing an End If and a Next statement. Properly indent
your code and you'll see exactly where to put these lines of
code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

Unfortunately I don't know anything about visual basic, this was made for me
by someone else (who's contact information I don't have). Does anyone know
where I should add the end statments that I'm missing? I definitely
appreciate everyones help!

~ Dorn

 

[Max]

Unfortunately I don't know anything about visual basic,
this was made for me
by someone else (who's contact information I don't have).

That person was Bob Phillips
re his response to your post in .programming, re: http://tinyurl.com/9ogeq

(You should have followed through by responding to Bob
over in your .prog post.)

Does anyone know where I should add
the end statments that I'm missing?

Better still, how about a sample implementation ? :
http://cjoint.com/?lojn2DftiP
Dorn_prog.xls

Here's what I did to implement
the UDF GetTotals by Bob Phillips

In Excel,
Press Alt+F11 to go to VBE
Click Insert > Module
Paste Bob's UDF*
into the code window on the right
(*corrected Bob's UDF lines for the dreaded
"inevitable" line breaks / wraps in the sample file <g>)

Press Alt+Q to get back to Excel

Then in Sheet2, as Bob says in his response:
" enter =GetTotals("Sheet1",$A2,B$1) in b2 and copy over (copy across & fill
down)"

Format B2 as Time: "13:30:55" first
before you copy across and fill down
-------

Btw, I'll take it that my formulae suggestion to your other similar post in
..worksheet.functions, re:
http://tinyurl.com/ahvyu
somehow didn't quite make the final cut for you
(You didn't feedback further over there)

 

[Max]

From your reply to Peo in the other branch
Think you were hit by the dreaded "inevitable" line breaks / wraps
when you copy > pasted Bob's UDF (appear as red lines in VBE).
These breaks ave been corrected in the sample implementation.

 

[David McRitchie]

In the future when something fails can you describe what you do see
and why you know it fails.

If you obtain code from a Google groups search, it is safest to go
to the original posting instead of using the HTML presentation.
There are lots of things that can get messed up by Google and
unfortunately some things involving @-signs are deliberately messed up.
See where it says options or original above the presentation display.

Anyway now you know about red syntax errors, due to line wrapping
which is the most common single error you will encounter in copying from
a newsgroup thread.

Getting Started with Macros and User Defined Functions
http://www.mvps.org/dmcritchie/excel/getstarted.htm