Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables in Declaring an Array
Dim myArray(0 To n) As String
The dimensions of this array change throughout. Why can't I use the variable 'n' in the declaration? And, more importantly, can someone help me solve this problem? Thanks, Jimmy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables in Declaring an Array
Take a look for help on the redim statement (note the key work preserve is
required to not clear the values of the array when it is resized.) dim myArray() as sting dim n as long n = 0 redim preserve myArray(n) myarray(0) = "this" n = 1 redim preserve myarray(n) myarray(0) = "that" -- HTH... Jim Thomlinson "Jimmy" wrote: Dim myArray(0 To n) As String The dimensions of this array change throughout. Why can't I use the variable 'n' in the declaration? And, more importantly, can someone help me solve this problem? Thanks, Jimmy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables in Declaring an Array
2 things jim
you have a typo he dim myArray() as sting (string) and should this be: array(n) = "that" -- Gary "Jim Thomlinson" wrote in message ... Take a look for help on the redim statement (note the key work preserve is required to not clear the values of the array when it is resized.) dim myArray() as sting dim n as long n = 0 redim preserve myArray(n) myarray(0) = "this" n = 1 redim preserve myarray(n) myarray(0) = "that" -- HTH... Jim Thomlinson "Jimmy" wrote: Dim myArray(0 To n) As String The dimensions of this array change throughout. Why can't I use the variable 'n' in the declaration? And, more importantly, can someone help me solve this problem? Thanks, Jimmy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables in Declaring an Array
My typing takes a little to be desired... I should really proof read before
hitting the Post button. You are correct on the String thing. As for the Array(n) = "that" it was just a quick n dirty demo. Yes n probably makes more sense... -- HTH... Jim Thomlinson "Gary Keramidas" wrote: 2 things jim you have a typo he dim myArray() as sting (string) and should this be: array(n) = "that" -- Gary "Jim Thomlinson" wrote in message ... Take a look for help on the redim statement (note the key work preserve is required to not clear the values of the array when it is resized.) dim myArray() as sting dim n as long n = 0 redim preserve myArray(n) myarray(0) = "this" n = 1 redim preserve myarray(n) myarray(0) = "that" -- HTH... Jim Thomlinson "Jimmy" wrote: Dim myArray(0 To n) As String The dimensions of this array change throughout. Why can't I use the variable 'n' in the declaration? And, more importantly, can someone help me solve this problem? Thanks, Jimmy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables in Declaring an Array
i'm not as knowledgeable as you, i was just checking how it worked so i could
learn more about arrays. noticed how i "asked" instead of saying "this is how it should be". i didn't know for sure. it should also probably be: myarray(n) = "this" right? i know i don't type very well, so i understand the typo. thanks -- Gary "Jim Thomlinson" wrote in message ... My typing takes a little to be desired... I should really proof read before hitting the Post button. You are correct on the String thing. As for the Array(n) = "that" it was just a quick n dirty demo. Yes n probably makes more sense... -- HTH... Jim Thomlinson "Gary Keramidas" wrote: 2 things jim you have a typo he dim myArray() as sting (string) and should this be: array(n) = "that" -- Gary "Jim Thomlinson" wrote in message ... Take a look for help on the redim statement (note the key work preserve is required to not clear the values of the array when it is resized.) dim myArray() as sting dim n as long n = 0 redim preserve myArray(n) myarray(0) = "this" n = 1 redim preserve myarray(n) myarray(0) = "that" -- HTH... Jim Thomlinson "Jimmy" wrote: Dim myArray(0 To n) As String The dimensions of this array change throughout. Why can't I use the variable 'n' in the declaration? And, more importantly, can someone help me solve this problem? Thanks, Jimmy |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables in Declaring an Array
Looks like you have the hang of ReDim to me... Like I said in my original
post the only trick is to understanding the use of the preserver key word. Preserve adds some overhead however so my origninal code should have been... dim myArray() as sting dim n as long n = 0 redim myArray(n) 'Note no Preserve as there is nothing to save myarray(n) = "this" n = 1 redim preserve myarray(n) myarray(n) = "that" Redim essentially makes a new array that is the size that you specify and then points the array variable at the new array. The preserve key word tells the compiler to copy over the values from the old array to the new array... -- HTH... Jim Thomlinson "Gary Keramidas" wrote: i'm not as knowledgeable as you, i was just checking how it worked so i could learn more about arrays. noticed how i "asked" instead of saying "this is how it should be". i didn't know for sure. it should also probably be: myarray(n) = "this" right? i know i don't type very well, so i understand the typo. thanks -- Gary "Jim Thomlinson" wrote in message ... My typing takes a little to be desired... I should really proof read before hitting the Post button. You are correct on the String thing. As for the Array(n) = "that" it was just a quick n dirty demo. Yes n probably makes more sense... -- HTH... Jim Thomlinson "Gary Keramidas" wrote: 2 things jim you have a typo he dim myArray() as sting (string) and should this be: array(n) = "that" -- Gary "Jim Thomlinson" wrote in message ... Take a look for help on the redim statement (note the key work preserve is required to not clear the values of the array when it is resized.) dim myArray() as sting dim n as long n = 0 redim preserve myArray(n) myarray(0) = "this" n = 1 redim preserve myarray(n) myarray(0) = "that" -- HTH... Jim Thomlinson "Jimmy" wrote: Dim myArray(0 To n) As String The dimensions of this array change throughout. Why can't I use the variable 'n' in the declaration? And, more importantly, can someone help me solve this problem? Thanks, Jimmy |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables in Declaring an Array
So Jim, are you saying that if Jimmy inserts the lines of code that you have
provided that the can then use "Dim myArray(0 To n) As String and his array will run properly? Or am I totally confused with this. "Jim Thomlinson" wrote: Looks like you have the hang of ReDim to me... Like I said in my original post the only trick is to understanding the use of the preserver key word. Preserve adds some overhead however so my origninal code should have been... dim myArray() as sting dim n as long n = 0 redim myArray(n) 'Note no Preserve as there is nothing to save myarray(n) = "this" n = 1 redim preserve myarray(n) myarray(n) = "that" Redim essentially makes a new array that is the size that you specify and then points the array variable at the new array. The preserve key word tells the compiler to copy over the values from the old array to the new array... -- HTH... Jim Thomlinson "Gary Keramidas" wrote: i'm not as knowledgeable as you, i was just checking how it worked so i could learn more about arrays. noticed how i "asked" instead of saying "this is how it should be". i didn't know for sure. it should also probably be: myarray(n) = "this" right? i know i don't type very well, so i understand the typo. thanks -- Gary "Jim Thomlinson" wrote in message ... My typing takes a little to be desired... I should really proof read before hitting the Post button. You are correct on the String thing. As for the Array(n) = "that" it was just a quick n dirty demo. Yes n probably makes more sense... -- HTH... Jim Thomlinson "Gary Keramidas" wrote: 2 things jim you have a typo he dim myArray() as sting (string) and should this be: array(n) = "that" -- Gary "Jim Thomlinson" wrote in message ... Take a look for help on the redim statement (note the key work preserve is required to not clear the values of the array when it is resized.) dim myArray() as sting dim n as long n = 0 redim preserve myArray(n) myarray(0) = "this" n = 1 redim preserve myarray(n) myarray(0) = "that" -- HTH... Jim Thomlinson "Jimmy" wrote: Dim myArray(0 To n) As String The dimensions of this array change throughout. Why can't I use the variable 'n' in the declaration? And, more importantly, can someone help me solve this problem? Thanks, Jimmy |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables in Declaring an Array
You are confused a bit... You can not declare a variable with an undefined
attribute. The array must be declared undimensioned. It can then be dimensioned at run time as I have shown. dim myArray () as String 'Undimensioned array redim myArray(100) 'Dimension the array with 101 elements 0 to 100 myArray(0) = "tada" 'add values to the array -- HTH... Jim Thomlinson "JLGWhiz" wrote: So Jim, are you saying that if Jimmy inserts the lines of code that you have provided that the can then use "Dim myArray(0 To n) As String and his array will run properly? Or am I totally confused with this. "Jim Thomlinson" wrote: Looks like you have the hang of ReDim to me... Like I said in my original post the only trick is to understanding the use of the preserver key word. Preserve adds some overhead however so my origninal code should have been... dim myArray() as sting dim n as long n = 0 redim myArray(n) 'Note no Preserve as there is nothing to save myarray(n) = "this" n = 1 redim preserve myarray(n) myarray(n) = "that" Redim essentially makes a new array that is the size that you specify and then points the array variable at the new array. The preserve key word tells the compiler to copy over the values from the old array to the new array... -- HTH... Jim Thomlinson "Gary Keramidas" wrote: i'm not as knowledgeable as you, i was just checking how it worked so i could learn more about arrays. noticed how i "asked" instead of saying "this is how it should be". i didn't know for sure. it should also probably be: myarray(n) = "this" right? i know i don't type very well, so i understand the typo. thanks -- Gary "Jim Thomlinson" wrote in message ... My typing takes a little to be desired... I should really proof read before hitting the Post button. You are correct on the String thing. As for the Array(n) = "that" it was just a quick n dirty demo. Yes n probably makes more sense... -- HTH... Jim Thomlinson "Gary Keramidas" wrote: 2 things jim you have a typo he dim myArray() as sting (string) and should this be: array(n) = "that" -- Gary "Jim Thomlinson" wrote in message ... Take a look for help on the redim statement (note the key work preserve is required to not clear the values of the array when it is resized.) dim myArray() as sting dim n as long n = 0 redim preserve myArray(n) myarray(0) = "this" n = 1 redim preserve myarray(n) myarray(0) = "that" -- HTH... Jim Thomlinson "Jimmy" wrote: Dim myArray(0 To n) As String The dimensions of this array change throughout. Why can't I use the variable 'n' in the declaration? And, more importantly, can someone help me solve this problem? Thanks, Jimmy |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables in Declaring an Array
you can't but you have 2 options.
You can declare const like const MAX_ITEMS AS INTEGER before declaring variables (see example 1) Another option is not using a fixed array but a variable size array (see example 2) Hope this helps. example 1 CONST MAX_ITEMS AS INTEGER=10 Dim myArray(0 To MAX_ITEMS ) As String Example 2 option base 1 dim n as integer dim myArray() as string n=15 redim myArray(1 to n) Jimmy wrote: Dim myArray(0 To n) As String The dimensions of this array change throughout. Why can't I use the variable 'n' in the declaration? And, more importantly, can someone help me solve this problem? Thanks, Jimmy |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables in Declaring an Array
Jim,
How do I populate myArray with Range("C1:C100")? e.g. I want myArray(0) to equal "C1".value, myArray(1) to equal "C2".value, (& so on) btw, This whole conversation has been emensly helpful. keep it up! Thanks, Jimmy Jim Thomlinson wrote: You are confused a bit... You can not declare a variable with an undefined attribute. The array must be declared undimensioned. It can then be dimensioned at run time as I have shown. dim myArray () as String 'Undimensioned array redim myArray(100) 'Dimension the array with 101 elements 0 to 100 myArray(0) = "tada" 'add values to the array -- HTH... Jim Thomlinson "JLGWhiz" wrote: So Jim, are you saying that if Jimmy inserts the lines of code that you have provided that the can then use "Dim myArray(0 To n) As String and his array will run properly? Or am I totally confused with this. "Jim Thomlinson" wrote: Looks like you have the hang of ReDim to me... Like I said in my original post the only trick is to understanding the use of the preserver key word. Preserve adds some overhead however so my origninal code should have been... dim myArray() as sting dim n as long n = 0 redim myArray(n) 'Note no Preserve as there is nothing to save myarray(n) = "this" n = 1 redim preserve myarray(n) myarray(n) = "that" Redim essentially makes a new array that is the size that you specify and then points the array variable at the new array. The preserve key word tells the compiler to copy over the values from the old array to the new array... -- HTH... Jim Thomlinson "Gary Keramidas" wrote: i'm not as knowledgeable as you, i was just checking how it worked so i could learn more about arrays. noticed how i "asked" instead of saying "this is how it should be". i didn't know for sure. it should also probably be: myarray(n) = "this" right? i know i don't type very well, so i understand the typo. thanks -- Gary "Jim Thomlinson" wrote in message ... My typing takes a little to be desired... I should really proof read before hitting the Post button. You are correct on the String thing. As for the Array(n) = "that" it was just a quick n dirty demo. Yes n probably makes more sense... -- HTH... Jim Thomlinson "Gary Keramidas" wrote: 2 things jim you have a typo he dim myArray() as sting (string) and should this be: array(n) = "that" -- Gary "Jim Thomlinson" wrote in message ... Take a look for help on the redim statement (note the key work preserve is required to not clear the values of the array when it is resized.) dim myArray() as sting dim n as long n = 0 redim preserve myArray(n) myarray(0) = "this" n = 1 redim preserve myarray(n) myarray(0) = "that" -- HTH... Jim Thomlinson "Jimmy" wrote: Dim myArray(0 To n) As String The dimensions of this array change throughout. Why can't I use the variable 'n' in the declaration? And, more importantly, can someone help me solve this problem? Thanks, Jimmy |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables in Declaring an Array
That was a stupid. Please ignore.
Jimmy wrote: Jim, How do I populate myArray with Range("C1:C100")? e.g. I want myArray(0) to equal "C1".value, myArray(1) to equal "C2".value, (& so on) btw, This whole conversation has been emensly helpful. keep it up! Thanks, Jimmy Jim Thomlinson wrote: You are confused a bit... You can not declare a variable with an undefined attribute. The array must be declared undimensioned. It can then be dimensioned at run time as I have shown. dim myArray () as String 'Undimensioned array redim myArray(100) 'Dimension the array with 101 elements 0 to 100 myArray(0) = "tada" 'add values to the array -- HTH... Jim Thomlinson "JLGWhiz" wrote: So Jim, are you saying that if Jimmy inserts the lines of code that you have provided that the can then use "Dim myArray(0 To n) As String and his array will run properly? Or am I totally confused with this. "Jim Thomlinson" wrote: Looks like you have the hang of ReDim to me... Like I said in my original post the only trick is to understanding the use of the preserver key word. Preserve adds some overhead however so my origninal code should have been... dim myArray() as sting dim n as long n = 0 redim myArray(n) 'Note no Preserve as there is nothing to save myarray(n) = "this" n = 1 redim preserve myarray(n) myarray(n) = "that" Redim essentially makes a new array that is the size that you specify and then points the array variable at the new array. The preserve key word tells the compiler to copy over the values from the old array to the new array... -- HTH... Jim Thomlinson "Gary Keramidas" wrote: i'm not as knowledgeable as you, i was just checking how it worked so i could learn more about arrays. noticed how i "asked" instead of saying "this is how it should be". i didn't know for sure. it should also probably be: myarray(n) = "this" right? i know i don't type very well, so i understand the typo. thanks -- Gary "Jim Thomlinson" wrote in message ... My typing takes a little to be desired... I should really proof read before hitting the Post button. You are correct on the String thing. As for the Array(n) = "that" it was just a quick n dirty demo. Yes n probably makes more sense... -- HTH... Jim Thomlinson "Gary Keramidas" wrote: 2 things jim you have a typo he dim myArray() as sting (string) and should this be: array(n) = "that" -- Gary "Jim Thomlinson" wrote in message ... Take a look for help on the redim statement (note the key work preserve is required to not clear the values of the array when it is resized.) dim myArray() as sting dim n as long n = 0 redim preserve myArray(n) myarray(0) = "this" n = 1 redim preserve myarray(n) myarray(0) = "that" -- HTH... Jim Thomlinson "Jimmy" wrote: Dim myArray(0 To n) As String The dimensions of this array change throughout. Why can't I use the variable 'n' in the declaration? And, more importantly, can someone help me solve this problem? Thanks, Jimmy |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables in Declaring an Array
Jim,
I'm still on the same project, but our team has decided to use a two-dim array. How can I get "redim preserve myarray(n,o)" to work? I'm having the worst time with this. Thanks so much, Jimmy Jim Thomlinson wrote: Looks like you have the hang of ReDim to me... Like I said in my original post the only trick is to understanding the use of the preserver key word. Preserve adds some overhead however so my origninal code should have been... dim myArray() as sting dim n as long n = 0 redim myArray(n) 'Note no Preserve as there is nothing to save myarray(n) = "this" n = 1 redim preserve myarray(n) myarray(n) = "that" Redim essentially makes a new array that is the size that you specify and then points the array variable at the new array. The preserve key word tells the compiler to copy over the values from the old array to the new array... -- HTH... Jim Thomlinson "Gary Keramidas" wrote: i'm not as knowledgeable as you, i was just checking how it worked so i could learn more about arrays. noticed how i "asked" instead of saying "this is how it should be". i didn't know for sure. it should also probably be: myarray(n) = "this" right? i know i don't type very well, so i understand the typo. thanks -- Gary "Jim Thomlinson" wrote in message ... My typing takes a little to be desired... I should really proof read before hitting the Post button. You are correct on the String thing. As for the Array(n) = "that" it was just a quick n dirty demo. Yes n probably makes more sense... -- HTH... Jim Thomlinson "Gary Keramidas" wrote: 2 things jim you have a typo he dim myArray() as sting (string) and should this be: array(n) = "that" -- Gary "Jim Thomlinson" wrote in message ... Take a look for help on the redim statement (note the key work preserve is required to not clear the values of the array when it is resized.) dim myArray() as sting dim n as long n = 0 redim preserve myArray(n) myarray(0) = "this" n = 1 redim preserve myarray(n) myarray(0) = "that" -- HTH... Jim Thomlinson "Jimmy" wrote: Dim myArray(0 To n) As String The dimensions of this array change throughout. Why can't I use the variable 'n' in the declaration? And, more importantly, can someone help me solve this problem? Thanks, Jimmy |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables in Declaring an Array
Jimmy wrote:
Jim, I'm still on the same project, but our team has decided to use a two-dim array. How can I get "redim preserve myarray(n,o)" to work? I'm having the worst time with this. Thanks so much, Jimmy A little more info would be helpful. I suspect your difficulty arises from the fact that the ReDim Preserve structure will allow you to change only the last dimension of the array. E.g., if the array has been dimensioned with 2 "rows" and 3 "columns", you can use ReDim Preserve to change the number of "columns" but not the number of "rows". You might want to post back with a sample code snippet if that's your difficulty. Alan Beban |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables in Declaring an Array
Thanks, Alan.
I got a little side tracked on another "%^#@(*&^*($" today. But when I get back to this one, I'll post more here. In the mean time, I have another question that I'm about to ask on a separate posting. Thanks again, Jimmy Alan Beban wrote: Jimmy wrote: Jim, I'm still on the same project, but our team has decided to use a two-dim array. How can I get "redim preserve myarray(n,o)" to work? I'm having the worst time with this. Thanks so much, Jimmy A little more info would be helpful. I suspect your difficulty arises from the fact that the ReDim Preserve structure will allow you to change only the last dimension of the array. E.g., if the array has been dimensioned with 2 "rows" and 3 "columns", you can use ReDim Preserve to change the number of "columns" but not the number of "rows". You might want to post back with a sample code snippet if that's your difficulty. Alan Beban |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables in Declaring an Array
First of all, thanks to all who endeavor to help me with this project:
Here it is: I want to make a 2dim array. The current code populates that array at Row15 (see below). I would like to change this 1dim to a 2dim The main problem is that I am constantly refrishing the array at either row33 or row35. Thank you in advance, Jimmy 1 ent_ku = Range("B2").Value 2 ent_or= Range("F2").Value 3 currentlastrow = Range("B65536").End(xlUp).Row 4 j = 2 5 pvFlag = False 6 ifMatch = False 7 Range("A1").Select 8 9 For i = Range("B2").Row To currentlastrow Step 1 10 h = i 11 current_Desc = Range("D" & i).Value 12 If Range("B" & i).Value = ent_ku Then 13 ifMatch = True 14 ReDim Preserve myArray(q) 15 myArray(q) = Range("F" & i).Value 16 If Range("F" & i).Value = "PVJco" Then 17 pvFlag = True 18 pvPrice = Range("I" & i).Value 19 pvChgDate = Range("J" & i).Value 20 End If 21 22 q = q + 1 23 24 ElseIf pvFlag = True Then 25 q = 0 26 locust = Application.Min(Range("I" & j, "I" & (i - 1))) 27 percentAbv = (pvPrice - locust) / locust 28 lowCompet = Application.Min(Range("I" & j, "I" & (i - 1))).Row 29 i = i - 1 30 31 j = i 32 pvFlag = False 33 ReDim myArray(q) ElseIf pvFlag = False Then 34 q = 0 35 ReDim myArray(q) 36 i = i - 1 37 End If 38 39 ActiveCell.Offset(1, 0).Select 40 ent_ku = Range("B" & h).Value 41 42 Next Jimmy wrote: Thanks, Alan. I got a little side tracked on another "%^#@(*&^*($" today. But when I get back to this one, I'll post more here. In the mean time, I have another question that I'm about to ask on a separate posting. Thanks again, Jimmy Alan Beban wrote: Jimmy wrote: Jim, I'm still on the same project, but our team has decided to use a two-dim array. How can I get "redim preserve myarray(n,o)" to work? I'm having the worst time with this. Thanks so much, Jimmy A little more info would be helpful. I suspect your difficulty arises from the fact that the ReDim Preserve structure will allow you to change only the last dimension of the array. E.g., if the array has been dimensioned with 2 "rows" and 3 "columns", you can use ReDim Preserve to change the number of "columns" but not the number of "rows". You might want to post back with a sample code snippet if that's your difficulty. Alan Beban |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables in Declaring an Array
It would be easier on responders if you were to use a simpler example to
focus on the problem you're trying to solve, a la Dim arr() ReDim arr(2,3) 'or whatever '...whatever ReDim arr(?,?) 'or whatever Alan Beban Jimmy wrote: First of all, thanks to all who endeavor to help me with this project: Here it is: I want to make a 2dim array. The current code populates that array at Row15 (see below). I would like to change this 1dim to a 2dim The main problem is that I am constantly refrishing the array at either row33 or row35. Thank you in advance, Jimmy 1 ent_ku = Range("B2").Value 2 ent_or= Range("F2").Value 3 currentlastrow = Range("B65536").End(xlUp).Row 4 j = 2 5 pvFlag = False 6 ifMatch = False 7 Range("A1").Select 8 9 For i = Range("B2").Row To currentlastrow Step 1 10 h = i 11 current_Desc = Range("D" & i).Value 12 If Range("B" & i).Value = ent_ku Then 13 ifMatch = True 14 ReDim Preserve myArray(q) 15 myArray(q) = Range("F" & i).Value 16 If Range("F" & i).Value = "PVJco" Then 17 pvFlag = True 18 pvPrice = Range("I" & i).Value 19 pvChgDate = Range("J" & i).Value 20 End If 21 22 q = q + 1 23 24 ElseIf pvFlag = True Then 25 q = 0 26 locust = Application.Min(Range("I" & j, "I" & (i - 1))) 27 percentAbv = (pvPrice - locust) / locust 28 lowCompet = Application.Min(Range("I" & j, "I" & (i - 1))).Row 29 i = i - 1 30 31 j = i 32 pvFlag = False 33 ReDim myArray(q) ElseIf pvFlag = False Then 34 q = 0 35 ReDim myArray(q) 36 i = i - 1 37 End If 38 39 ActiveCell.Offset(1, 0).Select 40 ent_ku = Range("B" & h).Value 41 42 Next Jimmy wrote: Thanks, Alan. I got a little side tracked on another "%^#@(*&^*($" today. But when I get back to this one, I'll post more here. In the mean time, I have another question that I'm about to ask on a separate posting. Thanks again, Jimmy Alan Beban wrote: Jimmy wrote: Jim, I'm still on the same project, but our team has decided to use a two-dim array. How can I get "redim preserve myarray(n,o)" to work? I'm having the worst time with this. Thanks so much, Jimmy A little more info would be helpful. I suspect your difficulty arises from the fact that the ReDim Preserve structure will allow you to change only the last dimension of the array. E.g., if the array has been dimensioned with 2 "rows" and 3 "columns", you can use ReDim Preserve to change the number of "columns" but not the number of "rows". You might want to post back with a sample code snippet if that's your difficulty. Alan Beban |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variables in Declaring an Array
You're right, Alan.
Though, the other half of the time I am directed to make my sample codes longer and more detailed. Go Figure. I think that I figured out a way to do it without trying to use a 2dim array that must refresh itself. Let's keep our fingers crossed that this will be the magic bullet that I am looking for. I apprciate all you help. Thanks, Jimmy Alan Beban wrote: It would be easier on responders if you were to use a simpler example to focus on the problem you're trying to solve, a la Dim arr() ReDim arr(2,3) 'or whatever '...whatever ReDim arr(?,?) 'or whatever Alan Beban Jimmy wrote: First of all, thanks to all who endeavor to help me with this project: Here it is: I want to make a 2dim array. The current code populates that array at Row15 (see below). I would like to change this 1dim to a 2dim The main problem is that I am constantly refrishing the array at either row33 or row35. Thank you in advance, Jimmy 1 ent_ku = Range("B2").Value 2 ent_or= Range("F2").Value 3 currentlastrow = Range("B65536").End(xlUp).Row 4 j = 2 5 pvFlag = False 6 ifMatch = False 7 Range("A1").Select 8 9 For i = Range("B2").Row To currentlastrow Step 1 10 h = i 11 current_Desc = Range("D" & i).Value 12 If Range("B" & i).Value = ent_ku Then 13 ifMatch = True 14 ReDim Preserve myArray(q) 15 myArray(q) = Range("F" & i).Value 16 If Range("F" & i).Value = "PVJco" Then 17 pvFlag = True 18 pvPrice = Range("I" & i).Value 19 pvChgDate = Range("J" & i).Value 20 End If 21 22 q = q + 1 23 24 ElseIf pvFlag = True Then 25 q = 0 26 locust = Application.Min(Range("I" & j, "I" & (i - 1))) 27 percentAbv = (pvPrice - locust) / locust 28 lowCompet = Application.Min(Range("I" & j, "I" & (i - 1))).Row 29 i = i - 1 30 31 j = i 32 pvFlag = False 33 ReDim myArray(q) ElseIf pvFlag = False Then 34 q = 0 35 ReDim myArray(q) 36 i = i - 1 37 End If 38 39 ActiveCell.Offset(1, 0).Select 40 ent_ku = Range("B" & h).Value 41 42 Next Jimmy wrote: Thanks, Alan. I got a little side tracked on another "%^#@(*&^*($" today. But when I get back to this one, I'll post more here. In the mean time, I have another question that I'm about to ask on a separate posting. Thanks again, Jimmy Alan Beban wrote: Jimmy wrote: Jim, I'm still on the same project, but our team has decided to use a two-dim array. How can I get "redim preserve myarray(n,o)" to work? I'm having the worst time with this. Thanks so much, Jimmy A little more info would be helpful. I suspect your difficulty arises from the fact that the ReDim Preserve structure will allow you to change only the last dimension of the array. E.g., if the array has been dimensioned with 2 "rows" and 3 "columns", you can use ReDim Preserve to change the number of "columns" but not the number of "rows". You might want to post back with a sample code snippet if that's your difficulty. Alan Beban |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Declaring variables | Excel Discussion (Misc queries) | |||
Declaring variables | Excel Programming | |||
Declaring Variables | Excel Programming | |||
Declaring Variables | Excel Programming | |||
Declaring Variables | Excel Programming |