change cell reference based on vba code

I am trying to get my textboxes to change based on a dropdown list. I have:

If Me.LookUpItem.Value = Range("ao2") Then Me.Stem1 = Range("a2")
If Me.LookUpItem.Value = Range("ao3") Then Me.Stem1 = Range("a3")

End Sub

What I would like is to change where the code looks up it's value i.e. if ao7 is selected then range would become a7 etc. without having to write masses of code.

Any ideas, it's probably quite simple.

Z

Unclear exactly what you want, but...

Please Login or Register  to view this content.

Perhaps...?

That doesn't do what I want. I'm trying to get each range to change based on a combobox selection. So for example if the combobox picks a selection from range a7 my textbox1 will select b7, if my combobox selects from a100 then textbox1 will select from b100.

Does that makes sense?

Z

I can't share my workbook but my code looks like this:

If Me.LookUpItem.Value = Range("ao2") Then Me.Stem1 = Range("a2")
If Me.LookUpItem.Value = Range("ao3") Then Me.Stem1 = Range("a3")