Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
General Question regarding passing cells, ranges, cell values through subs & functions
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
General Question regarding passing cells, ranges, cell values through subs & functions
in Worksheet_Change you never set cell to anything, so when you use it, it
is nothing. you probably meant For Each Cell in Target rather than For Each TargetCell in Target In the thisworkbook module or in a sheet module, you should qualify a named range with it worksheet Dim rng1 As Range rng1 = Range("ValidRange1") raises an error because you must use Set rng1 = to initialize rng1. Since you don't declare rng2, etc, they will be variant arrays. No sense using the rng variable, then assigning their values to arrays. Do it directly. Worksheets("Data").Range("ValidRange1") you have a space in TheRange Set The Range = Intersect(TestRange.EntireRow,FullRange) so that will cause an error. Also, you already checked that TestRange is interior to FullRange, so you don't need EntireRow on the end. Dim arr(6) <---- arr(1) holds Col A , arr(2) holds Col B , etc. not if you don't initialize it with those values and I don't see anywhere that you do. If Not arr(1) Is Nothing Then if arr(1) is not a reference to an object this could raise an error. In any event, it probably isn't the test you want. Your passing a reference to a range, so that should be OK. Public arr1() , arr2(), arr3(), arr4() As Variant someone already told you that this is not the proper way to declare variables. In this case, it makes no difference since if you don't specify a type for each variable it will be variant by default. I also told you that use Arr1() gains you nothing and makes your code incompatible with xl97, but that is your choice. To make it compatible leave off the (). -- Regards, Tom Ogilvy " wrote in message ... 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 . . through i = 1 to UBound(arr4,1) End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Basic Question on Subs | Excel Worksheet Functions | |||
Common Functions and Subs | Excel Discussion (Misc queries) | |||
passing arrays between functions in VBA | Excel Programming | |||
Passing values between 2 subs ? | Excel Programming |