how to use worksheet event instead of formulas


please see attached workbook.

currently, i have built this template and made it automatic with formula for example if you select any name in C2 all the other cells will be filled automatically.

i am looking for a VBA based solution where, the same operation is done without formulas in those cells.  and they are done with worksheet change event.  for example if i type any name or i select any name in the C2 then all of those cells populate as values and then if no match found then it should be left blank.

thanks for your usual help.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
Why do you need to use a VBA option?

VBA will have some advantages but not sure from your description above what you would gain.

Rob H
FloraAuthor Commented:
thanks Rob,

but i need VBA solution. that is what i need.
Martin LissOlder than dirtCommented:
I assume what you are doing now is copying a Product Name from the List sheet and pasting it into C2 on the Main sheet. If that is what you are doing your formulas are incorrect because you have multiple entries with the same Product Name but the data that get put on the Main sheet is always from the last of the duplicates. For example if you copy/pasted any one of the "Alice Mutton" values from List to Main, you always get "Conrad" as the sir name.

Please let me know if that's not what you do.
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Since you have already working formulas, you may place those formulas through VBA.

Place the following code into Main Sheet Module and see if this is what you were trying to achieve.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "C2" Then
   Range("C4").Formula = "=IFERROR(LOOKUP(2,1/($C$2=Table1[Product Name]),Table1[Birthdate16]),"""")"
   Range("C4").Value = Range("C4").Value
   Range("C4").NumberFormat = "mm/dd/yyyy"
   Range("E2").Formula = "=IFERROR(LOOKUP(2,1/($C$2=Table1[Product Name]),Table1[Gender17]),"""")"
   Range("E2").Value = Range("E2").Value
   Range("E4").Formula = "=IFERROR(LOOKUP(2,1/($C$2=Table1[Product Name]),Table1[Cust Surname15]),"""")"
   Range("E4").Value = Range("E4").Value
   Range("C8").Formula = "=IFERROR(LOOKUP(2,1/($C$2=Table1[Product Name]),Table1[SP Name]),"""")"
   Range("C8").Value = Range("C8").Value
   Range("E8").Formula = "=IFERROR(LOOKUP(2,1/($C$2=Table1[Product Name]),Table1[Cust Address]),"""")"
   Range("E8").Value = Range("E8").Value
   Range("E10").Formula = "=IFERROR(IF(LOOKUP(2,1/($C$2=Table1[Product Name]),Table1[Ph. Number18])="""",""Enter Birthday Here"",LOOKUP(2,1/($C$2=Table1[Product Name]),Table1[Ph. Number18])),"""")"
   Range("E10").Value = Range("E10").Value
   Range("H4").Formula = "=IFERROR(LOOKUP(2,1/($C$2=Table1[Product Name]),Table1[Cust Address17]),"""")"
   Range("H4").Value = Range("H4").Value
   Range("H8").Formula = "=IFERROR(LOOKUP(2,1/($C$2=Table1[Product Name]),Table1[Quantity11]),"""")"
   Range("H8").Value = Range("H8").Value
End If
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FloraAuthor Commented:
Thanks Sktneer

it worked.

Matrin and Rob thanks.

yes, i had put just dummy data there, i understand it was duplicate. sorry for the causing confusion. Sktneer solution worked for me for now.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Flora! Glad I could help.
Rob HensonFinance AnalystCommented:
Once you have the formulas in place, protect the sheet to ensure it doesn't get changed.

Then there is no need for VBA to change them.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.