Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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.C ount, "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
|
|||
|
|||
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" wrote in message ... 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.C ount, "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
|
|||
|
|||
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.C ount, "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
|
|||
|
|||
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" wrote in message ... 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.C ount, "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
|
|||
|
|||
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" wrote in message ... 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.C ount, "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
|
|||
|
|||
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, 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, 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
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|