Thanks a lot for your reply Domenic. I am receiving error vMatchval= Error 2042 with above code. I will try to explain better with the use of tables and data.
PT 4 Worksheet
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]PTW No[/TD]
[TD]PTW End Date[/TD]
[TD]SiteCode[/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]CHG0081150[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]29/08/2014[/TD]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD="class: xl65, width: 147"]65MV
65MV[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]CHG0082838[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]29/08/2014[/TD]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD="class: xl65, width: 147"]12WG
32ZH
12KF
52WA
13HU
M3WW
92JC
22JI
22JI
O2YH
42RM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]CHG0081513[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]30/08/2014[/TD]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD="class: xl65, width: 147"]S2DL
S2LE
2NWP
S2RW[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
First of all, I have selected a cell under
SiteCode and cleaned up to remove Enter and spaces.
Code:
Sitecode = ActiveCell.Value
Sitecode = Application.WorksheetFunction.Clean(Sitecode)
' If C3 is selected, I get Sitecode = "12WG32ZH12KF52WA13HUM3WW92JC22JI22JIO2YH42RM"
Then I have broken the value of variable Sitecode into 4 characters each and stored them in a array - Arr.
Code:
Dim SitecodeDivided As Variant
SitecodeDivided = Len(Sitecode) / 4
ReDim Arr(SitecodeDivided) As String
For a = 1 To SitecodeDivided
Arr(a) = Mid(Sitecode, 4 * a - 3, 4)
Next a
Array now has
Arr[1]="12WG"
Arr[2]="32ZH"
.
Arr[11]="42RM"
I have another worksheet "SM9 Export' with following columns. This is the Master Data having all the Planned Work No with their Site code. Each Site code can have multiple planned works and hence multiple Planned work No can be returned.
SM9 Export Worksheet
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]PTW No[/TD]
[TD]PTW End Date[/TD]
[TD]SiteCode[/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"][TABLE="width: 82"]
<tbody>[TR]
[TD="width: 82"]CHG0083744[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]08/09/2014[/TD]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD="class: xl65, width: 147"]22JI
22JI
O2YH[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"]CHG0082838[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]29/08/2014[/TD]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD="class: xl65, width: 147"]12WG
32ZH
12KF
52WA
13HU
M3WW
92JC
22JI
22JI
O2YH
42RM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96"][TABLE="width: 82"]
<tbody>[TR]
[TD="width: 82"]CHG0080113[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]13/08/2014[/TD]
[TD][TABLE="width: 147"]
<tbody>[TR]
[TD="class: xl65, width: 147"]S2RW[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
With the search in SM9 Export worksheet, I would like to search each of the Array values stored earlier in the column C2 to C4000 and provide outcome of all Planned work No. which have any of the Array values without duplicating. If searching for Arr[8]="22JI", it should find
CHG0083744 and CHG0082838 and then searching for Arr[10]="O2YH', it should not duplicate with results
CHG0083744 and CHG0082838 as they have already been found with Arr[8] search.
I think I need to cleanup each cell of column C of SM9 Export worksheet in the For loop before I look for matching strings from the array?
So far I have added Domenic's code but it gives error
Code:
'Declare these additional variables
Dim vMatchVal As Variant
Dim aResults() As Variant
'
'Re-allocate storage space to store the results
ReDim aResults(1 To UBound(Arr))
'Lookup values from Arr and store the results in aResults
For I = LBound(Arr) To UBound(Arr)
With Worksheets("SM9 Export") 'change the name of the master sheet accordingly
vMatchVal = Application.Match(Arr(I), .Range("C2:C4000"), 0) ' Lookup range changed to column C of SM9 Export worksheet
' In the Debug Window, I get error - : vMatchVal : Error 2042 : Variant/Error
aResults(I) = Application.Index(.Range("A2:A100"), vMatchVal) 'change the return range accordingly
End With
Next I
'Transfer the results from aResults to a vertical range of cells in Sheet2, starting at B2
Worksheets("PT 4").Range("I6").Resize(UBound(aResults)).Value = Application.Transpose(aResults)
I hope I have been to explain it better. Please help !!!!!!! Thanks