May 27 2021 06:30 AM
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 numbers belonging to that product are the same like its buying price and selling price. So when I enter the same product today I want the program to fill automatically the buying price and selling price. I don't want to copy paste the cells all day. Can someone help me please?
May 27 2021 06:47 AM
Which column contains the product names?
Which column contains the buying price?
Which column contains the selling price?
May 27 2021 07:09 AM
I tagged them in black in the first bar @Hans Vogelaar
May 27 2021 07:12 AM
May 27 2021 07:32 AM
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 Sub
Switch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open the workbook.
May 27 2021 07:37 AM