How do I use a UDF?

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!

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

--
Regards,

Peo Sjoblom

(No private emails please)


"Dorn" <[email protected]> wrote in message
news:[email protected]...
>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!

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

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


"Dorn" <[email protected]> wrote in message
news:[email protected]...
>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!

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

"Chip Pearson" wrote:

> 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
>
>
> "Dorn" <[email protected]> wrote in message
> news:[email protected]...
> >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!
>
>
>

"Dorn" wrote
> 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)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

From your reply to Peo in the other branch
> > ... still getting a syntax error ..

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.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

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

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Max" <[email protected]> wrote in message news:[email protected]...
> From your reply to Peo in the other branch
> > > ... still getting a syntax error ..
>
> 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.
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
>
>