VBA code required to replace the Index, Match and array formulas.

[Anbuselvam]

Hi

https://www.dropbox.com/s/yj6n2yn6cq3n5rf/Index Match_Batch Card.xlsx?dl=0

In the above link file, Sheet Batch Card A8 to F19 cells are shown results depends on D5 and F5 dropdown selection.

A8 to F19 results getting from Raw details stored on Sheet Formulation.

To get above results I have used index match and array formulas too.

Please provide the VBA code to eliminate all the formulas.

Sincerely Yours
Anbuselvam K

 

[DanteAmor]

Line 3, If Target.Count > 3 Then Exit Sub (What is count 3 mean by)

Line 10, K = 7 (What is 7 mean by)

Line 15, celda = b.Address (What is Address)

Line 18 to 26 (Totally I did not Understand what it does)

>3, It is a control point, if you modify a cell C4 or F4, the macro is executed automatically, but if you modify more than 3 cells (> 3), it means that maybe you are inserting a row or a column, or you are copying a quantity of cells, in these cases the macro does not execute.

7, It is the initial destination line, that is, where you will start to put results

celda, "celda" is a variable, in "celda" the address of the first found data is stored, "celda" is inside a cycle, the cycle is going to end when it returns to the address of the first found data.



All the code is to put the data in the summary sheet, but the important thing is the references:

    Cells(k, "A").Value = h.Cells(1, j).Value
    Cells(k, "B").Value = h.Cells(3, j).Value
    Cells(k, "E").Value = h.Cells(b.Row, j).Value

in column "A" puts the value of row 1
etc.

 

[Anbuselvam]

Thanks a lot, for all your support Mr Dante Amor

I will rewrite the code for my other requirements and ask you the doubts when I required.

 

[DanteAmor]

Of course, you can do as many times as you want.

 

[Anbuselvam]

In the below code which line is assigning to take data from Column C to BB according to the Column A and B Data?

If you change the structure of the sheets, you must change the code.

The next time you try to make the change in the code, just check the logic of the lines. If you have doubts I will also help you.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C4:F4")) Is Nothing Then
        If Target.Count > 3 Then Exit Sub
        Range("A7:E16").Value = ""
        pName = Range("C4").Value
        nOrig = Range("E4").Value
        If pName = "" Or nOrig = "" Then Exit Sub
        '
        existe = False
        k = 7
        Set h = Sheets("Formulation")
        Set r = h.Columns("A")
        Set b = r.Find(pName, LookAt:=xlWhole, LookIn:=xlValues)
        If Not b Is Nothing Then
            celda = b.Address
            Do
                'detalle
                If LCase(h.Cells(b.Row, "B").Value) = LCase(nOrig) Then
                    existe = True
                    uc = h.Cells(1, Columns.Count).End(xlToLeft).Column
                    For j = 3 To uc
                        If h.Cells(b.Row, j).Value <> "" Then
                            Cells(k, "A").Value = h.Cells(1, j).Value
                            Cells(k, "B").Value = h.Cells(3, j).Value
                            Cells(k, "E").Value = h.Cells(b.Row, j).Value
                            k = k + 1
                        End If
                    Next
                    Exit Do
                End If
                Set b = r.FindNext(b)
            Loop While Not b Is Nothing And b.Address <> celda
        Else
            MsgBox "Prodcut not exists"
        End If
        If existe = False Then
            MsgBox "Relation Prodcut - Origin not exists"
        End If
    End If
End Sub

https://www.dropbox.com/s/3qibal7668ualcz/Batch card New dam2.xlsm?dl=0

 

[DanteAmor]

In the below code which line is assigning to take data from Column C to BB according to the Column A and B Data?

Here begins a cycle from column 3 (column C), to the last column with data

For j = 3 To uc

 

[Anbuselvam]

Thanks for your support.

Here begins a cycle from column 3 (column C), to the last column with data

For j = 3 To uc