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

	A	B
1	3	Blue

<tbody>
</tbody>

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.

Place in Line	Hat Color
1
2
3	Blue
4
5

<tbody>
</tbody>

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

Place in Line	Hat Color
1
2
3	Blue
4
5	Green

<tbody>
</tbody>

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.