Function to search for a value within a range

[ChiTownGBM]

Hi all,

I need to create a function that will take a value and search a range for that value and then copy all of the data on row to a location.

I found this code on the MSDN website:

Sub FindIt()
Dim rng As Range

' Find data and use Resize property to copy range.
Set rng = Range("A1:A12").Find(What:="Jun", _
LookAt:=xlWhole, LookIn:=xlValues)
If rng Is Nothing Then
MsgBox "Data not found"
Exit Sub
Else
rng.Resize(1, 3).Copy Destination:=Range("G1")
End If

End Sub


More can be found at:

http://msdn2.microsoft.com/en-us/li...evGuideExcel2007RangeObject_SearchinginRanges

I'd basically like to turn that VB script into a function where I can pass the value to search for, the range to search for. The starting cell for copying the row of data, if found can be the current cell.

Thanx for your help. :-

 

[xlbo]

The function would take the form:

Function FindIt(searchVal as long, searchRange as range, startCell as range)



End Function

All you nered to do is sub in the input variables above into the appropriate places in the sub that you have posted

e.g.

Set rng = searchRange.Find(What:=searchVal, _
LookAt:=xlWhole, LookIn:=xlValues)


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[ChiTownGBM]

I think I understand. I'm a network manager and not a developer I need to know how to turn this code into a function? In other words I dunno how to do it. All the functions that I've ever created I was given step-by-step instructions on what to do.

Please help.

Chi

 

[Gavona]

This forum is more about helping you to help yourself than writing your solution for you.

You cannot do what you want in a "function" but you could trigger the sub-routine using events. Basically you could trigger the sub-routine if a cell changed in a specified range.

Probably best
(i) to specify your business objectives - there may be different approaches to meeting them
(ii) to get a sub working that does what you want when manually run (with my response to your original post and Geoff's you should be able to get there but post back with your revised code if you can't get it working)
(iii) to modify the code to be triggered by an "event" (look up events in help and in this forum's FAQs, maybe do a search in this forum. Post back when you need help or clarification)



Gavin

 

[xlbo]

Gavin - can be done as a function just not a function called from a worksheet e.g.

Sub CallTheFunction()
call RunFunction "This is what appears in the messagebox"
End Sub

Function RunFunction(strMessage as string)
msgbox strMessage
End Fucntion

Chi - there are lots of examples of what you want to do. As Gavin has stated, this forum is to help you to help yourself, not to write code for you

I have actually already given you about 75% of what you need - I have given you an example of how to use 2 out of the 3 variables that need passing to the function. Have a look at what I have posted. Have a search of this forum. Read the help files. Try something for yourself and post back when you have a specific issue with the code you are trying to generate

If you want step by step instructions, you are in the wrong place. This is a site for people who are interested in learning, not just being told what to do

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Gavona]

Thanks Geoff. Am I missing something?

A Function procedure is a series of Visual Basic statements enclosed by the Function and End Function statements. A Function procedure is similar to a Sub procedure, but a function can also return a value...

Technically you may well be able to swap "Function" for "Sub" as you have illustrated but the functionality you have illustrated (or Chi has requested) appears to be no different from a sub-routine and it is not a true "function" in the way defined by microsoft... Or am I missing something?

Of course what we can only guess what Chi meant by "Function" - hence the need to establish his business objectives.
Regards

Gavin

 

[ChiTownGBM]

Geoff, I've never scripted in VBA' that talent falls outside of my discipline. I can write very complicated formulas using Excel's native/built-in functions so I'm sure I possess the logic needed and I'm willing to do it. Step-by-step instructions is not what I asked for. I provided the core code and asked for help as I helped myself. Everyone needs to start somewhere but It's not a reasonable expectation to believe that someone who's never scripted can jump in at this level.

BTW, the very first thing I did was search this site for an example or tutorial on scripting an Excel function in VBA.

Thanks for your help and insight thus far.

 

[xlbo]

Seriously, I have given you, verbatim, almost everything you need

I have shown you how to structure the function and I have shown you how to replace hard coded elements with the variables that are passed to the function. I even showed you how to do this with your own code. I can't really show you any more without writing the whole function for you

What you had:

Sub FindIt()
Dim rng As Range
Set rng = Range("A1:A12").Find(What:="Jun", _
LookAt:=xlWhole, LookIn:=xlValues)
If rng Is Nothing Then
  MsgBox "Data not found"
  Exit Sub
Else
  rng.Resize(1, 3).Copy Destination:=Range("G1")
End If
End Sub

What I have given you already:

Function FindIt([b]searchVal[/b] as long, [b]searchRange[/b] as range, startCell as range)
Dim rng As Range
Set rng = [b]searchRange[/b].Find(What:=[b]searchVal[/b], _
LookAt:=xlWhole, LookIn:=xlValues)
If rng Is Nothing Then
  MsgBox "Data not found"
  Exit function
Else
[COLOR=green]
'All you need to do is figure out how to make "Range("G1")" a variable as per how I have shown you with the other 2[/color]
rng.Resize(1, 3).Copy Destination:=Range("G1")

End Function

Gavin - yes, functions in code are (or can be) very similar to subs. The main differences are:

1: Functions CAN (but don't have to) return a value - a sub cannot
2: Functions do not appear in the macro list in Tols>Options>Play macros
3: Functions, if written correctly, can be used on the worksheet itself rather than just within another piece of code. these functions cannot affect data outside of the cell they reside in however

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Gavona]

Chi

'All you need to do is figure out how to make "Range("G1")" a variable as per how I have shown you with the other 2

Actually I gave you the pointer for that in your original thread thread68-1454030

Geoff,
For completeness I would add that Subs with parameters don't appear in the macro list either. So in this case it makes no difference at all if we call it a Sub or a Function.

Gavin

 

[xlbo]

Gavin - yes, there is no real difference whether it is called a sub or a function. I have called it a function simply because that is what the OP whanted !!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question