Another approach using dictionary [code] Private Sub UserForm_Initialize() Dim arr As Variant arr = GetDistinct(Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)) ListBox1.List =...
Thanks a lot PNRao for this great solution Another approach using collection [code] Private Sub UserForm_Initialize() Dim ws As Worksheet...
Thanks vaali... this is the data sheet1 data EID NAME PAY PayDay 14123 Joe1 $1,234.00 29-Jun-17 14124 Joe2...
HI, Here is the required macro to search a field and if value found copy the whole row into another...
I found a solution like this :Records in between two specific dates easily can be filtered using drop-down lists ....
I found a solution as follows: Watch the video to see how it's done and download example file : https://youtu.be/SZduAoOtiS0
I have sorted the above problem. Code is given below... [code] Sub seperate() Call CleanSheets MsgBox "1 of 10 Cleaning...
I have two columns in a worksheet one with “EmpID”, and the other “Tax Paid”. I get reports every fortnight. Some EmpID gets added and some are dropped. I have worksheet “PreviousFN”
I want to copy “Tax Paid” for the common “EmpID” from the “PreviousFN” to “CurrentFN”.
How do I do it, please?
Hi, This Macro assumes you have EmpID, Tax Paid in Column A and B. And Data starts from the 2nd Row:
'LastRow in PreviousFN
lastRowPrev = Sheets("PreviousFN").Cells(Sheets("PreviousFN").Rows.Count, "A").End(xlUp).Row
'LastRow in CurrentFN
lastRowCur = Sheets("CurrentFN").Cells(Sheets("CurrentFN").Rows.Count, "A").End(xlUp).Row
'Now we will loop through each row in PreviousFN and serach for that EmpId in CurrentFN, and update the record if found.Other wise add at the last row
For iCntrPrev = 2 To lastRowPrev
blnExitst = 0
For iCntrCur = 2 To lastRowCur
'Check if EMPIds are macthing
If Sheets("CurrentFN").Cells(iCntrCur, 1) = Sheets("PreviousFN").Cells(iCntrPrev, 1) Then
'if matches then get the value from 2nd ccolumn of PreviousFN and place it at 2nd ccolumn of CurrentFN
Sheets("CurrentFN").Cells(iCntrCur, 2) = Sheets("PreviousFN").Cells(iCntrPrev, 2)
blnExitst = 1
'if no match found then add new record
If blnExitst = 0 Then
lastRowCur = lastRowCur + 1
Sheets("CurrentFN").Cells(lastRowCur, 1) = Sheets("PreviousFN").Cells(iCntrPrev, 1)
Sheets("CurrentFN").Cells(lastRowCur, 2) = Sheets("PreviousFN").Cells(iCntrPrev, 2)
Thanks a lot …fantastic!
Now I have added two more worksheets called “Omitted” and “Changed”.
How do I change the above code? So that, if EMPIds are macthing copy them “Changed”. And if EMPIds are NOT macthing copy them to “Omitted” please?
EMPIds are macthing
EMPIds are NOT macthing
Please share using the share button above.
If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.
Found the answer useful and wants to credit the user, then vote the answer (vote up).