Autofilter and return cell values using VBA

  With wTarg
    'assume the header is row 1
    Set rRng = .Rows(1)
    'try finding the matching number first
    If Not FindInRange(rRng, sNum, rFind) Then GoTo QuickExit
    lNum = rFind.Column
    If Not FindInRange(rRng, OP, rFind) Then GoTo QuickExit
    lOP = rFind.Column
    If Not FindInRange(rRng, DP, rFind) Then GoTo QuickExit
    lDP = rFind.Column
    If Not FindInRange(rRng, DType, rFind) Then GoTo QuickExit
    lDType = rFind.Column
    If Not FindInRange(rRng, Exp, rFind) Then GoTo QuickExit
    lExp = rFind.Column
    If Not FindInRange(rRng, EDate, rFind) Then GoTo QuickExit
    lEDate = rFind.Column
    'transit time may or may not exist
    If Not FindInRange(rRng, TTime, rFind) Then lTTime = 0
  End With

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sPEG As String
    sPEG = [PEG]
    If Not Intersect(Target, [NUM]) Is Nothing Then
        If Target <> vbNullString Then
            If CInt(Left(Target, 1)) <> Application.VLookup(sPEG, Range("j2:k20"), 2, 0) Then
                MsgBox "Number invalid for entered PEG", 16
                [NUM].ClearContents
                Application.Goto [NUM]
                Exit Sub
            End If
        End If
    End If
End Sub

Option Explicit

Sub Add()
    Dim sOP As String, sDP As String, sDType As String
    Dim lNum As Long, dPrice As Double, sCurr As String, lTrans As Long, dtExp As Date, dtEff As Date
    Dim x, y, i As Long, ii As Long, iii As Long, iv As Long, v As Long, vi As Long, vii As Long, viii As Long
 
    sPEG = [PEG]: lNum = [NUM]
    With Sheets(sPEG).Cells(1).CurrentRegion
    
    ' Load all sheet data into array x and headers into array y
       x = .Value: y = .Rows(1)
       
    ' Determine the correct columns that require data
       ii = Application.Match("OP", y, 0): iii = Application.Match("DP", y, 0)
       iv = Application.Match("d_type", y, 0): v = Application.Match(lNum, y, 0)
       vi = Application.Match("expiry", y, 0): vii = Application.Match("effective_date", y, 0)
       
    ' Loop though array x checking for matching "OP", "DP" & "d_type"
       For i = 2 To UBound(x, 1)
            If x(i, ii) = [OP] And x(i, iii) = [DP] And x(i, iv) = [DType] Then 'Full Match found
                
                ' Load data into the array
                x(i, v) = [Price]: x(i, v + 1) = [Currency]: x(i, vi) = [ExpDate]: x(i, vii) = [EffDate]
                
                ' Check if there is a "transit time" column, if there is then add time to array
                If Not IsError(Application.Match("transit_time", y, 0)) Then
                    ix = Application.Match("transit_time", y, 0)
                    x(i, ix) = [TransTime]
                End If
                
           End If
       Next
       
       ' Write the new values in array x back to the worksheet (updating it)
       .Value = x
    End With
    
    MsgBox " Price has been Updated... !!! "
 
End Sub