'用字典法返回對應的數據速度較快,但sheet1工作表的F列數值不能有重復
Dim?Sht1Row,?Sht2Row?As?Long
Dim?Rng1,?Rng2?As?Range
Dim?Dic
Sht1Row?=?Sheet1.Range("F65536").End(xlUp).Row?'獲得sheet1工作表F列的最大行號
Sht2Row?=?Sheet2.Range("C65536").End(xlUp).Row?'獲得sheet2工作表C列的最大行號
Set?Dic?=?CreateObject("Scripting.Dictionary")?'創建壹個字典對象
For?Each?Rng1?In?Sheet1.Range("F2:F"?&?Sht1Row)
Dic(Rng1.Value)?=?Rng1.Offset(0,?-1).Value?'向字典對象中填充數據,以sheet1工作表F列數據為key,E列為item
Next
For?Each?Rng2?In?Sheet2.Range("C2:C"?&?Sht2Row)
Rng2.Offset(0,?2)?=?Dic(Rng2.Value)?'在字典key中檢索sheet2工作表C列數值,返回對應的item值
Next
End?Sub
Sub?test2()
'用循環法返回對應的數據,數據量大時速度較慢
Dim?Sht1Row,?Sht2Row?As?Long
Dim?Rng1,?Rng2?As?Range
Sht1Row?=?Sheet1.Range("F65536").End(xlUp).Row?'獲得sheet1工作表F列的最大行號
Sht2Row?=?Sheet2.Range("C65536").End(xlUp).Row?'獲得sheet2工作表C列的最大行號
For?Each?Rng2?In?Sheet2.Range("C2:C"?&?Sht2Row)
For?Each?Rng1?In?Sheet1.Range("F2:F"?&?Sht1Row)
If?Rng1?=?Rng2?Then?Rng2.Offset(0,?2)?=?Rng1.Offset(0,?-1)?'在sheet1工作表的F列查找sheet2工作表C列數值,返回對應的E列數據
Next
Next
End?Sub