pass variables to function to search sheet

abenitez77

Board Regular
Joined
Dec 30, 2004
Messages
149
I am trying to pass xlvalues, xlwhole, xlbyrows but I am getting an error msg (byref) when I try to compile this. I have the values that I am trying to pass, come from a table that stores the values.
My error is happening at this line:
Set ResultRange = FindAll(iRange, kWord, p3, p4, p5, p6)

Can you please help me set this up correctly?

Code:
        ' Create recordset for Key_List table ------------------------------------------------------------------------
        tSQL = "Select Doctype, keyword, SheetName, RangeToSearch, Param1, Param2, Param3, Param4, Param5, Param6 "
        tSQL = tSQL & "From Key_List Order by ID"
        
        Set rsKL = Connection.Execute(tSQL)
        If Not (rsKL.EOF) Then
             rsKL.MoveFirst
        End If
        
        ' Loop on Key_List table
        Do While Not rsKL.EOF
            ' Get Values form Key_List table in SQL
            kWord = rsKL!KeyWord
            dType = rsKL!DocType
            sName = rsKL!SheetName
            rToSearch = rsKL!SheetName
            p1 = rsKL!Param1
            p2 = rsKL!Param2
            p3 = rsKL!Param3
            p4 = rsKL!Param4
            p5 = rsKL!Param5
            p6 = rsKL!Param6
            
            ' Prep Spreadsheet prior to Searching KeyWords --------------------------
            MainShtZm = ActiveWindow.Zoom
            Range("A1").Select
            ActiveWindow.Zoom = MainShtZm
            
            ' Clean the Sheet for Processing -----------------------------------------
            Call ClearSheetMerged
            
            ' Search KeyWords form Key_List table to see if they exists in the open spreadsheet --------------------------------------
            Set iRange = Sheets(xSheetName).Range(rToSearch)
            Set ResultRange = FindAll(iRange, kWord, p3, p4, p5, p6)
            
            'Sheet name is not constant so check Is Deal Sheet or not?
            If Not (ResultRange Is Nothing) Then
                'GoTo NextCounter
                
                '1st Layout sheet data import code is here ……….
                Sheets(Counter).Columns.EntireColumn.AutoFit ' *********** Added this line to fit dates/numbers/text/etc... ***********
                'HdrSkipRec, DtlSkipRec and Reason are reference parameters.
                If ProcessedXLS = False Then
                    ProcessedXLS = GetItemDetailsData(xSheetName, Hdrid, XLFileFullPath)
                Else
                    ProcessedXLS2 = GetItemDetailsData(xSheetName, Hdrid, XLFileFullPath)
                End If
                
                GoTo NextCounter 'Go to next tab
            End If
            
            rsKL.MoveNext
            
        Loop

This is some of the results from the key_list table:
ID DocType KeyWord SheetName RangeToSearch Param1 Param2 Param3 Param4 Param5 Param6
1 PCF Price Change Form NULL A1:CZ50 iRange TSearch xlValues xlPart xlByColumns False
2 WHS NULL Whse Upload Cost Sheet NULL NULL NULL NULL NULL NULL NULL
3 CRF Cost /Retail Form NULL A1:CZ50 iRange TSearch xlValues xlPart xlByRows True
4 CCF Promotional/ Regional Cost Change Form NULL A1:CZ50 iRange TSearch xlValues xlPart xlByRows True
5 CCF WAL-MART COST CHANGE FORM NULL A1:CZ50 iRange TSearch xlValues xlPart xlByRows True
6 CCF COST CHANGE FORM NULL A1:CZ50 iRange TSearch xlValues xlPart xlByRows True
7 CCF COST CHANGE FORM PLEASE DO NOT REFORMAT THIS FORM NULL A1:CZ50 iRange TSearch xlValues xlPart xlByRows True
8 CCF Walmart Dept 90 Cost Change Form NULL A1:CZ50 iRange TSearch xlValues xlPart xlByRows True
9 CCF Dept 83-DSD COST CHANGE FORM NULL A1:CZ50 iRange TSearch xlValues xlPart xlByRows True
10 CCF Dept 93-DSD COST CHANGE FORM NULL A1:CZ50 iRange TSearch xlValues xlPart xlByRows True
11 CCF WAL-MART COST FORM NULL A1:CZ50 iRange TSearch xlValues xlPart xlByRows True
12 CCF DSD COST CHANGE FORM NULL A1:CZ50 iRange TSearch xlValues xlPart xlByRows True
13 CIP CURRENT ITEM PRICING NULL A1:CZ50 iRange TSearch xlValues xlPart xlByRows True
23 SKIP SKIP Guidelines and Instructions NULL NULL NULL NULL NULL NULL NULL
24 SKIP SKIP RL Report Help NULL NULL NULL NULL NULL NULL NULL
25 SKIP SKIP Dept Pricing Days NULL NULL NULL NULL NULL NULL NULL
14 RBF WAL-MART ROLLBACK FORM NULL A1:CZ50 iRange TSearch xlValues xlPart xlByRows True
15 NULL WM Item NULL A1:CZ50 iRange TSearch xlValues xlPart xlByRows True
16 IDCF Individual DC Cost Form NULL A1:CZ50 iRange TSearch xlValues xlPart xlByRows True
17 WHS Warehouse Cost Form NULL A1:CZ50 iRange TSearch xlValues xlPart xlByRows True
18 NMS SUPERCENTER / NEIGHBORHOOD MARKET STORES NULL A1:CZ50 iRange TSearch xlValues xlPart xlByRows True
19 SSO SSO Summary NULL A1:CZ50 iRange TSearch xlValues xlPart xlByRows True
20 PBR Planner/Buyer responsible for validation of data NULL A1:CZ50 iRange TSearch xlValues xlPart xlByRows True
21 CRM Case Ready Meats Collaborative Business Proposal NULL A1:CZ50 iRange TSearch xlValues xlPart xlByRows True
22 NIP RETAIL & COST CHANGE NULL A1:CZ50 iRange TSearch xlValues xlPart xlByRows True
 
Last edited:

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
I found the issue. I had my variables as strings, I had to declare my variables like this:
p3 As XlFindLookIn, p4 As XlLookAt, p5 As XlSearchOrder
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top