Solved: Excel - Variable Lookup

[simonc]

I am an experienced Excel user but a novice at VBA programming. I am trying to achieve the following via a macro and need some help!

I want to take the value in the active cell (text) and use it to lookup a value in a range on another worksheet in the workbook corresponding to the text in the active cell . The value looked up in the lookup table is a range name that I want to make the active cell.

For example:

Text in the active cell = WD:-1

Lookup Range:
A1 A2
WD:-1 CM1
WD:-2 CM2
WD:-3 CM3
etc.

CM1 is a range name that I would like to make the active cell. I have tried several approaches to this but failed, mainly due to my lack of VBA knowledge (learning all the time!). I would be most appreciative if anyone can put me on the right track.

[Bob Phillips]

The code would look like

[vba]

Sub FindIt()
Dim cell As Range

Set cell = Columns(1).Find(ActiveCell.Value)
If Not cell Is Nothing Then

Application.Goto Range(cell.Offset(0, 1).Value)
End If
End Sub
[/vba]

but you can't use CM1, CM2 etc. as range names as these are cell references.

[georgiboy]

I may have missunderstood but heres what i came up with. It changes the activecell name to the found cell value. XLD is correct you would not be able to rename a cell to an existing cell range "like trying to rename A2 to A1".

[VBA]Sub RngNameCh()
Dim rCell As Range, MyRange As Range

Set MyRange = Sheets("Sheet2").Range("A1:A" & Range("A65536").End(xlUp).Row)

For Each rCell In MyRange.Cells

If rCell.Value = ActiveCell.Value Then
ActiveWorkbook.Names.Add Name:=rCell.Value, RefersTo:=ActiveCell
End If

Next rCell

End Sub[/VBA]

[simonc]

Many thanks to you both for your help, it is much appreciated. The spreadsheet is working exactly as I wanted it to, many thanks.