Solved: Excel macro question: matching text to numeral value & inserting the numerals + more!

[Rachel_C]

Hi,

First of all, I love this site & thanks to all the creators behind this, and all the helpful & very knowledgeable posters!

I better start by saying I am a big big novice to excel macros and VBA...!
Also, my query may have been answered somewhere along the line, but I have had a fair bit of a look ... maybe it's also a case of not really having a totally good grasp on what I am trying to do...! but any help would be greatly appreciated...! (including 'ahhh this thread answers your query! )

Ok, I have attached a dummy file, and I'll also explain what I am trying to do below...

I want to be able to enter text in a cell ('value') and have it match a two digit numeral code ('code') on a seperate sheet, within the same workbook and insert that two digit code into the previous column. There would be 20 codes, possibly more over time ...

I don't really know if that is at all possible, but it would be bloody handy if it is...!

Pls feel free to ask for further clarification on what it is I am actually trying to do...!

Cheers,

Rachel

[mdmackillop]

Hi Rachel,
Welcome to VBAX and thanks for the comments.
Here's the code for a VBA solution, you can see its application in the attached file.
[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Target.Column = 3 Then
On Error Resume Next
Target.Offset(, -1) = Sheets("Code Key").Columns(2).Find(What:=Target, _
MatchCase:=False).Offset(, -1)
End If
If Err > 0 Then MsgBox ("Code not found")
Application.EnableEvents = True
End Sub

[/vba]

A simpler way to do this though would be using VLOOKUP as shown in B2:B5, if you add the code to the right of the Key (could be hidden)
=VLOOKUP(C2,'Code key'!B:C,2,FALSE)

Regards
MD

[Rachel_C]

Hi mdmackillop!

Wow, thank you so much for this --- I chose the VBA solution, this is perfect, I was able to configure it and have now got it doing exactly what I need!

I am very very grateful!

Cheers,

Rachel

Edit: Chrs for that geekgirlau - all done, and thanks for the heads up!

[geekgirlau]

Glad to have you on board, Rachel.

Don't forget to mark your thread as "Solved", using "Thread Tools" at the top of the screen.

[mdmackillop]

Glad to help Rachel.
BTW, As you say you're new to macros, please note that only Event and associated code goes into the Worksheet module (as in this example). Your macros should normally be placed in a Standard module
Regards
MD