Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
array function for multidimensional arrays
Hi All,
rbetuk = Array("A", "B", "C") is a very convenient way of creating arrays, but it creates only a 3x1 dimension array. Can I declare a 3x2 or 3x3 dimension array in this way? Thanks, Stefi |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
array function for multidimensional arrays
Try using arrays themselves as arguments:
rbetuk = Array(Array("A", "B", "C"), Array("D", "E", "F"), Array("G", "H", "I")) You can test this by reading the array 'in' and 'out' with the following test code: Sub stefi() rbetuk = Array(Array("A", "B", "C"), _ Array("D", "E", "F"), _ Array("G", "H", "I")) For Each itm In rbetuk For Each itm2 In itm MsgBox itm2 Next 'itm2 Next 'itm End Sub -- Jay "Stefi" wrote: Hi All, rbetuk = Array("A", "B", "C") is a very convenient way of creating arrays, but it creates only a 3x1 dimension array. Can I declare a 3x2 or 3x3 dimension array in this way? Thanks, Stefi |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
array function for multidimensional arrays
Nice trick, thanks Jay!
By the way, another question: how can I find out the order number of an array element within an array? E.g. rbetuk = Array("A", "B", "C") I'd like to know that content of variable, say XVAR="B" is the 1st, 2nd, or 3rd element of the array. Something like ORDERNO = Function???(rbetuk,XVAR) This Function??? function should return 2. Stefi €˛Jay€¯ ezt Ć*rta: Try using arrays themselves as arguments: rbetuk = Array(Array("A", "B", "C"), Array("D", "E", "F"), Array("G", "H", "I")) You can test this by reading the array 'in' and 'out' with the following test code: Sub stefi() rbetuk = Array(Array("A", "B", "C"), _ Array("D", "E", "F"), _ Array("G", "H", "I")) For Each itm In rbetuk For Each itm2 In itm MsgBox itm2 Next 'itm2 Next 'itm End Sub -- Jay "Stefi" wrote: Hi All, rbetuk = Array("A", "B", "C") is a very convenient way of creating arrays, but it creates only a 3x1 dimension array. Can I declare a 3x2 or 3x3 dimension array in this way? Thanks, Stefi |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
array function for multidimensional arrays
I found out that Worksheetfunction.Match does the trick!
Stefi €˛Stefi€¯ ezt Ć*rta: Nice trick, thanks Jay! By the way, another question: how can I find out the order number of an array element within an array? E.g. rbetuk = Array("A", "B", "C") I'd like to know that content of variable, say XVAR="B" is the 1st, 2nd, or 3rd element of the array. Something like ORDERNO = Function???(rbetuk,XVAR) This Function??? function should return 2. Stefi €˛Jay€¯ ezt Ć*rta: Try using arrays themselves as arguments: rbetuk = Array(Array("A", "B", "C"), Array("D", "E", "F"), Array("G", "H", "I")) You can test this by reading the array 'in' and 'out' with the following test code: Sub stefi() rbetuk = Array(Array("A", "B", "C"), _ Array("D", "E", "F"), _ Array("G", "H", "I")) For Each itm In rbetuk For Each itm2 In itm MsgBox itm2 Next 'itm2 Next 'itm End Sub -- Jay "Stefi" wrote: Hi All, rbetuk = Array("A", "B", "C") is a very convenient way of creating arrays, but it creates only a 3x1 dimension array. Can I declare a 3x2 or 3x3 dimension array in this way? Thanks, Stefi |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
array function for multidimensional arrays
That doesn't actually create a 2d array, rather it creates an array of
arrays. Instead of indexing like rbetuk(1,2) you have to use rbetuk(1)(2), not a great problem, but not as we are taught to index into arrays (more like Javascript multi-dimensional arrays). This gives standard 2d arrays, bujt is limited in how big the array can be rdetuk = ActiveSheet.Evaluate("{""A"",""B"",""C"";""D"", ""E"", ""F"";""G"", ""H"", ""I""}") -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jay" wrote in message ... Try using arrays themselves as arguments: rbetuk = Array(Array("A", "B", "C"), Array("D", "E", "F"), Array("G", "H", "I")) You can test this by reading the array 'in' and 'out' with the following test code: Sub stefi() rbetuk = Array(Array("A", "B", "C"), _ Array("D", "E", "F"), _ Array("G", "H", "I")) For Each itm In rbetuk For Each itm2 In itm MsgBox itm2 Next 'itm2 Next 'itm End Sub -- Jay "Stefi" wrote: Hi All, rbetuk = Array("A", "B", "C") is a very convenient way of creating arrays, but it creates only a 3x1 dimension array. Can I declare a 3x2 or 3x3 dimension array in this way? Thanks, Stefi |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
array function for multidimensional arrays
I have never built an array. If i understand from your post the "A"
represents column. If ciorrect how do you get to the row. What i am after is to copy 7 cells in a row to a different wksh this row changes with each entry and would be triggered by entry in column 'J'. Also "J" value must be reduced by 10.00 "J" is formated to currency wksh's to copy to have first row frozen for labeld & scrolling. Would need next row or insert a row on sheets copied to. Have this code to start with Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) Select Case Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=data 6=Motorcycle 7=Indian 8=Native nbrs are for sheets If target.Column = 10 And target.Value 10 Then _ Call CopyStuff(target) End Select End Sub want to copy Data cells in row colmn E F G H I J-10.00 K to donors wksh to next open row in wksh If we can make it happen once I feel i can repeat for other sheets. will need to modify for < 10.00 to different sheet My challenge is to modify for needed change. Old dog new tricks Thanks in advance .. "Bob Phillips" wrote: That doesn't actually create a 2d array, rather it creates an array of arrays. Instead of indexing like rbetuk(1,2) you have to use rbetuk(1)(2), not a great problem, but not as we are taught to index into arrays (more like Javascript multi-dimensional arrays). This gives standard 2d arrays, bujt is limited in how big the array can be rdetuk = ActiveSheet.Evaluate("{""A"",""B"",""C"";""D"", ""E"", ""F"";""G"", ""H"", ""I""}") -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jay" wrote in message ... Try using arrays themselves as arguments: rbetuk = Array(Array("A", "B", "C"), Array("D", "E", "F"), Array("G", "H", "I")) You can test this by reading the array 'in' and 'out' with the following test code: Sub stefi() rbetuk = Array(Array("A", "B", "C"), _ Array("D", "E", "F"), _ Array("G", "H", "I")) For Each itm In rbetuk For Each itm2 In itm MsgBox itm2 Next 'itm2 Next 'itm End Sub -- Jay "Stefi" wrote: Hi All, rbetuk = Array("A", "B", "C") is a very convenient way of creating arrays, but it creates only a 3x1 dimension array. Can I declare a 3x2 or 3x3 dimension array in this way? Thanks, Stefi |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
array function for multidimensional arrays
You don't need an array for that, just copy E:K over, and reduce J by 10
afterwards Sh.Range("E" & Target.Row).Resize(,7).Copy wkSh.Range("A" & iNextFreeRow) wkSh.Range("F" & iNextFreeRow).Value = wkSh.Range("F" & NextFreeRow).Value - 10 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Curt" wrote in message ... I have never built an array. If i understand from your post the "A" represents column. If ciorrect how do you get to the row. What i am after is to copy 7 cells in a row to a different wksh this row changes with each entry and would be triggered by entry in column 'J'. Also "J" value must be reduced by 10.00 "J" is formated to currency wksh's to copy to have first row frozen for labeld & scrolling. Would need next row or insert a row on sheets copied to. Have this code to start with Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) Select Case Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=data 6=Motorcycle 7=Indian 8=Native nbrs are for sheets If target.Column = 10 And target.Value 10 Then _ Call CopyStuff(target) End Select End Sub want to copy Data cells in row colmn E F G H I J-10.00 K to donors wksh to next open row in wksh If we can make it happen once I feel i can repeat for other sheets. will need to modify for < 10.00 to different sheet My challenge is to modify for needed change. Old dog new tricks Thanks in advance . "Bob Phillips" wrote: That doesn't actually create a 2d array, rather it creates an array of arrays. Instead of indexing like rbetuk(1,2) you have to use rbetuk(1)(2), not a great problem, but not as we are taught to index into arrays (more like Javascript multi-dimensional arrays). This gives standard 2d arrays, bujt is limited in how big the array can be rdetuk = ActiveSheet.Evaluate("{""A"",""B"",""C"";""D"", ""E"", ""F"";""G"", ""H"", ""I""}") -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jay" wrote in message ... Try using arrays themselves as arguments: rbetuk = Array(Array("A", "B", "C"), Array("D", "E", "F"), Array("G", "H", "I")) You can test this by reading the array 'in' and 'out' with the following test code: Sub stefi() rbetuk = Array(Array("A", "B", "C"), _ Array("D", "E", "F"), _ Array("G", "H", "I")) For Each itm In rbetuk For Each itm2 In itm MsgBox itm2 Next 'itm2 Next 'itm End Sub -- Jay "Stefi" wrote: Hi All, rbetuk = Array("A", "B", "C") is a very convenient way of creating arrays, but it creates only a 3x1 dimension array. Can I declare a 3x2 or 3x3 dimension array in this way? Thanks, Stefi |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
array function for multidimensional arrays
Thank You for explanitation you make it simple
Thanks Again "Bob Phillips" wrote: You don't need an array for that, just copy E:K over, and reduce J by 10 afterwards Sh.Range("E" & Target.Row).Resize(,7).Copy wkSh.Range("A" & iNextFreeRow) wkSh.Range("F" & iNextFreeRow).Value = wkSh.Range("F" & NextFreeRow).Value - 10 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Curt" wrote in message ... I have never built an array. If i understand from your post the "A" represents column. If ciorrect how do you get to the row. What i am after is to copy 7 cells in a row to a different wksh this row changes with each entry and would be triggered by entry in column 'J'. Also "J" value must be reduced by 10.00 "J" is formated to currency wksh's to copy to have first row frozen for labeld & scrolling. Would need next row or insert a row on sheets copied to. Have this code to start with Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) Select Case Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=data 6=Motorcycle 7=Indian 8=Native nbrs are for sheets If target.Column = 10 And target.Value 10 Then _ Call CopyStuff(target) End Select End Sub want to copy Data cells in row colmn E F G H I J-10.00 K to donors wksh to next open row in wksh If we can make it happen once I feel i can repeat for other sheets. will need to modify for < 10.00 to different sheet My challenge is to modify for needed change. Old dog new tricks Thanks in advance . "Bob Phillips" wrote: That doesn't actually create a 2d array, rather it creates an array of arrays. Instead of indexing like rbetuk(1,2) you have to use rbetuk(1)(2), not a great problem, but not as we are taught to index into arrays (more like Javascript multi-dimensional arrays). This gives standard 2d arrays, bujt is limited in how big the array can be rdetuk = ActiveSheet.Evaluate("{""A"",""B"",""C"";""D"", ""E"", ""F"";""G"", ""H"", ""I""}") -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jay" wrote in message ... Try using arrays themselves as arguments: rbetuk = Array(Array("A", "B", "C"), Array("D", "E", "F"), Array("G", "H", "I")) You can test this by reading the array 'in' and 'out' with the following test code: Sub stefi() rbetuk = Array(Array("A", "B", "C"), _ Array("D", "E", "F"), _ Array("G", "H", "I")) For Each itm In rbetuk For Each itm2 In itm MsgBox itm2 Next 'itm2 Next 'itm End Sub -- Jay "Stefi" wrote: Hi All, rbetuk = Array("A", "B", "C") is a very convenient way of creating arrays, but it creates only a 3x1 dimension array. Can I declare a 3x2 or 3x3 dimension array in this way? Thanks, Stefi |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
array function for multidimensional arrays
pasted in code iNextFreeRow error' invalid or unqualified reference' can you
tell me what I lack knowledge of "Bob Phillips" wrote: You don't need an array for that, just copy E:K over, and reduce J by 10 afterwards Sh.Range("E" & Target.Row).Resize(,7).Copy wkSh.Range("A" & iNextFreeRow) wkSh.Range("F" & iNextFreeRow).Value = wkSh.Range("F" & NextFreeRow).Value - 10 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Curt" wrote in message ... I have never built an array. If i understand from your post the "A" represents column. If ciorrect how do you get to the row. What i am after is to copy 7 cells in a row to a different wksh this row changes with each entry and would be triggered by entry in column 'J'. Also "J" value must be reduced by 10.00 "J" is formated to currency wksh's to copy to have first row frozen for labeld & scrolling. Would need next row or insert a row on sheets copied to. Have this code to start with Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) Select Case Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=data 6=Motorcycle 7=Indian 8=Native nbrs are for sheets If target.Column = 10 And target.Value 10 Then _ Call CopyStuff(target) End Select End Sub want to copy Data cells in row colmn E F G H I J-10.00 K to donors wksh to next open row in wksh If we can make it happen once I feel i can repeat for other sheets. will need to modify for < 10.00 to different sheet My challenge is to modify for needed change. Old dog new tricks Thanks in advance . "Bob Phillips" wrote: That doesn't actually create a 2d array, rather it creates an array of arrays. Instead of indexing like rbetuk(1,2) you have to use rbetuk(1)(2), not a great problem, but not as we are taught to index into arrays (more like Javascript multi-dimensional arrays). This gives standard 2d arrays, bujt is limited in how big the array can be rdetuk = ActiveSheet.Evaluate("{""A"",""B"",""C"";""D"", ""E"", ""F"";""G"", ""H"", ""I""}") -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jay" wrote in message ... Try using arrays themselves as arguments: rbetuk = Array(Array("A", "B", "C"), Array("D", "E", "F"), Array("G", "H", "I")) You can test this by reading the array 'in' and 'out' with the following test code: Sub stefi() rbetuk = Array(Array("A", "B", "C"), _ Array("D", "E", "F"), _ Array("G", "H", "I")) For Each itm In rbetuk For Each itm2 In itm MsgBox itm2 Next 'itm2 Next 'itm End Sub -- Jay "Stefi" wrote: Hi All, rbetuk = Array("A", "B", "C") is a very convenient way of creating arrays, but it creates only a 3x1 dimension array. Can I declare a 3x2 or 3x3 dimension array in this way? Thanks, Stefi |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
array function for multidimensional arrays
I tried till blue want to copy from Data E::L will trigger on j entry
to Donors A:H J-10.00 paste will need next row on Donors sheet Thanks Bob have a great weekend Thank You "Bob Phillips" wrote: You don't need an array for that, just copy E:K over, and reduce J by 10 afterwards Sh.Range("E" & Target.Row).Resize(,7).Copy wkSh.Range("A" & iNextFreeRow) wkSh.Range("F" & iNextFreeRow).Value = wkSh.Range("F" & NextFreeRow).Value - 10 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Curt" wrote in message ... I have never built an array. If i understand from your post the "A" represents column. If ciorrect how do you get to the row. What i am after is to copy 7 cells in a row to a different wksh this row changes with each entry and would be triggered by entry in column 'J'. Also "J" value must be reduced by 10.00 "J" is formated to currency wksh's to copy to have first row frozen for labeld & scrolling. Would need next row or insert a row on sheets copied to. Have this code to start with Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal target As Range) Select Case Sh.Name Case "Data", "Motorcycle", "Indian", "Native" '1=data 6=Motorcycle 7=Indian 8=Native nbrs are for sheets If target.Column = 10 And target.Value 10 Then _ Call CopyStuff(target) End Select End Sub want to copy Data cells in row colmn E F G H I J-10.00 K to donors wksh to next open row in wksh If we can make it happen once I feel i can repeat for other sheets. will need to modify for < 10.00 to different sheet My challenge is to modify for needed change. Old dog new tricks Thanks in advance . "Bob Phillips" wrote: That doesn't actually create a 2d array, rather it creates an array of arrays. Instead of indexing like rbetuk(1,2) you have to use rbetuk(1)(2), not a great problem, but not as we are taught to index into arrays (more like Javascript multi-dimensional arrays). This gives standard 2d arrays, bujt is limited in how big the array can be rdetuk = ActiveSheet.Evaluate("{""A"",""B"",""C"";""D"", ""E"", ""F"";""G"", ""H"", ""I""}") -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jay" wrote in message ... Try using arrays themselves as arguments: rbetuk = Array(Array("A", "B", "C"), Array("D", "E", "F"), Array("G", "H", "I")) You can test this by reading the array 'in' and 'out' with the following test code: Sub stefi() rbetuk = Array(Array("A", "B", "C"), _ Array("D", "E", "F"), _ Array("G", "H", "I")) For Each itm In rbetuk For Each itm2 In itm MsgBox itm2 Next 'itm2 Next 'itm End Sub -- Jay "Stefi" wrote: Hi All, rbetuk = Array("A", "B", "C") is a very convenient way of creating arrays, but it creates only a 3x1 dimension array. Can I declare a 3x2 or 3x3 dimension array in this way? Thanks, Stefi |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
array function for multidimensional arrays
Thanks Bob, it worked great. Do you know the size limit? In my present task I
had to create only a 2x7 array, but for the futere it would be good to know it. Stefi €˛Bob Phillips€¯ ezt Ć*rta: That doesn't actually create a 2d array, rather it creates an array of arrays. Instead of indexing like rbetuk(1,2) you have to use rbetuk(1)(2), not a great problem, but not as we are taught to index into arrays (more like Javascript multi-dimensional arrays). This gives standard 2d arrays, bujt is limited in how big the array can be rdetuk = ActiveSheet.Evaluate("{""A"",""B"",""C"";""D"", ""E"", ""F"";""G"", ""H"", ""I""}") -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jay" wrote in message ... Try using arrays themselves as arguments: rbetuk = Array(Array("A", "B", "C"), Array("D", "E", "F"), Array("G", "H", "I")) You can test this by reading the array 'in' and 'out' with the following test code: Sub stefi() rbetuk = Array(Array("A", "B", "C"), _ Array("D", "E", "F"), _ Array("G", "H", "I")) For Each itm In rbetuk For Each itm2 In itm MsgBox itm2 Next 'itm2 Next 'itm End Sub -- Jay "Stefi" wrote: Hi All, rbetuk = Array("A", "B", "C") is a very convenient way of creating arrays, but it creates only a 3x1 dimension array. Can I declare a 3x2 or 3x3 dimension array in this way? Thanks, Stefi |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
array function for multidimensional arrays
Not offhand Stefi. I would have to experiment to find out. I only use for
small arrays like yours. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Stefi" wrote in message ... Thanks Bob, it worked great. Do you know the size limit? In my present task I had to create only a 2x7 array, but for the futere it would be good to know it. Stefi "Bob Phillips" ezt ķrta: That doesn't actually create a 2d array, rather it creates an array of arrays. Instead of indexing like rbetuk(1,2) you have to use rbetuk(1)(2), not a great problem, but not as we are taught to index into arrays (more like Javascript multi-dimensional arrays). This gives standard 2d arrays, bujt is limited in how big the array can be rdetuk = ActiveSheet.Evaluate("{""A"",""B"",""C"";""D"", ""E"", ""F"";""G"", ""H"", ""I""}") -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jay" wrote in message ... Try using arrays themselves as arguments: rbetuk = Array(Array("A", "B", "C"), Array("D", "E", "F"), Array("G", "H", "I")) You can test this by reading the array 'in' and 'out' with the following test code: Sub stefi() rbetuk = Array(Array("A", "B", "C"), _ Array("D", "E", "F"), _ Array("G", "H", "I")) For Each itm In rbetuk For Each itm2 In itm MsgBox itm2 Next 'itm2 Next 'itm End Sub -- Jay "Stefi" wrote: Hi All, rbetuk = Array("A", "B", "C") is a very convenient way of creating arrays, but it creates only a 3x1 dimension array. Can I declare a 3x2 or 3x3 dimension array in this way? Thanks, Stefi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic multidimensional arrays | Excel Programming | |||
MultiDimensional Dynamic Arrays | Excel Programming | |||
Declare Multidimensional Arrays | Excel Programming | |||
Multidimensional Arrays - VBA | Excel Programming | |||
Multidimensional Arrays - VBA | Excel Programming |