+ Reply to Thread
Results 1 to 8 of 8

How do I use a UDF?

  1. #1
    Dorn
    Guest

    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!

  2. #2
    Peo Sjoblom
    Guest

    Re: How do I use a UDF?

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

    --
    Regards,

    Peo Sjoblom

    (No private emails please)


    "Dorn" <Dorn@discussions.microsoft.com> wrote in message
    news:50DB6E8F-F8E5-49CE-B082-BB89568EAC16@microsoft.com...
    >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!



  3. #3
    Dorn
    Guest

    Re: How do I use a UDF?

    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


  4. #4
    Chip Pearson
    Guest

    Re: How do I use a UDF?

    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" <Dorn@discussions.microsoft.com> wrote in message
    news:50DB6E8F-F8E5-49CE-B082-BB89568EAC16@microsoft.com...
    >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!




  5. #5
    Dorn
    Guest

    Re: How do I use a UDF?

    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" <Dorn@discussions.microsoft.com> wrote in message
    > news:50DB6E8F-F8E5-49CE-B082-BB89568EAC16@microsoft.com...
    > >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!

    >
    >
    >


  6. #6
    Max
    Guest

    Re: How do I use a UDF?

    "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
    --



  7. #7
    Max
    Guest

    Re: How do I use a UDF?

    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
    --



  8. #8
    David McRitchie
    Guest

    Re: How do I use a UDF?

    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" <demechanik@yahoo.com> wrote in message news:O1GYE1P6FHA.1720@TK2MSFTNGP09.phx.gbl...
    > 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
    > --
    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1