VBA code Help

mtoure

New Member
Joined
Jul 26, 2019
Messages
3
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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,656
Office Version
  1. 2010
Platform
  1. Windows
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
Code:
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
 
Last edited:

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,936
Office Version
  1. 365
Platform
  1. Windows
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.


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

New Member
Joined
Jul 26, 2019
Messages
3
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
Code:
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

Well-known Member
Joined
Dec 23, 2017
Messages
1,656
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,936
Office Version
  1. 365
Platform
  1. Windows
Hi, mtoure
Did you try my code in post #3 ?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,736
Messages
5,597,815
Members
414,178
Latest member
Octavian Manoli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top