Clash Royale CLAN TAG

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

enter image description hereI Have two Excel Sheets ("REC") & ("Training register"), "Training 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 "Training 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

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


1 Answer
1

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

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!

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.