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

 

[Gavona]

Please use Forum707 for VBA questions next time and for any follow ups.

Sub FindIt(rng,MyValue)

Look up the Copy and the Paste methods in help. Basically the default is to paste to the current cell.

Rather than the Resize method you could use rng.EntireRow if the number of columns may vary.

Gavin