Q:

VBA: filter data based on cell value and fill in to another sheet

enter image description hereI Have two Excel Sheets ("Record") & ("Register"),  " Register" is the database. from this database I need to create Records of each employees based on their employee ID (cell value). i am searching for a  VBA code that should give me  the training Record a each employee once i have enter their ID in the cell and click "a command button". Attached the Excel screen short for reference.
Steps 1: Enter Employee ID in the "Record" sheet
Step 2: Click command button "Filter" in the Record sheet
Step 3: VBA code to run and filter data from "Register" and  fill "Record". 
IF i Type another Employee ID in the sheet "Record" , it should ClearContents of the previous query. and produce the data.
Please help me, i am not good in VBA .attached the Excel screen short for reference ( UPDATE on 29/07/2018-Question Solved : sharing the code below; thank you Mr.ComradeMicha for your valuable feedback)
Sub Button2_Click()

'Declare the variables
Dim RegisterSh As Worksheet
Dim RecordSh As Worksheet
Dim EmployeeRange As Range
Dim rCell As Range
Dim i As Long

'Set the variables
Set RegisterSh = ThisWorkbook.Sheets("Register")
Set RecordSh = ThisWorkbook.Sheets("Record")

'Clear old data Record Sheet
RecordSh.Range("A8:F107").ClearContents

Set EmployeeRange = RegisterSh.Range(RegisterSh.Cells(6, 4), RegisterSh.Cells(Rows.Count, 6).End(xlUp))
'I went from the cell row6/column4 (or D6) and go down until the last non empty cell

i = 7

For Each rCell In EmployeeRange 'loop through each cell in the range

If rCell = RecordSh.Cells(4, 2) Then 'check if the cell is equal to "Record"

i = i + 1                                'Row number (+1 everytime I found another "Record")
RecordSh.Cells(i, 1) = i - 7               'S No.
RecordSh.Cells(i, 2) = rCell.Offset(0, 2)  'Training name
RecordSh.Cells(i, 3) = rCell.Offset(0, -2) 'End date
RecordSh.Cells(i, 4) = rCell.Offset(0, 6) 'Validity
RecordSh.Cells(i, 5) = rCell.Offset(0, 7) 'Remarks
RecordSh.Cells(i, 6) = rCell.Offset(0, 5) 'Minimal requirement

End If

Next rCell

End Sub

A:

Your code is missing a few essential parts you may want to look into:

It seems to require the user to select a specific row before the macro is started, even though there is a command button to trigger the macro. If the layout stays the same, just use constants to store the row where certain input or lookup fields are located.
ra is used both on the input form and on the lookup sheet. That's asking for trouble... Again, use constants or at least "StartingRow=3" or something similar.
You are correcting your employee number to a format that doesn't fit the data provided in the screenshot. Hopefully just a dummy data issue, but in case you are wondering why you don't find anything ;)
You are typecasting all fields individually. If your layout is always the same, it's much easier to just use the "variant" type for all cell values and make sure you already formatted all columns correctly.
ru is never initialized? It's supposed to be "the next row", why not simply use "ra+1" then instead of ru? Also, TRNRow and RTRNRow are never initialized either.
When you "search" your records, you actually only copy the same row into your results, then "copy next row until employee number is wrong". So this only works for exactly one employee, and even then you only catch the first few trainings. Use the Find function on the employee number cell in the records sheet to find the next row with that id, then copy the row's contents and find next.

I think if you get yourself aquainted with the Find function, you will easily finish this macro on your own. Here's a good guide: https://excelmacromastery.com/excel-vba-find
Good luck!

