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).

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Place in line[/TD]
[TD]Color of Hat[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10[/TD]
[TD]Blue[/TD]
[/TR]
</tbody>[/TABLE]

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="width: 500"]
<tbody>[TR]
[TD]Table 1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Place in Line[/TD]
[TD]Color of Hat[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]1[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]2[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]3[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]4[/TD]
[TD]Brown[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]5[/TD]
[TD]White[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]6[/TD]
[TD]Brown[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]7[/TD]
[TD]Brown[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]8[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]9[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]10[/TD]
[TD]Blue[/TD]
[/TR]
</tbody>[/TABLE]


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