Forum Discussion

gamzeoz93's avatar
gamzeoz93
Copper Contributor
May 27, 2021

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 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? 

 

 

excel
Formulas and Functions

5 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 27, 2021

    gamzeoz93 

    Which column contains the product names?

    Which column contains the buying price?

    Which column contains the selling price?

    • gamzeoz93's avatar
      gamzeoz93
      Copper Contributor
      May 27, 2021
      I sent you a message because I can't load a picture here.
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        May 27, 2021

        gamzeoz93 

        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.

         

Resources