Macro to store words into an array [SOLVED]

[swetha690]

Hi all,


I have an excel file where I have words in different columns like the sample below in over 600 rows.

'

Entry1 Entry2 Entry3

Entry4 Entry5 Entry6

Entry7 Entry8 Entry9


I would like to store the words in each row into an array. For Eg: Entry1, Entry2 and Entry3 has to stored in Array1, Entry4, Entry 5 and Entry 6 has to be stored in Array2 and so on. In essence, each row must have its own array. So far I have been able to write a macro that stores the words in the first row into an array. However, when I try to extend the code to other rows, I am not quite sure as to how to create 600 different arrays for each row. Is there a way to do this by writing a macro. I would appreciate any help in this regard. Thanks in advance.


I hope I have followed the rules in posting in the forum. If there are any errors please let me know. I will correct it from the next time.


Thank You,

Swetha

 

[Colin Legg]

Hi, I guess the question on my mind is why do you need to to put each row in its own array (or an array of arrays)? It would seem much more logical to use a 2 dimensional array of 3 columns and 600 rows. If you explain a bit more about where you need to go with this, we might be able to suggest a good solution?

 

[swetha690]

First of all Thank You for your reply Colin.


To explain further to give you a better idea, I would like to use these arrays for the purpose of data validation. I am in the process of collecting data from users. I have a list of the 600 possible entries. I was thinking of maybe creating a user form for getting data from the users and the validating the entry against my list of allowed entries. If there is no match found, I would like to come up with a list of possible entries based on the value that has been entered by the user. I was thinking of using the fuzzy match for this.

The words in different columns when combined forms a valid entry.

For Eg: Entry1 Entry2 Entry3 is a valid entry.

What I have already done is that I have separated each word from the entry and put them in different coulmns.

I wrote a macro using the split function for this purpose.

Now, I would like to identify each row with an array of the words contained in it, so that I can compare each array with the value entered to calculate the % match.

I hope this elaborates my situation. If the problem is still unclear, please do let me know.


Thanks,

Swetha

 

[SirJB7]

Hi, swetha690!

Consider uploading a sample file (including manual examples of desired output if applicable), or at least our macro code. It'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.

Regards!

 

[swetha690]

Here's a sample file with just 20 entries. I have written a macro that splits each string and puts each word into a cell.


http://www.2shared.com/file/8Ehde_EW/Sample.html

Column B contains the list of permissible entries


What I would want to do now is to write a macro to store values in Cells C3:F3 into Array1, C4:F4 into Array2 and so on.


After that I was thinking of comparing user's input with each of these arrays and calculating a % match. I have not actually tried this part practically yet. But, this is the idea that I have. Please let me know if the problem is clear.

 

[swetha690]

Hello all,


I went ahead and wrote a macro to create a multidimensional array for the values. However, I have run into a new problem. When I try to access any value in the multidimensional array, the MsgBox does not display the value.

The sample file can be downloaded at

http://www.2shared.com/file/CPzFo-1I/Sample.html

The code for creating the multidimensional array is in module 2.

It would be great if someone can help me out on this.


Thanks,

Swetha

 

[NARAYANK991]

Hi Swetha ,


Try this :

[pre]

Private Sub InstArray()
Dim myArr(20, 4) As String
Dim row, col, lastrow As Long
Dim i, j As Integer

row = 1: col = 3
lastrow = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).row
Cells(row, col).Select

For i = 0 To 19
For j = 0 To 4
myArr(i, j) = ActiveCell.Offset(i, j).Value

Next j
Next i

For i = 0 To 19
MsgBox myArr(i, 0)
Next
End Sub

[/pre]
The two outer loops were the problem.


Narayan

 

[swetha690]

Hi NARAYANK991,


Thank you for your input. The code is perfect now.


Swetha

 

[shrivallabha]

You can directly load up an array but then it will be 1 based array.

[pre]

Private Sub InstArray()
Dim v1BasedArray As Variant

'If you assign range value directly to variant then it '
'becomes one based array
v1BasedArray = Range("C1:F20").Value
For i = LBound(v1BasedArray) To UBound(v1BasedArray)
MsgBox i & " : " & v1BasedArray(i, 1)
Next i

End Sub

[/pre]