I have a worksheet that is 7 columns by 5 rows...in any one of those cells
the user can input a certain text value, say "X". I need to find which cell
that value is in and then test the next two cells, going across and then down
to see if the same value exists in order to assign points based on a
predefined points system. Any ideas on how to do that ?
You write that you want to check "the next two cells", so it appears that you
want to match either of three patterns:
Pattern 1
| X | X |
| | |
Pattern 2
| X | |
| | X |
Pattern 3
| X | X |
| | X |
If that is the case, the below UDF can return the appropriate pattern:
To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.
To use this User Defined Function (UDF), enter a formula like
=PatternMatch(Grid,TextValue)
in some cell.
===================================
Option Explicit
'Make case insensitive
Option Compare Text
Function PatternMatch(Grid As Range, TextValue As String) As String
Dim c As Range
PatternMatch = "No Match"
For Each c In Grid.Resize _
(Grid.Rows.Count - 1, Grid.Columns.Count - 1)
If c.Value = TextValue Then
If c.Offset(0, 1).Value = TextValue And _
c.Offset(1, 1).Value = TextValue Then
PatternMatch = "Pattern 3"
Exit Function
ElseIf c.Offset(1, 1).Value = TextValue Then
PatternMatch = "Pattern 2"
Exit Function
ElseIf c.Offset(0, 1).Value = TextValue Then
PatternMatch = "Pattern 1"
Exit Function
End If
End If
Next c
'check last row for Pattern 1
For Each c In Grid.Resize(1, Grid.Columns.Count -
1).Offset(rowoffset:=Grid.Rows.Count - 1)
If c.Value = TextValue And _
c.Offset(0, 1).Value = TextValue Then
PatternMatch = "Pattern 1"
Exit Function
End If
Next c
End Function
=============================
--ron
