當前位置:成語大全網 - 新華字典 - VBA實現查找值 返回特定的值

VBA實現查找值 返回特定的值

Sub?test1()

'用字典法返回對應的數據速度較快,但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