VBA code Help

[mtoure]

Hello All,
i have a question regarding a code - i am not sure if it is possible but i hope that there is a way around it.
I have to manipulate a register on a daily basis and i am trying to use vba codes to make the task easy. A part of this is to bring in codes from a different workbook- [ for example the Col A on the register has Company names and i need to insert Col B and enter codes associated with the Companies. ] I have the code saved into a different workbook and everytime i have to open that workbook and do a vlookup to bring in the values. Is there a way to create a code that will allow me to enter the Company codes ( lookup array ) into the code itself without having to reference to the other workbook?
Thank you

 

[offthelip]

This code will automatically put a Company Code inot column B whenever a nmae that matches the list is entered in Column A:
This code uses the worksheet change event so it must be put in the Sheet object

Private Sub Worksheet_Change(ByVal Target As Range)
colno = Target.Column
If colno = 1 Then
Namearr = Array("John", "Jane", "Harry", "Peter")
Codearr = Array("1234", "5678", "91011", "56tf5")
rowno = Target.Row


For i = 0 To UBound(Namearr)
    If Target.Value = Namearr(i) Then
    Application.EnableEvents = False
     Cells(rowno, 2) = Codearr(i)
    End If
Next i
End If
Application.EnableEvents = True


End Sub

 

[Akuini]

Another way:
Try this UDF (User Defined Functions).
Insert a module then copy paste the code below into it.
Then in cell B2 type the formula ‘=mtoure(A2)’ then copy down.

Function mtoure(c As Range) As String
Dim a, b, res
    a = Array("Company1", "Company2", "Company3") 'company name
    b = Array("123", "124", "125")                'company code
    
    res = Application.Match(c, a, False)
    If IsNumeric(res) Then mtoure = b(res - 1)
End Function

If you never use a UDF then this article would help:
https://www.engineerexcel.com/intro-to-excel-vba-user-defined-functions/

 

[mtoure]

This code will automatically put a Company Code inot column B whenever a nmae that matches the list is entered in Column A:
This code uses the worksheet change event so it must be put in the Sheet object

Private Sub Worksheet_Change(ByVal Target As Range)
colno = Target.Column
If colno = 1 Then
Namearr = Array("John", "Jane", "Harry", "Peter")
Codearr = Array("1234", "5678", "91011", "56tf5")
rowno = Target.Row


For i = 0 To UBound(Namearr)
    If Target.Value = Namearr(i) Then
    Application.EnableEvents = False
     Cells(rowno, 2) = Codearr(i)
    End If
Next i
End If
Application.EnableEvents = True


End Sub

Hello offthelip, I tried the code but it does run and gives me a popup. I am not well versed in macro and i am actually just learning. could you please provide me with more guidance?

Thank you

 

[offthelip]

What is the error you are getting? also what did you enter in the worksheet and where? and can you post the code the you are using and tell me where you have put it. did it trigger automatically when you entered something in column A?

 

[Akuini]

Hi, mtoure
Did you try my code in post #3 ?