Hi can anyone help with the following code?
On Sheet2 I have the following
MyLetters in "E4:K4"
MyNumbers in "D5:D30"<strike></strike>
MyData in "E5:K30"
MyResults in "AN20:AT26"<strike></strike>
The code works fine but I want to change the results so that instead of Indexing every row with (MyLetters) within (MyData) into the Results, I want to index the Rows that only have 2 or more of (MyLetters) into the Results. In the example below (ResultsA) shows the results using every Row and (MyLetters) and ResultsB shows what the results would be if only indexing Rows with 2 or more MyLetters.(To get ResultsB I cleared all the Letters on rows(5,8,11,13,14,16,22,24,25,28 and ran the code again)
Could I add something like 'COUNTIF(Row, MyData) >= 2' to the code somewhere to achieve this?
<tbody>
</tbody><strike>
</strike>
Any help would be appreciated
Regards
pwill
On Sheet2 I have the following
MyLetters in "E4:K4"
MyNumbers in "D5:D30"<strike></strike>
MyData in "E5:K30"
MyResults in "AN20:AT26"<strike></strike>
The code works fine but I want to change the results so that instead of Indexing every row with (MyLetters) within (MyData) into the Results, I want to index the Rows that only have 2 or more of (MyLetters) into the Results. In the example below (ResultsA) shows the results using every Row and (MyLetters) and ResultsB shows what the results would be if only indexing Rows with 2 or more MyLetters.(To get ResultsB I cleared all the Letters on rows(5,8,11,13,14,16,22,24,25,28 and ran the code again)
Could I add something like 'COUNTIF(Row, MyData) >= 2' to the code somewhere to achieve this?
Code:
[FONT=Verdana]
Sub IndexMyData()
[/FONT]
[FONT=Verdana] Dim MyRow(26), InSht As Worksheet, OutSht As Worksheet
Dim c As Integer
Dim i As Long
Dim j As Long
Dim r As Long
Dim MyResults
Dim CustOrd
Dim MyData
Dim MyNums
Dim MyLtrs
Application.EnableEvents = False
Set InSht = Sheet2
Set OutSht = Sheet2
Set MyResults = OutSht.Range("AN20:AT26")
MyData = InSht.Range("E5:K30")
MyNums = InSht.Range("D5:D30")
MyLtrs = InSht.Range("E4:K4")
For i = 1 To 26
MyRow(i) = " " & Join(WorksheetFunction.Index(MyData, i, 0)) & " "
CustOrd = CustOrd & MyNums(i, 1) & ","
Next i
MyResults.ClearContents
For i = 1 To 7
c = 0
For j = 1 To 26
If InStr(MyRow(j), " " & MyLtrs(1, i) & " ") > 0 Then
c = c + 1
MyResults.Cells(c, i) = MyNums(j, 1)
If c = 7 Then Exit For
End If
Next j
Next i
With OutSht.Sort
.SortFields.Clear
For i = 1 To 7
.SortFields.Add Key:=MyResults.Cells(i, 1), CustomOrder:=CVar(CustOrd)
Next i
.SetRange MyResults
.Orientation = xlLeftToRight
.Apply
End With
Application.EnableEvents = True
End Sub[/FONT]
A | B | C | D | E | F | G | H | I | ||||||||||
1 | ||||||||||||||||||
2 | ||||||||||||||||||
3 | ||||||||||||||||||
4 | MyLtrs | A | B | C | D | E | F | G | Results A | |||||||||
5 | 1 | 7 | 12 | 17 | 21 | D | 40 | 43 | AN | AO | AP | AQ | AR | AS | AT | AU | ||
6 | 2 | 5 | 10 | A | 19 | C | 38 | 41 | 20 | 1 | 2 | 2 | 4 | 9 | 11 | 13 | ||
7 | 3 | 8 | 13 | 18 | 22 | 30 | 41 | 44 | 21 | 17 | 7 | 13 | 11 | 12 | 17 | 19 | ||
8 | 4 | 4 | 9 | 14 | 18 | B | 37 | 40 | 22 | 20 | 10 | 21 | 19 | 18 | ||||
9 | 5 | 9 | 14 | 19 | 23 | 31 | 42 | 45 | 23 | 25 | 17 | 24 | 26 | 25 | ||||
10 | 6 | 3 | 8 | 13 | 17 | 25 | 36 | 39 | 24 | 26 | 26 | |||||||
11 | 7 | 10 | A | 20 | 24 | 32 | 43 | 46 | 25 | |||||||||
12 | 8 | 2 | 7 | 12 | 16 | 24 | 35 | 38 | 26 | |||||||||
13 | 9 | 11 | 16 | 21 | 25 | E | 44 | 47 | ||||||||||
14 | 10 | 1 | 6 | 11 | A | 23 | 34 | 37 | Results B | |||||||||
15 | 11 | 12 | 17 | 22 | B | 34 | 45 | F | AN | AO | AP | AQ | AR | AS | AT | AU | ||
16 | 12 | 0 | 5 | 10 | 14 | 22 | E | 36 | 20 | 2 | 2 | 11 | 11 | 13 | 17 | 25 | ||
17 | 13 | 13 | 18 | 23 | C | 35 | 46 | G | 21 | 13 | 17 | 17 | 19 | 19 | 25 | |||
18 | 14 | -1 | 4 | 9 | 13 | 21 | 32 | 35 | 22 | 26 | 26 | 26 | ||||||
19 | 15 | 14 | 19 | 24 | 28 | 36 | 47 | 50 | 23 | |||||||||
20 | 16 | -2 | 3 | 8 | 12 | 20 | 31 | 34 | 24 | |||||||||
21 | 17 | A | 20 | 25 | D | 37 | F | 51 | 25 | |||||||||
22 | 18 | -3 | 2 | 7 | 11 | 19 | 30 | E | 26 | |||||||||
23 | 19 | 16 | 21 | B | 30 | 38 | G | 52 | ||||||||||
24 | 20 | -4 | 1 | 6 | 10 | 18 | D | 32 | ||||||||||
25 | 21 | 17 | 22 | C | 31 | 39 | 50 | 53 | ||||||||||
26 | 22 | -5 | 0 | 5 | 9 | 17 | 28 | 31 | ||||||||||
27 | 23 | 18 | 23 | 28 | 32 | 40 | 51 | 54 | ||||||||||
28 | 24 | -6 | -1 | 4 | 8 | 16 | C | 30 | ||||||||||
29 | 25 | 19 | 24 | D | E | 41 | 52 | 55 | ||||||||||
30 | 26 | -7 | -2 | 3 | 7 | A | B | D |
<tbody>
</tbody>
</strike>
Any help would be appreciated
Regards
pwill
Last edited: