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