Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way of asking "is a member of" in VBA?
I often have to test whether any of a range of numbers (x in the example
below) is represented in a set of values - which may or may not be entered in a range of cells. If I put the set of values into an array (ArrValues below - could be one- or two-dimensional), is there a neat way of asking: If x "is a member of" ArrValues on one line of code, or do I have to cycle through all the members of ArrValues in turn (say using For loop) and check whether each one is equal to x? I've tried using the Match WorksheetFunction with 0 as the third argument: With WorksheetFunction For x = 1 To 100 If Not .IsNA(.Match(x, ArrValues, 0)) Then Cells(x, 14) = x 'report matching values in column 14 End If Next k End With but it doesn't work - I get an "Unable to get the Match property of the WorksheetFunction class". Instead, I'm having to do this, for example: For i = 1 To 10 For j = 1 To 10 For x = 1 To 100 If ArrValues(i, j) = x Then Cells(x, 14) = x 'report values in column 14 End If Next k Next j Next i The example may seem trivial, but I have other instances where it's not so trivial. Thanks Ian |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way of asking "is a member of" in VBA?
Ian,
' If you set row_num or column_num to 0 (zero), then ' Index returns the array of values for the entire column or row ' (looping thru the array column could be as fast or faster) Sub MakeSomethingUp() Dim N As Variant Dim arr As Variant Dim arr2 As Variant Dim strMessage As String 'Fill the array. arr = Range("A1:B50").Value 'Return the second column of the array arr2 = Application.Index(arr, 0, 2) 'Find the position in the array of "77" N = Application.Match(77, arr2, 0) If Not IsError(N) Then strMessage = "Found in row " & N Else strMessage = "Not Found" End If MsgBox strMessage End Sub ----------- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "IanKR" wrote in message I often have to test whether any of a range of numbers (x in the example below) is represented in a set of values - which may or may not be entered in a range of cells. If I put the set of values into an array (ArrValues below - could be one- or two-dimensional), is there a neat way of asking: If x "is a member of" ArrValues on one line of code, or do I have to cycle through all the members of ArrValues in turn (say using For loop) and check whether each one is equal to x? I've tried using the Match WorksheetFunction with 0 as the third argument: With WorksheetFunction For x = 1 To 100 If Not .IsNA(.Match(x, ArrValues, 0)) Then Cells(x, 14) = x 'report matching values in column 14 End If Next k End With but it doesn't work - I get an "Unable to get the Match property of the WorksheetFunction class". Instead, I'm having to do this, for example: For i = 1 To 10 For j = 1 To 10 For x = 1 To 100 If ArrValues(i, j) = x Then Cells(x, 14) = x 'report values in column 14 End If Next k Next j Next i The example may seem trivial, but I have other instances where it's not so trivial. Thanks Ian |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way of asking "is a member of" in VBA?
Many thanks for this, Jim.
I see that you can use Match (successfully) with a 1-dimensional array - this is presumably where I went wrong, as I was trying to use it with a 2-dim'l array. So I'd have to use Index against each column of my array in turn, then. It would probably be quicker to use For... loops, as I was doing already?! Is this what you mean by "looping thru the array column could be as fast or faster"? One thing I notice is that you don't: Dim arr (1 To 50, 1 To 2) as Variant and Dim arr2 (1 To 50) as Variant i.e. you leave out the Bounds in the brackets, and also that you don't Redim them with Bounds within the Sub. Presumably this is not necessary? Does Dim-ing them with Bounds only serve to save on memory? As an aside, the stuff I've read in Excel VBA books on arrays has always completely baffled me. They always mention the need to declare them as per: Dim arr (1 To 50, 1 To 2) for example, and they talk about LBounds and UBounds, but very little on how to actually USE them in a meaningful way (and nothing on how to populate them with values - I never knew until today that you could do arr = Range("A1:B50").Value to populate an array from a worksheet range). But thanks again - I've learned a few useful things, including how to use the Index worksheet function. But I'm still dreadfully confused by what you can, can't, must and mustn't do with arrays! Ian Jim Cone wrote in message : Ian, ' If you set row_num or column_num to 0 (zero), then ' Index returns the array of values for the entire column or row ' (looping thru the array column could be as fast or faster) Sub MakeSomethingUp() Dim N As Variant Dim arr As Variant Dim arr2 As Variant Dim strMessage As String 'Fill the array. arr = Range("A1:B50").Value 'Return the second column of the array arr2 = Application.Index(arr, 0, 2) 'Find the position in the array of "77" N = Application.Match(77, arr2, 0) If Not IsError(N) Then strMessage = "Found in row " & N Else strMessage = "Not Found" End If MsgBox strMessage End Sub ----------- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "IanKR" wrote in message I often have to test whether any of a range of numbers (x in the example below) is represented in a set of values - which may or may not be entered in a range of cells. If I put the set of values into an array (ArrValues below - could be one- or two-dimensional), is there a neat way of asking: If x "is a member of" ArrValues on one line of code, or do I have to cycle through all the members of ArrValues in turn (say using For loop) and check whether each one is equal to x? I've tried using the Match WorksheetFunction with 0 as the third argument: With WorksheetFunction For x = 1 To 100 If Not .IsNA(.Match(x, ArrValues, 0)) Then Cells(x, 14) = x 'report matching values in column 14 End If Next k End With but it doesn't work - I get an "Unable to get the Match property of the WorksheetFunction class". Instead, I'm having to do this, for example: For i = 1 To 10 For j = 1 To 10 For x = 1 To 100 If ArrValues(i, j) = x Then Cells(x, 14) = x 'report values in column 14 End If Next k Next j Next i The example may seem trivial, but I have other instances where it's not so trivial. Thanks Ian |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way of asking "is a member of" in VBA?
Some comments that I hope make sense... In time critical code you should try alternative methods and time them. Sometimes you can be surprised. The lower bound is simply the starting row or column and the upper bound is the ending row or column... Arr( 2 to 10, 3 to 4) has rows numbered from 2 to 10 (9 rows) and columns numbered from 3 to 4 (two columns). Arr(2, 3) is the top left corner and Arr(10, 4) is the bottom right corner. A range can be considered as an array of values, for example ... 'Run this one and see what you get. Sub RangeIsAnArray() Dim rng As Excel.Range Set rng = Range("A10:B55") '(10, 2) the value in the 10th row / 2nd col of the range (not the worksheet) MsgBox rng(10, 2) End Sub A Variant is a data type that can contain almost anything. (it also requires more memory than any other data type) So... Dim Arr as Variant When the range values are assigned to it ... Arr = Range("A1:B50").Value Arr ends up as a variant containing an array. Arr(1, 2) holds the value (in this case) of cell B1. To my simple way of thinking an Array is simply a memory structure that allows you to store and retrieve data. In order to use it, you have to tell Excel that you want an array and what kind of data it will hold... Dim strArry() as String Then you have to tell Excel how much room you need... ReDim strArry(1 to 10, 1 to 1) (you need 10 rows and 1 column) 'Then assign something to the first element of the array. strArry(1, 1) = "Ian" 'Retrieve the value... MsgBox strArry(1, 1) -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "IanKR" wrote in message ... Many thanks for this, Jim. I see that you can use Match (successfully) with a 1-dimensional array - this is presumably where I went wrong, as I was trying to use it with a 2-dim'l array. So I'd have to use Index against each column of my array in turn, then. It would probably be quicker to use For... loops, as I was doing already?! Is this what you mean by "looping thru the array column could be as fast or faster"? One thing I notice is that you don't: Dim arr (1 To 50, 1 To 2) as Variant and Dim arr2 (1 To 50) as Variant i.e. you leave out the Bounds in the brackets, and also that you don't Redim them with Bounds within the Sub. Presumably this is not necessary? Does Dim-ing them with Bounds only serve to save on memory? As an aside, the stuff I've read in Excel VBA books on arrays has always completely baffled me. They always mention the need to declare them as per: Dim arr (1 To 50, 1 To 2) for example, and they talk about LBounds and UBounds, but very little on how to actually USE them in a meaningful way (and nothing on how to populate them with values - I never knew until today that you could do arr = Range("A1:B50").Value to populate an array from a worksheet range). But thanks again - I've learned a few useful things, including how to use the Index worksheet function. But I'm still dreadfully confused by what you can, can't, must and mustn't do with arrays! Ian Jim Cone wrote in message : Ian, ' If you set row_num or column_num to 0 (zero), then ' Index returns the array of values for the entire column or row ' (looping thru the array column could be as fast or faster) Sub MakeSomethingUp() Dim N As Variant Dim arr As Variant Dim arr2 As Variant Dim strMessage As String 'Fill the array. arr = Range("A1:B50").Value 'Return the second column of the array arr2 = Application.Index(arr, 0, 2) 'Find the position in the array of "77" N = Application.Match(77, arr2, 0) If Not IsError(N) Then strMessage = "Found in row " & N Else strMessage = "Not Found" End If MsgBox strMessage End Sub ----------- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "IanKR" wrote in message I often have to test whether any of a range of numbers (x in the example below) is represented in a set of values - which may or may not be entered in a range of cells. If I put the set of values into an array (ArrValues below - could be one- or two-dimensional), is there a neat way of asking: If x "is a member of" ArrValues on one line of code, or do I have to cycle through all the members of ArrValues in turn (say using For loop) and check whether each one is equal to x? I've tried using the Match WorksheetFunction with 0 as the third argument: With WorksheetFunction For x = 1 To 100 If Not .IsNA(.Match(x, ArrValues, 0)) Then Cells(x, 14) = x 'report matching values in column 14 End If Next k End With but it doesn't work - I get an "Unable to get the Match property of the WorksheetFunction class". Instead, I'm having to do this, for example: For i = 1 To 10 For j = 1 To 10 For x = 1 To 100 If ArrValues(i, j) = x Then Cells(x, 14) = x 'report values in column 14 End If Next k Next j Next i The example may seem trivial, but I have other instances where it's not so trivial. Thanks Ian |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way of asking "is a member of" in VBA?
Many thanks Jim - what you say makes absolute sense and you've explained it
very clearly. I just wish the VB books I've read had done the same! Perhaps you should write a book yourself... Some comments that I hope make sense... In time critical code you should try alternative methods and time them. Sometimes you can be surprised. The lower bound is simply the starting row or column and the upper bound is the ending row or column... Arr( 2 to 10, 3 to 4) has rows numbered from 2 to 10 (9 rows) and columns numbered from 3 to 4 (two columns). Arr(2, 3) is the top left corner and Arr(10, 4) is the bottom right corner. A range can be considered as an array of values, for example ... 'Run this one and see what you get. Sub RangeIsAnArray() Dim rng As Excel.Range Set rng = Range("A10:B55") '(10, 2) the value in the 10th row / 2nd col of the range (not the worksheet) MsgBox rng(10, 2) End Sub A Variant is a data type that can contain almost anything. (it also requires more memory than any other data type) So... Dim Arr as Variant When the range values are assigned to it ... Arr = Range("A1:B50").Value Arr ends up as a variant containing an array. Arr(1, 2) holds the value (in this case) of cell B1. To my simple way of thinking an Array is simply a memory structure that allows you to store and retrieve data. In order to use it, you have to tell Excel that you want an array and what kind of data it will hold... Dim strArry() as String Then you have to tell Excel how much room you need... ReDim strArry(1 to 10, 1 to 1) (you need 10 rows and 1 column) 'Then assign something to the first element of the array. strArry(1, 1) = "Ian" 'Retrieve the value... MsgBox strArry(1, 1) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way of asking "is a member of" in VBA?
Ian, You are welcome. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "IanKR" wrote in message Many thanks Jim - what you say makes absolute sense and you've explained it very clearly. I just wish the VB books I've read had done the same! Perhaps you should write a book yourself... Some comments that I hope make sense... In time critical code you should try alternative methods and time them. Sometimes you can be surprised. The lower bound is simply the starting row or column and the upper bound is the ending row or column... Arr( 2 to 10, 3 to 4) has rows numbered from 2 to 10 (9 rows) and columns numbered from 3 to 4 (two columns). Arr(2, 3) is the top left corner and Arr(10, 4) is the bottom right corner. A range can be considered as an array of values, for example ... 'Run this one and see what you get. Sub RangeIsAnArray() Dim rng As Excel.Range Set rng = Range("A10:B55") '(10, 2) the value in the 10th row / 2nd col of the range (not the worksheet) MsgBox rng(10, 2) End Sub A Variant is a data type that can contain almost anything. (it also requires more memory than any other data type) So... Dim Arr as Variant When the range values are assigned to it ... Arr = Range("A1:B50").Value Arr ends up as a variant containing an array. Arr(1, 2) holds the value (in this case) of cell B1. To my simple way of thinking an Array is simply a memory structure that allows you to store and retrieve data. In order to use it, you have to tell Excel that you want an array and what kind of data it will hold... Dim strArry() as String Then you have to tell Excel how much room you need... ReDim strArry(1 to 10, 1 to 1) (you need 10 rows and 1 column) 'Then assign something to the first element of the array. strArry(1, 1) = "Ian" 'Retrieve the value... MsgBox strArry(1, 1) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
"Member not found" exception...Help! | Excel Programming | |||
Getting "compile error" "method or data member not found" on reinstall | Excel Programming | |||
"Member Not Found" Error with Excel 2000 | Excel Programming |