I hereby call upon the VBA Masters of the Universe and request a favor.

[bradenkeith]

I am in need of a simplified code that draws data to a series of cells depending on a particular cell's value. To keep things organized, I will illustrate what I need with the following example and figure out the rest on my own.

Imagine there is a line of infinite persons, each person is wearing a hat of a different color, and the purpose of this exercise is to associate the color of a persons hat with their respective place in line. It seems simple enough; however, I need all information consolidated visibly to two cells (A2:B2).

	A	B
1	Place in line	Color of Hat
2	10	Blue

<tbody>
</tbody>

A spinner will be used to change the value of cell A2 by increments of 1 (from 1 to infinity), and the code I am looking for must draw data to cell B2 upon activating said spinner. All data will be drawn from the following Table ("Table 1"):

Table 1	A	B
11	Place in Line	Color of Hat
12	1	Blue
13	2	Green
14	3	Orange
15	4	Brown
16	5	White
17	6	Brown
18	7	Brown
19	8	Blue
20	9	Green
21	10	Blue

<tbody>
</tbody>

The tricky part is that I also need to be able to change Cell B2 thereby updating Table 1 with new information. Can it be done?

 

[mikerickson]

If you put =VLOOKUP(A2, A12:B21, 2, FALSE) in B2, it will return the color as you change the entry in A2

 

[bradenkeith]

The only problem is that I could not update cell B2 manually because it would then replace the formula. right?

 

[shg]

One way:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Oops
    Application.EnableEvents = False
    
    Select Case Target.Address(False, False)
        Case "A2"
            Range("B2").Formula = "=vlookup(A2, A12:B21, 2, false)"
        Case "B2"
            Range("B12:B21").Cells(Range("A2").Value).Value = Target.Value
    End Select

Oops:
    Application.EnableEvents = True
End Sub

It's missing error checking.

 

[mikerickson]

Looking at your chart. If you manually put Blue in B2, would you want 1 or 10 to be the value returned?

 

[bradenkeith]

I would want it to update table 1. Because table 1 already has blue for position 10, no data would change.

If A2= 3, then B2 would equal "Orange." If I updated cell B2 to "Blue" at that point, I would like cell B14 to change to "Blue."

 

[mikerickson]

Line 12, Table 1 has 1 assigned to blue.

 

[mikerickson]

If you have a named range Name: Table1 RefersTo: =Sheet1!$A$12:$B$21 this might work.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    With Target
        If .Cells.Count = 1 And .Address = "$B$2" Then
            Range("Table1").Cells(Range("A2").Value, 2) = .Value
        ElseIf Not Application.Intersect(.Cells, Range("A2")) Is Nothing Then
            Range("B2").FormulaR1C1 = "=VLOOKUP(RC[-1],Table1,2,False)"
        End If
    End With
    Application.EnableEvents = True
End Sub