Populate an array without Iteration

[Joseph.Marro]

Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I have searched everywhere and I am not sure if I can even accomplish this task. I know you can populate range values into an array without iteration. However, is it possible to convert a range into an array of row numbers without iteration? <o></o>
<o></o>
Example:

Sub Example()
Dim myRange As Range
Dim myArray() As Variant
Set myRange = Range("A23:A31")
myArray = myRange
Set myRange = Nothing
End Sub

<o></o>
How do I Modify this to produce an array containing the row numbers {23,24,25,26,27,28,29,30,31}? Is it even possible without iteration?<o></o>
<o></o>
Thank you,<o></o>
<o></o>
Joseph Marro<o></o>
<o></o>

 

[xenou]

It matters not. The process will take milliseconds no matter how you slice or dice it. If you want, use the approaches suggested earlier. You could just do this:

dim a
a = Array(21,22,23,24,25,26,27,28,29,30,31,32,33,34)

The point of my last (cheeky) post was that a series of numbers doesn't need to exist in an array since it can be created on the fly when you need it.

I may be wrong but regarding for/each it applies to collections, not simple arrays of primitive values.

Note: agree with Norie. Slow code in Excel is usually not a matter of understanding how to micro-optimize loading arrays but using good strategies that are appropriate for an Excel VBA environment.

 

[GTO]

hi Joseph,

I read your questions a bit differently than Alex and Norie, as I believe you are referring to looping (in any manner) through a range as significantly slower.

...I know I could use a For Each Cell In Range loop to accomplish the task, but it is horrifically slow. I was thinking it would be faster to stuff the entire range into an array and loop through it using a For Next Loop...

Maybe I am incorrect but I was under the impression that a for loop on an array is significantly faster than a for each loop on a range...

If that is what you are asking, YES! You can find your way through an array WAYYY quicker than a range.

If the blonde guy is mis-reading... Oopsie?

Mark

 

[Joseph.Marro]

Norie & Xenou,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
The code I originally posted was a overly simplified version of the overall task I’m looking to accomplish. I was tasked to develop a method for matching alphanumeric strings based on a percentage of match (exact match does not exist). Using a combination of Damerau Levenshtein Edit Distance, Longest Common Sub-String Algorithm and regular expression pattern matching. Needless to say, this is an expensive process for conduct comparison of two strings let alone thousands. I have optimized this portion to the best of my ability. Now that I have the comparison method I am looking to apply it to a function where the user selects a cell containing the string they want to match and a range they want to match the string against. Unfortunately, the range is upwards of 17,000 cells. I found that stuffing the range values into an array and looping through the array was fast then looping through the range. However, if I have to populate the array using a for loop, I might as well loop through the range. The end result of the function is to return row number (Just like the Match() function but a hell of a lot more complicated). I hope this help better explain what I am trying to do.<o></o>
<o></o>
Thank you,<o></o>
<o></o>
Joseph Marro<o></o>

 

[Norie]

Mark

I actually thought it was a range - the OP did mention that using a For Each cell in range loop was slow.

 

[Joseph.Marro]

GTO,

Yes, that is exactly the point I was trying to get at. This code has me a bit numb at the moment. I am trying to make it faster anyway I can.

Thank you,

Joseph Marro

 

[xenou]

Perhaps then:

Dim a As Variant
a = Range(A1:J1000)[COLOR="Blue"].Value[/COLOR]

This results in an array containing the cell values.
However, the array is 2 dimensional (rows/columns) and always 1-based.

 

[GTO]

Mark

I actually thought it was a range - the OP did mention that using a For Each cell in range loop was slow.

Hi Norie

Okay, an Oopsie on my part. I can still use blonde as an exxcuse, right?

...I was tasked to develop a method for matching alphanumeric strings based on a percentage of match (exact match does not exist). Using a combination of Damerau Levenshtein Edit Distance, Longest Common Sub-String Algorithm and regular expression pattern matching. Needless to say, this is an expensive process for conduct comparison of two strings let alone thousands. I have optimized this portion to the best of my ability. Now that I have the comparison method I am looking to apply it to a function where the user selects a cell containing the string they want to match and a range they want to match the string against. Unfortunately, the range is upwards of 17,000 cells. I found that stuffing the range values into an array and looping through the array was fast then looping through the range. However, if I have to populate the array using a for loop, I might as well loop through the range. The end result of the function is to return row number (Just like the Match() function but a hell of a lot more complicated). I hope this help better explain what I am trying to do.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thank you,<o></o>
<o></o>
Joseph Marro<o></o>

Okay, after already committing an Oopsie... I'm a bit reticent, but still, 'cat killin' curiousity' has me bravely running out onto the ice way too late in the season...

Am I understanding this correctly? After flopping the vals into an array, is each element to be checked for a (fuzzy) match agaiinst the one value supplied? If that is true, are you trying to get the row numbers so that you know where the matches were found?

I sooo hope I'm not just in la-la land...

Mark

 

[Joseph.Marro]

Xenou,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
That part of the array isn't an issue. The hard part is populating the row number into the array without looping. I have been working with Nories evaluate row method but I can’t seem to populate both the value and the row number into a 2D array without looping. I may end up having to use parallel arrays... I'm not too fond of that solution though.<o></o>
<o></o>
Thank you,<o></o>
<o></o>
Joseph Marro<o></o>

 

[Norie]

Joseph

You want an array with both the row number and value?

Mark

Wasn't sure who the blonde was so oops on my part too I suppose.

 

[Joseph.Marro]

Mark,

You didn't make an Oopsie, you were spot on. To answer your question, yes that is the ultimate goal.

Thank you,

Joseph marro