Solved

excel VBA return range object from a function

Posted on 2001-06-06
3
4,340 Views
Last Modified: 2012-08-13
Dear all,

   I 've a function which will return a range object:

Dim temp as range
set temp = findit("abc")
....

end sub

Function Findit(Expr As String)
Dim col_head As Range
Range("A1").Activate
Set col_head = Cells.Find(What:=Expr, LookAt _
       :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
       MatchCase:=False)
Findit= col_head
End Function

However , the problem is that after the function is excuted, there is error when returning to the line set temp = findit("abc").do anyone know what's the problem?thanks!
0
Comment
Question by:hpchong7
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 13

Expert Comment

by:cri
ID: 6159088
0
 
LVL 2

Expert Comment

by:nfernand
ID: 6159408
Hi guys... another tips:

Specify the data type returned: Function Findit(Expr As String) as Range, this way VBA has not to deal with variants that are slower to handle.

If all of this is used as a function inside a cell, no error menssages may be generated. All you see the most is an #error# or #N/A# value in the cell.

As I know (maybe I'm wrong) the find function generates an error if data cannot be found. SO you have to prevent this by trappnig the error with "on error".

Bye.
0
 
LVL 6

Accepted Solution

by:
blakeh1 earned 20 total points
ID: 6159959
I had problems running, it would actually error out on the
line
Findit= col_head
with an "Object Variable not set" error

I changed that line to
Set Findit = col_head
and I have no problem runnning it.
(Note: the variable will be "Nothing" if no match was found)

Sub testFindit()
    Dim temp As Range
    Set temp = Findit("ax")
End Sub
Function Findit(Expr As String) As Range
    Dim col_head As Range
    Range("A1").Activate
    Set col_head = Cells.Find(What:=Expr, LookAt _
          :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
          MatchCase:=False)
    Set Findit = col_head
End Function
 
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question