Thread: Using cell values to address an array

I have a column in a worksheet where each value has an integer value between 1 and 6. I also have an array defined in size from 1 to 6.

I want to be able to create a new colum in the worksheet where the value for each row is one of the values held in the array. In fact it's the same element number of the array as number held in the first column.

So I want to use the first column to address the array and fill the new column. I keep getting syntax errors and could use some help.

Thanks

Show us what you mean.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

Ok, I think that's it. At least it's one of the ways I have tried, others have just been a variation of this.

I did forget to mention earlier that I'm trying to do this using a macro, as it's a small part in a big file I need to generate automatically

This is the programming forum, so VBA is assumed. I'll look at your file.

Put this formula in B2 and copy down:

=INDEX(Lengths!$C$4:$C$9, MATCH($A2, Lengths!$B$4:$B$9, 0))

Here's two other formulas:

=VLOOKUP($A2, Lengths!$B$4:$C$9, 2, 0)
=LOOKUP($A2, Lengths!$B$4:$B$9,Lengths!$C$4:$C$9)

INDEX/MATCH is one of the most robust worksheet functions available. Also, it's not volatile, so performance should be excellent.

I know this is the programming forum, but your need is clearly resolved via super-fast formula, so in this case I would use it.

=========
If there is some reason you cannot use the better option of the ready-to-go formula, then I would simply create a macro that typed in that formula for me, then removed the formulas leaving the value behind.

Code:

Option Explicit

Sub GetLengths()
Dim LR As Long
Application.ScreenUpdating = False

LR = Range("A" & Rows.Count).End(xlUp).Row

With Range("B2:B" & LR)
    .FormulaR1C1 = "=INDEX(Lengths!R4C3:R9C3, MATCH(RC1, Lengths!R4C2:R9C2, 0))"
    .Value = .Value
End With

Application.ScreenUpdating = True
End Sub

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

Using your array would require looping, evaluating every row one by one to see what the value in A is and matching that to the value in your array. Looping is not the way to solve this efficiently, so the answer to your question:

"....instead of the array being filled with cell values it was defined in VBA...how would you solve it then?"

I would write the values into a table on the worksheet temporarily and use the instantaneously finished formula approach already shown, but adjusting the formula to use the range of cells I'd written in.

Inherently, worksheet functions are simply faster, as you would expect them to be. I wouldn't loop through 100s, 1000s of rows of data doing one-at-a-time comparisons when it can be done all at once this way.