This is related to another question I had - but I think - in
considering what I'm trying to do, I must be missing something about
passing arguments, particularly those that I mentioned.

What I'm trying to do is
1) Check to see if a user has entered a value into a cell range
2) Look at all the values within the same row as the cell entered
3) Then, based on the values in particular columns of that
intersection, perform some analysis.

I think I'm confusing how to pass the range vs it address vs its value
- both in the case when a range is a
single cell and when its multiple.

In Sheet1:

Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim WatchRange as Range
Dim Cell As Range
Dim CheckRange As Range
Dim Cell2 As Range
Set WatchRange = Range("A:F")
For Each TargetCell in Target
If Union(Cell,WatchRange).Address = WatchRange.Address Then
' Only check if value is entered, not cleared
If Range(Cell.Address).Value < "" Then
Set CheckRange = EntryIsValid(Cell,WatchRange)
Call AnalyzeIt(CheckRange)
End If
End If
Next Cell
End Sub

In ThisWorkbook
' I want this to load on opening of worksheet by user....
ValidRange1-4 are on a different worksheet than the one they are
entering data in as will be predefined.
The contain data that says what values are okay in col n1,n2,etc. if
col m has a particular value.


Private Sub Workbook_Open()
' Pick range on other worksheet that has definitions of what pairs of
values are good.
Dim rng1 As Range
rng1 = Range("ValidRange1") <---- should I explicitly say on what
worksheet this is, or if there is
rng2 = Range("ValidRange2") only one range
of cells named "ValidRange" am I okay.
rng3 = Range("ValidRange3")
rng4 = Range("ValidRange4")
arr1 = rng1.Value
arr2 = rng2.Value
arr3 = rng3.Value
arr4 = rng4.Value
End Sub

In Module

Public arr1() , arr2(), arr3(), arr4() As Variant
Function EntryIsValid(TestRange,FullRange) As Range
Dim TheRange As Range
Set The Range = Intersect(TestRange.EntireRow,FullRange)
EntryIsValid = TheRange
End Function

Sub AnalyzeIt(CheckRange As Range)
Dim arr(6) <---- arr(1) holds Col A , arr(2) holds Col B , etc.
Dim res(2000,6)
' Probably need a routine here to load res(x,y) with all the valid
unique entries and then
' create a routine that checks, when making comparisons below,
that the number being
' loaded into res(x,y) each subsequent time is already there -
otherwise, ignore.
If Not arr(1) Is Nothing Then
For i = 1 to UBound (arr1,1)
Count = 1
If arr(1).Value = arr1(i,1) Then
res(Count,2) = arr1(i,2)
Count = Count + 1
End If
Count = 1
If arr(1).value = arr2(i,1) Then
res(Count,3) = arr2(i,2)
res(Count,4) = arr2(i,3)
Count = Count +1
End If
Count = 1
If arr(1).value = arr3(i,1) Then
res(Count,5) = arr3(i,2)
Count = Count +1
End If
Next i
For i = 1 to UBound(arr2,1)
..
..
..
similar as above
Next i