Excel VBA - If cell matches a certain value in a column, insert data in an adjacent column

So I have two forms in Sheet EmployeeForm (EmployeeForm1 & EmployeeForm2) and an Excel Table TableEmployee in Sheet EmployeeData that looks like this: enter image description here

The data in the table comes from the submission from these two forms, but so far I've only succeeded in inputting the first half of the table.

The data in Employee Form 2 is submitted only and only after the Employee Form 1 is submitted (can be days, even weeks later).

Now what I want to achieve is to have a working VBA code that can match the Employee ID in cell D13 with Employee ID in column H correctly, and record the data in D14:D17 to its proper place.

So in this example above, since the Employee ID is 145, once I click the submit button in Form 2, the data in D14:D17 should be stored in L7:O7.

This is my code so far:

Sub Submit_Form1()

   Dim LastRow As Long, ws As Worksheet

   Set ws = Worksheets("EmployeeData")

   LastRow = ws.Range("H" & Rows.Count).End(xlUp).Row + 1

   ws.Range("H" & LastRow).Value = Worksheets("EmployeeForm").Range("D5").Value   'Employee ID
   ws.Range("I" & LastRow).Value = Worksheets("EmployeeForm").Range("D6").Value   'Employee Name
   ws.Range("J" & LastRow).Value = Worksheets("EmployeeForm").Range("D7").Value  'Place of Birth
   ws.Range("K" & LastRow).Value = Worksheets("EmployeeForm").Range("D8").Value  'Working Experience

 End Sub

And for Form 2

Sub Submit_Form2()

    Dim LastRow As Long, ws As Worksheet
    Dim H As String

   Set ws = Worksheets("EmployeeData")
    employeeid = Sheets("EmployeeForm").Range("D13").Value

    If Cells(H) = employeeid Then

     ws.Range("L" & LastRow).Value = Worksheets("EmployeeForm").Range("D14").Value   'Education
     ws.Range("M" & LastRow).Value = Worksheets("EmployeeForm").Range("D15").Value   'Last Company
    ws.Range("N" & LastRow).Value = Worksheets("EmployeeForm").Range("D16").Value  'Join Date
    ws.Range("O" & LastRow).Value = Worksheets("EmployeeForm").Range("D17").Value  'Position

 End Sub

Of course, the second macro doesnt work, but can anybody please enlighten me as how to do this the right way? Thanks a lot!

1 answer

  • answered 2018-10-09 16:31 SJR

    Can you try this?

    Sub Submit_Form2()
    Dim ws As Worksheet, v As Variant
    Set ws = Worksheets("EmployeeData")
    employeeid = Sheets("EmployeeForm").Range("D13").Value
    v = Application.Match(employeeid, ws.Range("H:H"), 0)
    If IsNumeric(v) Then
        ws.Range("L" & v).Value = Worksheets("EmployeeForm").Range("D14").Value   'Education
        ws.Range("M" & v).Value = Worksheets("EmployeeForm").Range("D15").Value   'Last Company
        ws.Range("N" & v).Value = Worksheets("EmployeeForm").Range("D16").Value  'Join Date
        ws.Range("O" & v).Value = Worksheets("EmployeeForm").Range("D17").Value  'Position
    End If
    End Sub

    The problem with your code was

    If Cells(H) = employeeid Then

    which is not valid syntax. Cells needs a row and column reference such cells(1,1) or cells (1,"A"). Not to mention that H wasn't defined.