Forum Discussion
gamzeoz93
May 27, 2021Copper Contributor
URGENT! Need a quick way to auto-fill some cells
When I enter the same product name, I need it to auto-fill some cells belonging to that product. For example yesterday I entered a product "Kero Domates Fidesi" as you can see in yellow. The number...
HansVogelaar
May 27, 2021MVP
Which column contains the product names?
Which column contains the buying price?
Which column contains the selling price?
gamzeoz93
May 27, 2021Copper Contributor
I sent you a message because I can't load a picture here.
- HansVogelaarMay 27, 2021MVP
You haven't specified which columns (B, C, ...), so I'll assume that the product price is in column B.
Right-click the sheet tab.
Select 'view Code' from the context menu.
Copy the following code into the worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range) Const ProductCol = "B" Dim cel As Range Dim cel2 As Range If Not Intersect(Columns(ProductCol).EntireColumn, Target) Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False For Each cel In Intersect(Columns(ProductCol).EntireColumn, Target) If cel.Value <> "" Then Set cel2 = Columns(ProductCol).Find(What:=cel.Value, After:=cel, LookAt:=xlWhole) If cel2.Address <> cel.Address Then cel.Offset(0, 4).Value = cel2.Offset(0, 4).Value cel.Offset(0, 5).Value = cel2.Offset(0, 5).Value End If End If Next cel Application.EnableEvents = True Application.ScreenUpdating = True End If End SubSwitch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open the workbook.
- gamzeoz93May 27, 2021Copper ContributorThank you very much I will try