Excel VBA

[iretex]

Please I need help with my code,

I wanted to lookup a reference from a range and if found write a value in the last empty cell on the row reference of the Matching value in my data range.

Option ExplicitDim d As Variant, SN As Integer


Sub Button1_Click()


Dim a As Variant, b As Variant, c As Variant


Dim looksn As Variant, FoundCell As Range


Dim Ans As Integer, NR As Long




Dim wkb1    As Workbook, SN As Range, wsMaster As Worksheet


 
Set wkb1 = ActiveWorkbook




'wkb1.Sheets("Sheet1").Range(sn) = looksn




'Sheets("Sheet1").Range("j8").Value = looksn


    On Error GoTo MyErrorHandler:


  'result = [VLOOKUP(Input!A2, Data!A1:X200, 5, FALSE)]
 
    a = Application.WorksheetFunction.VLookup(Sheets("sheet1").Range("I5"), Sheets("Sheet2").Range("Db"), 4, 0)
    b = Application.WorksheetFunction.VLookup(Sheets("sheet1").Range("I5"), Sheets("Sheet2").Range("Db"), 6, 0)
    c = Application.WorksheetFunction.VLookup(Sheets("sheet1").Range("I5"), Sheets("Sheet2").Range("Db"), 2, 0)
    d = Sheets("sheet1").Range("I5")
      
MyErrorHandler:
    
    'If IsError(a) Then
     If Err.Number = 1004 Then
     MsgBox "Shipment Cannot go, return to the Warehouse for proper documentation", vbCritical: Exit Sub
    End If
    
    'Display the matching values
          
    Sheet1.Range("J8").Value = a
    Sheet1.Range("J10").Value = b
    Sheet1.Range("J12").Value = c
    Sheet1.Range("J14").Value = d
    
    Ans = MsgBox("Are the details correct? Will you like to go ahead and checkout?", vbYesNoCancel)
    
    Select Case Ans
    
          Case vbYes
  '       ...[code if Ans is Yes]...
  Set wsMaster = ThisWorkbook.Sheets("Sheet2")
  
  NR = wsMaster.Range("A" & wsMaster.Rows.Count).End(xlUp).Row + 1
  
  'Call execute
  
  'Cells(FoundCell.Row, NR).Value = "checkout"
  
    'Call execute
    
          Case vbNo
  '       ...[code if Ans is No]...
    Exit Sub
        
        Case vbCancel
        
    Exit Sub
    
    
      End Select
    
    
    
    
    'WorksheetFunction.VLookup(Me.txtUsername.Value, Range("UserLogin"), 4, 0)
    
End Sub




Sub execute()


        Dim wb As Workbook
        Dim ws As Worksheet
        Dim FoundCell As Range
        Set wb = ActiveWorkbook
        Set ws = ActiveSheet


            'Const WHAT_TO_FIND As String = "Bingo"
            Sheets("Sheet1").Range("I5") = SN


            'Set FoundCell = ws.Range("A:A").Find(What:=WHAT_TO_FIND)
            Set FoundCell = Sheets("Sheet2").Range("Db").Find(What:=SN)
            If Not FoundCell Is Nothing Then
                MsgBox (SN & " found in row: " & FoundCell.Row)
            Else
                MsgBox (SN & " not found")
            End If
End Sub