A sample file showing sample input/output would be helpful.

Patrick

Solved

Posted on 2011-10-29

In range $B$26:$N$45, column E contains strings of the following format: 7,12,7,5,7,8. In range $J$13:$L$18 each row contains single non-repeating integers (3, 11, 7). I need a VBA routine to check if column E of the first range contains integers from each row of the second range according to the following rules:

* First value of first range must match one of cells in first row of second range;

* Second value of first range must match one of cells in second row of second range;

* ...

* Sixth value of first range must match one of cells in sixth row of second range;

All rules are ANDed and if a row in the first range does not meet the strictures of the above rules then both italics and strikethrough are applied to that row!

Please advise?

Best wishes,

John

* First value of first range must match one of cells in first row of second range;

* Second value of first range must match one of cells in second row of second range;

* ...

* Sixth value of first range must match one of cells in sixth row of second range;

All rules are ANDed and if a row in the first range does not meet the strictures of the above rules then both italics and strikethrough are applied to that row!

Please advise?

Best wishes,

John

11 Comments

Thanks for the prompt follow-up.

As requested, I attach a sample file of exam answers and student results.

Best wishes,

John Q-27421907.xlsm

1) Add this function to a regular VBA module:

```
Function RetrieveSplitItem(Index As Long, Expression As Variant, Optional Delimiter As String = " ", _
Optional Limit As Long = -1, Optional Compare As VbCompareMethod = vbTextCompare, _
Optional FailIfNoDelimiter As Boolean = False) As Variant
' Function by Patrick G. Matthews
' This function is a wrapper for the VB6/VBA Split function, thus allowing it to be used in
' the user interface for Excel and/or Access. Depending on the Index value, the function
' returns a single delimited value, or an array of the various delimited values
' Arguments:
' Index indicates which element to return. If zero, function returns the full array. If >0,
' function returns the Nth delimited item, starting with 1 (even though the lower bound of
' the array is zero). If <0, function returns the Nth to last delimited item. If the
' absolute value of Index is greater than the number of elements in the array, function
' returns a subscript out of range error
' Expression is the delimited string expression to be split
' Delimiter is the character/sequence of characters used to delimit the string expression
' Limit places a cap on the number of elements returned by the function. Default -1
' indicates that there is no limit
' Compare indicates whether to match the Delimiter as non-case-sensitive (default, or 1),
' or case-sensitive (vbBinaryCompare from VBA, or 0)
' FailIfNoDelimiter controls how function behaves if the Delimiter is not found. Default
' (false) is to use the regular Split functionality, and still return an array with a
' single element so long as the string expression is not a zero length string. If True,
' if the Delimiter is not found then the function returns a subscript out of range error
Dim arr As Variant
' Set default return value of the function. 9 corresponds to 'subscript out of range'
RetrieveSplitItem = CVErr(9)
' Check for presence of Delimiter
If Not (InStr(1, Expression, Delimiter, Compare) = 0 And FailIfNoDelimiter) Then
' Create initial array using Split
arr = Split(Expression, Delimiter, Limit, Compare)
' Depending on Index, choose the appropriate return value
Select Case Index
' For Index = 0, just return the whole array
Case 0
RetrieveSplitItem = arr
' If positive, return the Nth element of the array. If Index is too large,
' return an error value
Case Is > 0
If (Index - 1) <= UBound(arr) Then
RetrieveSplitItem = arr(Index - 1)
End If
' If negative, return the Nth to last element of the array. If absolute value
' of Index is too large, return an error value
Case Else
If (UBound(arr) + Index + 1) >= 0 Then
RetrieveSplitItem = arr(UBound(arr) + Index + 1)
End If
End Select
End If
End Function
```

2) Select B26:N45, and create a formula-based CF rule using the formula

=(COUNTIF(INDEX($J$13:$L$1

Set the Font formatting to Italic, strikethrough, and red color

Slight problem. Partial answers are not generating the right formatting (see attached).

Best wishes,

John

Q-27421907-X.xlsm

=(COUNTIF(INDEX($J$13:$L$1

That formula did not work - it strkesthrough all rows without exception. Can you upload the corrected file?

Best wishes,

John

The following slight alteration appears to work correctly:

=IF(PRODUCT(COUNTIF(INDEX(

Best wishes,

John

To allow for possible blanks, the following additional correction was required:

=IF(PRODUCT(IF($J$13<>"",C

Thanks again.

John

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Storing values to an array | 8 | 42 | |

Excel line breaks | 1 | 19 | |

Min Month2 | 5 | 16 | |

VBA Help TT V-1.1 | 15 | 22 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**12** Experts available now in Live!