CODEWITHSUNDEEP
excelvba
user71812
user71812

Reputation: 457

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!

Upvotes: 1

Views: 1602

Answers (1)

SJR
SJR

Reputation: 23081

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.

Upvotes: 1

Related Questions