Writting Function using VBA

Hi

I am trying to write a function to return an address but 
instead I get #VALUE!.

Public Function fnd(a, b)
    
    fnd = Range(a).Find(b).Address

End Function


Please help.

Thanks.
0
anonymous (74722)
3/31/2005 11:22:29 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
445 Views

Similar Articles

[PageSpeed] 0

..Find won't work in UDF's called from the worksheet until xl2002.

Depending on the range (a), you could use application.match() through each
column.

If the range is small, you could just loop through the values in that range,
too.

nc wrote:
> 
> Hi
> 
> I am trying to write a function to return an address but
> instead I get #VALUE!.
> 
> Public Function fnd(a, b)
> 
>     fnd = Range(a).Find(b).Address
> 
> End Function
> 
> Please help.
> 
> Thanks.

-- 

Dave Peterson
0
ec357201 (5290)
3/31/2005 12:29:45 PM
Hi Dave

Thanks for your response.

I am using Excel 2003.


>-----Original Message-----
>..Find won't work in UDF's called from the worksheet 
until xl2002.
>
>Depending on the range (a), you could use 
application.match() through each
>column.
>
>If the range is small, you could just loop through the 
values in that range,
>too.
>
>nc wrote:
>> 
>> Hi
>> 
>> I am trying to write a function to return an address 
but
>> instead I get #VALUE!.
>> 
>> Public Function fnd(a, b)
>> 
>>     fnd = Range(a).Find(b).Address
>> 
>> End Function
>> 
>> Please help.
>> 
>> Thanks.
>
>-- 
>
>Dave Peterson
>.
>
0
anonymous (74722)
3/31/2005 12:45:49 PM
This worked ok for me (also xl2003):

Option Explicit
Public Function fnd(a, b)
    Dim FoundCell As Range
    
    Set FoundCell = Range(a).Find(b)
    If FoundCell Is Nothing Then
        fnd = "Not Found"
    Else
        fnd = FoundCell.Address
    End If

End Function

I used this in the cell:
=fnd("B1:B99",A1)

===
I think I'd also specify all the parameters in the .find statement.  .Find
remembers all the ones you previously set--manually or via code.  

nc wrote:
> 
> Hi
> 
> I am trying to write a function to return an address but
> instead I get #VALUE!.
> 
> Public Function fnd(a, b)
> 
>     fnd = Range(a).Find(b).Address
> 
> End Function
> 
> Please help.
> 
> Thanks.

-- 

Dave Peterson
0
ec357201 (5290)
4/1/2005 6:13:59 AM
Reply:

Similar Artilces:

Excel Text Function (Right, Left)
My sheet has columns like this A 1. 63mmx 4kg 2. 75mmx 4kg SWR How can i detect that if a1:a2 is right,3 "swr", that it should be returned 3 otherwise 6. How can i add =right function with conditional formula. Help me Rao Ratan singh Hi =IF(RIGHT(A1,"SWR"),3,6) fill down your column (move cursor over bottom right hand corner of the cell when you see a +, double click) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Rao Ratan Singh" <RaoRatanSingh@discussions.microsoft.com> wrote in message news:D...

How to use count for calls per hour
I am trying to create a pivot table and chart that will show me the # of calls per hour by day for a range of dates Aug 1 - Sept 19. My data looks like this where each line is the date and time of the call: How do I get a count of the # of the calls per hour by day? 8/1/08 12:48 AM 8/1/08 1:53 AM 8/1/08 2:36 AM 8/1/08 3:24 AM 8/1/08 3:37 AM 8/1/08 4:04 AM 8/1/08 4:44 AM 8/1/08 4:55 AM 8/1/08 4:58 AM 8/1/08 7:02 AM 8/1/08 7:28 AM 8/1/08 7:43 AM 8/1/08 7:47 AM 8/1/08 7:56 AM 8/1/08 7:58 AM 8/1/08 8:13 AM 8/1/08 8:33 AM 8/1/08 9:07 AM 8/1/08 10:28 AM 8/1/08 10:38 AM 8/1/08 10:44 AM 8/1/08 ...

How export all mailbox-enabled users from the GAL using Outlook?
Hello, We have a user which, for administrative reasons, needs to export all mailbox-enabled users in the GAL now and then. They just need all the names. Is there a straightforward way for them to do that? We don't want to give them any special permissions and want to avoid server scripting. Ideally, they should be able to export it to a CSV file or any text file. Thanks, - Alan. Alan wrote: > We have a user which, for administrative reasons, needs to export all > mailbox-enabled users in the GAL now and then. They just need all the > names. Is there a straightforward way fo...

VBA charts
Why does Excel 2007 not record ALL comands which I implement while using the macro recorder in making/changing charts? Hi, The macro recorder for the new items in xl2007, charts and shapes, is very incomplete :( Unless you have a previous version to record with you will need to work with the object browser and intellisense in order to manually create your code. Cheers Andy rolando wrote: > Why does Excel 2007 not record ALL comands which I implement while using the > macro recorder in making/changing charts? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

VBA to put a copy of worksheet on the desktop 05-13-10
Hi all, In my workbook XYZ I have a sheet ABC. With a button on sheet DEF I can refresh sheet ABC. When the code finishes it job I want to add the actual date (short European notation dmyy) and time (f.i. 241110 16.31) to the name of the sheet (which becomes ABC 241110 16.31) and after that make a copy of that sheet in a separate workbook and put that workbook as an icon on the desktop of my computer. Is this possible? If so, please help me with the necessary code. Thanks in advance for your assistance. Jack Sons The Netherlands ...

Need Help Filtering a Form with VBA
I have a form whose underlying data comes primarily from "Table1" (for this example). On the form is a drop-down box that displays a field named "Primary OBS" for each record. "Primary OBS" is stored in "Table1". It is an index into another table "Table2" which contains a field named "Team Code". "Team Code" can look like "JD130000" or "JG320000", etc. What I need to do is to filter the records in the form by the first two letters of the "Team Code". In other words, the user ca...

iPhone inbox (using Gmail) and Entourage
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: pop Before I upgraded to Entourage 2008, I was using 2004 with (10.4) on an older iMac. I have an iPhone and use it for email. Previously, my email would come to my iPhone and when I opened Entourage, the emails on my iPhone would disappear. I've upgraded to Snow Leopard and Entourage 2008. Now, my emails don't disappear after I've opened Entourage on my iPhone. I have to manually delete them. I checked the settings and they're all the same. Can anyone help me figure out how...

Can I use an industry standard XSD and ADO.NET / SQLServer to output a conforming XML file
We have tentatively agreed with a supplier to exchange order information. The plan is to use an 'industry standard' order schema definition from say www.xcbl.org. Is there are simple way to use ADO.NET in this scenario. Ideally (naively) I would like to do something along these lines : 1. Create a SQLServer stored proc that returns a dataset (or similar) containing the order details such as : select PurchaseOrderID,ProductID,Qty from PurchaseOrder join PurchaseOrderItem...... 2. In a COM+ object (not entirely relevant to the problem, but this is how it will work), I would expe...

vba #4
i want to create a vba routine that tell me the data in a specific cell has gone over a limit and warn me of it, if it hasnt gone over the limit then not to warn me can any1 tell me how this is done Maybe a simple IF() will do? Assume the specific cell is A2, with limit not to exceed 25 Put in say B2: =IF(A2>25,"Alert!","") B2 will remain blank until the value in A2 exceeds 25, wherein the phrase "Alert" will appear in B2 -- Rgds Max xl 97 -- Please respond, in newsgroup xdemechanik <at>yahoo<dot>com --- "Squibbly" <notreal@addy....

VBA script to empty an outlook folder
I've had no luck finding a code sample for this: I'd like to create a script to move all mail in a certain folder to the "Deleted Items" folder. This folder is used for spam. What I'm really trying to avoid is having to click on a piece of email by hand in order to delete it, because that would let the email open off-site links, possibly notifying spammers that they've hit on a valid email address. Another solution that would work if Outlook can do it: can you set up a folder to prohibit off-site links opening up in html mail? Stephen ...

Nested function limit
I've noticed that the limit for nested IF statements is 8. I've also noticed this is true for other functions than IF as well. Is this true for all function? The only way I can get around it is to put some of the inside stuff in another cell, and the reference it. Any ideas? Scott Hi this limit applies to all formulas (only 7 nested formulas are allowed). For IF clauses you may create a lookup table and use VLOOKUP to get the respective value for a search criteria -- Regards Frank Kabel Frankfurt, Germany Scott wrote: > I've noticed that the limit for nested IF statements...

VBA And the Datagrid
Hi, I need some help on the MS Datagrid as I need to add headers and adjust column sizes in the grid which is read only. Can someone point me in the direction for some code samples and or reference. It's data source is a sql statement. I tried this Dim dgExtract as New Datagrid and all the objects are exposed but doesn't do what I expect. I used the OCX control on my form and used this way and it worked: Set Datagrid8.RecordSource = rst and it populates. But I need to set column names and sizes etc.. Thanks. While waiting perhaps for one of the very few access users that bo...

Strange error when using offline sync
A couple of users are getting the message below when bringing their laptop and logging on to the domain. First of all, does the message below mean that only items on "Deleted" items failed to sync, or the whole thing fail to sync ? I've seen article that may associate this problem to the server Symantec AV but I already stopped/started Symantec services and problem below still persists. How can I solve this ? Is this a back end related problem ? 16:12:16 Synchronizing Mailbox 'My user' 16:12:16 Synchronizing Hierarchy 16:12:17 Synchronizing Favorites 16:12:17 Synchro...

Newly Created Function not Working
Hello I am using an excel function that I dropped into a VBA module. Th function works fine for me but when I copied the function into colleauges computer it is not working. We keep getting the error NAME returned. Any ides as to why this could be happening? We are bot running excel 2000. The function I am using is the sheetoffset functio if that helps at all -- nuve ----------------------------------------------------------------------- nuver's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1003 View this thread: http://www.excelforum.com/showthread.php?threadi...

Using MBCS in a UNICODE defined project
Hi, my project has the UNICODE and _UNICODE macros defined. I want to display a lot of chinese text, and this is working well for me. However, within the code, I make a call to a function: TestFunction(char * input); I need to supply it with MBCS format. So I use a T2A().... TestFunction(T2A(input)); and all is good (assuming the string is english alphabet chars). However.. that "input" string is read from a text file. If the input string is something like this.. "abcdefgXXXhigjklmop" - where "XXX" denotes some chinese MBCS chars If i read that string my proje...

How do I send out a survey using excel?
Is it possible to build and send out a survey to a specific user group in an Office application? Most likely yes, but need more information in order to assist you "Mas" <Mas@discussions.microsoft.com> wrote in message news:0742EDFE-2A2E-46B7-967F-FC4DEF577F3D@microsoft.com... : Is it possible to build and send out a survey to a specific user group in an : Office application? ...

Use arrows to navigate
I just got Excel 2003, and had a strange problem. The arrow keys no longer move the selection box; they scroll through the worksheet. Anybody know how to change this setting? I have looked in the help file, but could not figure it out. Matthew Any chance you hit the scroll lock on the keyboard? Just hit it again to toggle it off. Matthew wrote: > > I just got Excel 2003, and had a strange problem. > > The arrow keys no longer move the selection box; they scroll through the > worksheet. > > Anybody know how to change this setting? I have looked in the help fi...

VAT report should be to print without using system password
Currently when print VAT report (used for UK), tools-routines-company-vat return, need to enter system password and then can print report. Should be able to print VAT report without entering system password. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.m...

Excel2000: Is there a way to use name as parameter for ODBC query from Excel table
Hi I have an Excel workbook where dates, started from 01.01.2003 and up to some year in future (2011 at moment), along with some other information (i.e. workday/weekend/holiday, working hours etc.), are listed. This table is read into another workbook, using ODBC query. But the problem is, that I don't need all those future dates. I can't use any functions exept MIN, MAX, SUM and COUNT in query from Excel files, but is there a way to pass a name as parameter. P.e. I define a name: CurrDat=TODAY() and use it in query's WHERE condition, like .... WHERE MyTable.Datefield Is Not Null...

Help me "dissect" this function (Excel 2002 [XP])
=DSUM(Membership,Membership!$C$49,C1:C3)+DSUM(Revenue,'Revenue Projects'!$D$2,C1:C3) I'm trying to adapt a budget worksheet from another non-profit for our use. I don't know who the author is and I've had it a couple years, so trying to go that route for help is a non-starter. I don't understand why they have Membership twice; I guess one is for the worksheet and the other is the column header, but I don't get it as $C$49 points to a a column label named "Revenue". and C1:C3 point to 2 blank cells and one with the column label "2002". So...

Vlookup in VBA
I have to do a vlookup with VBA in one worksheet in which the Table_Array will go from column A11 to L11 down to a row, containing a specific text, "Paid." is that possible Hi please stay in your existing thread -- Regards Frank Kabel Frankfurt, Germany Jeff wrote: > I have to do a vlookup with VBA in one worksheet in which the > Table_Array will go from column A11 to L11 down to a row, containing > a specific text, "Paid." is that possible Why not keep to the original thread and respond to Frank's suggestion? -- HTH RP "Jeff" <Jeff...

Please help with finding correct functions!!!
I am trying to configure Excel to balance my checkbook. Column A is Trans. Type, Column B is Date, Column C is Description of Trans., Column D is Debits, Column E lists if items are posted, Column F is Debits and Column G is Balance. I'm trying to get Column D (debits) to subtract from Column G (balance) and get a new balance (new column? H?) and get Column F (credits) to add to Column G (balance to get a new balance (again, new column? H?). I am not computer illiterate and I should be able to figure this out by myself. But I've been working on finding the right functions (l...

Trying to do Business Cards Using Publisher
I apologize for this, but my previous thread I think maybe was looked at as being closed so I wasn't getting any responses. Please excuse me for being a little impatient here, but I would just love to get some good advice here on how to solve my ongoing problem. So I just started a new thread, but it really is a continuation of my previous one under basically the same heading. It looks like I have a problem here. After I applied my 2 by 3.5 business card graphic to the blank business card template in Publisher by applying it to the Master page, it looked great online and when I p...

Excel 97 VBA Help File
In the MS Excel Visual Basic Reference help file contents page, I click on Functions and it only offers me functions beginning with the letter S. So, I have a list of Solver and SQL functions. But what about all the other functions in VBA, for example for doing arithmetic and manipulating dates and strings? Why don't they show up? Are they left out because those functions are all part of Visual Basic generally, and the Excel VBA help file is specific to the _extra_ functions in Excel VBA? It's the only explanation I can think of. Am I right, or have I got a corrupted help file (vbaxl...

VBA Project Password
Hi, I have some VBA code (using Alt+F11 key) in an Excel file that I use for my work. I have used the password option for the project using a simple combination of keys (I thought I used 1234) for the password but it seems that I was wrong. Is there a way to cancel the password or recover it because I need to add more code and it always asks for the password. Khalil See what JE McGimpsey has for you at http://www.mcgimpsey.com/excel/fileandvbapwords.html Gord Dibben MS Excel MVP On Sat, 7 Jul 2007 14:43:16 +0200, "Khalil handal" <adelbhandal@hotmail.com> wrote: &...