Create a Parametered Search Function Excel VBA

[JasonEnsor]

Hi Guys,

Once again I am trying to re-evaluate how one of my applications run and so I thought I would see if anyone can offer some help and advice as the members here seem to know their stuff.

So in Excel I tend to use the following code to search workbooks, each time I am searching the same range but on a different worksheet or workbook and the value I search for might change occasionally. As I am passing between workbooks it's getting quite messy which is why I have been trying to clean everything up as much as I can. Ideally I would like to create a Search function where I can pass in parameters like Range, Worksheet and Item to find and it return to me the Row number that contains the data so I can then just pass the correct data in to that row. There will only be one instance of the result on the sheet that will be being searched as it just holds information about payments for a particular month.

If I can create the function then I can add it to my toolbox of functions and it will just remove so much clutter. I'm not even sure if the search I am using is the best way forward as it is something I have not really looked in to much, it was a recorded macro that for a small project worked and I just tweaked the values.

Set Carry = SwimmerPayments.Sheets(util.MonthName)
            
            With Carry.Range("A:A")
                Set Rng = .Find(What:=SwimmerT.SwimmerName, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=True)
            End With
    
            If Not Rng Is Nothing Then
                Application.Goto Rng, True
                TempRow = ActiveCell.Row
                
                Carry.Range("D" & TempRow) = "CF"
                Carry.Range("I" & TempRow) = NewPaymentForm.LblWeek1.Caption       
            Else
                iRow = Carry.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        
                Carry.Cells(iRow, 1).Value = NewPaymentForm.NameBox.Value
                Carry.Cells(iRow, 2).Value = NewPaymentForm.cmbClassTimes.Value
                Carry.Cells(iRow, 4).Value = "CF"
                Carry.Cells(iRow, 9).Value = NewPaymentForm.LblWeek1.Caption
            End If

Once again any help would be appreciated.

Jason

 

[SkipVought]

Hi,

Seems that you might need to pass values for lookup value, lookup range, sheet name, OPTIONAL workbook path/ name.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}