Possibly a reverse of VLookup???

[bradenkeith]

Hey guys,

I need some assistance. I'm trying to formulate a string of code for a reverse of VLookup. I am probably way off, but let me try to explain what it is that I'm trying to accomplish:

Let's say Cell A1 is someones place in line, and Cell B1 needs to be entered with the persons corresponding hat color. Let's say the third person in line has a Blue hat. I would use a scroll wheel to change cell A1 to 3, and update Cell B1 with "Blue."


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD]Blue[/TD]
[/TR]
</tbody>[/TABLE]

At this point, I would click an "Update" button and a macro would run. The information I entered into cell B1 would be updated into the table I have set up on Spreadsheet3.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Place in Line[/TD]
[TD]Hat Color[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Then, I could change Cell A1 to 5 and update Cell B1 to Green, because that is that persons hat color. It would update in the table on Spreadsheet3 and so on..:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Place in Line[/TD]
[TD]Hat Color[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Green[/TD]
[/TR]
</tbody>[/TABLE]


This is the non-functioning code i have come up with:

Sub ReverseVLookup()
    i = Sheets("Sheet1").Range("A1").Value
    Sheets("Sheet3").Range(i, 2).Value = Sheets("Sheet1").Range("B1").Value
End Sub

Please help. Thanks.

 

[RoryA]

You'd need to use:

Sheets("Sheet3").Cells(i, 2).Value

assuming you don't have a header row in your table and the table starts in row 1. If you do have a header row, you'd need to add 1 to i. Equally if the table doesn't start in row 1 you'd need to adjust for that as well.

 

[bradenkeith]

Yup, I've done it. I'm so exhausted that I have become brain-dead. I'm going to sleep now. Thank you for your help. I don't know what I was thinking. And here the whole time I was typing, I didn't even realize it. Too much code... Good night.