|
实际的工作,可能表格中会有几十万条不等,所以,原来是用VLOOKUP往下拉,但是这个很费时间,前面的型号是用循环语句跑出来的,希望这个也能用VBA来代替下,哪位高手帮下忙。我是初学阶段,能看懂个大概,但是自己写不出。
这种表格,先跑单元格,用字典要麻烦一点,跑单元格容易点,写代码费神 - Sub test()
- Dim LastRow, i, sht As Worksheet
- Set sht = Sheets("Sheet1")
- LastRow = sht.Range("A" & Rows.Count).End(xlUp).Row
- With Sheets("选型表")
- For i = 3 To LastRow
- If sht.Cells(i, "A") = "" Then sht.Cells(i, "J") = "" Else sht.Cells(i, "J") = Application.WorksheetFunction.VLookup(sht.Cells(i, "A"), .Range("A2:D3"), 2, 0)
- If sht.Cells(i, "B") = "" Then sht.Cells(i, "K") = "" Else sht.Cells(i, "K") = Application.WorksheetFunction.VLookup(sht.Cells(i, "B"), .Range("A6:C9"), 2, 0)
- If sht.Cells(i, "C") = "" Then sht.Cells(i, "L") = "" Else sht.Cells(i, "L") = Application.WorksheetFunction.VLookup(sht.Cells(i, "C"), .Range("A11:D12"), 2, 0)
- If sht.Cells(i, "D") = "" Then sht.Cells(i, "M") = "" Else sht.Cells(i, "M") = Application.WorksheetFunction.VLookup(sht.Cells(i, "D"), .Range("A15:B17"), 2, 0)
- If sht.Cells(i, "E") = "" Then sht.Cells(i, "N") = "" Else sht.Cells(i, "N") = Application.WorksheetFunction.VLookup(sht.Cells(i, "E"), .Range("A19:D22"), 2, 0)
- If sht.Cells(i, "F") = "" Then sht.Cells(i, "O") = "" Else sht.Cells(i, "O") = Application.WorksheetFunction.VLookup(sht.Cells(i, "F"), .Range("A24:D26"), 2, 0)
- If sht.Cells(i, "G") = "" Then sht.Cells(i, "P") = "" Else sht.Cells(i, "P") = Application.WorksheetFunction.VLookup(sht.Cells(i, "G"), .Range("A28:D29"), 2, 0)
- If sht.Cells(i, "H") = "" Then sht.Cells(i, "Q") = "" Else sht.Cells(i, "Q") = Application.WorksheetFunction.VLookup(sht.Cells(i, "H"), .Range("A31:D31"), 2, 0)
- Next
- End With
- End Sub
复制代码
|
|