PDA

View Full Version : Macro not working properly and very slow which matching data between two workbook



abraham30
05-30-2017, 12:28 AM
Hello Expert!

I need your help how to update the macro so that it will work faster. I have already asked my question in other forum but not getting proper resolution. Please help me

I have two sheets Master and company_list available in below path

Master=''C:\temp"
Company_list="C:\QC\Responsibility"

I want to insert one column named as "Processing site" and populate data when User_fullname column from 'Master' workbook is matching with "name" column for sheet 'user' from other workbook 'Company_list'. It is not working when user_fullname is blank.

One more requirement is same as above

I want to insert one column named as "Priority" and populate data when ENAME column from 'Master' workbook is matching with "check" column for sheet 'processing' from other workbook 'Company_list'. I used the below macro but it is not working for few rows and its very slow.

Thanks in advance




Sub test()
Dim fn As String, myName As String
fn = "'C:\QC\Responsibility\[company_list.xlsx]user'!"
With ActiveSheet.Cells(1).CurrentRegion


.Parent.AutoFilterMode = False
myName = .Columns(.Columns.Count - 2).Cells(2, 1).Address(0, 0)
.Columns("b").Insert
.Cells(1, 2).Value = "Processing site"
.Columns("b").Offset(1).Resize(.Rows.Count - 1).Formula = _
"=iferror(index(" & fn & "a:b,match(" & myName & "," & fn & "a:a,0),2),"""")"
.AutoFilter
End With
fn1 = "'C:\QC\Responsibility\[company_list.xlsx]processing'!"
With ActiveSheet.Cells(1).CurrentRegion


.Parent.AutoFilterMode = False
myName1 = .Columns(.Columns.Count - 12).Cells(2, 1).Address(0, 0)
.Columns("b").Insert
.Cells(1, 2).Value = "Priority"
.Columns("b").Offset(1).Resize(.Rows.Count - 1).Formula = _
"=iferror(index(" & fn1 & "a:b,match(" & myName1 & "," & fn1 & "a:a,0),2),"""")"
.AutoFilter
End With

End Sub