下面的VBA代码应该是1)选择另一个文件中的单元格范围2)在活动文件中插入一个新列3)当它遍历C列中的每一行时,它在另一个文件中的单元格范围中查找相应的值从#1开始4)在查找的值旁边的列中输入值
然而,它只是不工作,我不知道什么是错误的作为一个新手在这个…请帮助。
Sub Translate()
Dim PartName As String
Dim CellName As String
Dim PartNameList As Range
Dim i As Integer
Set PartNameList = Workbooks("C:Users" "DesktopPart name translation.xlsx").Worksheets("Reference").Range("A1:B2000")
Worksheets("Initial").Columns("D").Insert
For i = 2 To Range("C2:C3000").Count
If IsEmpty("C" & i) = True Then Exit For
PartName = WorksheetFunction.VLookup(Range("C" & i), PartNameList, 2, False)
Range("C" & i).Value = PartName
Next i
End Sub
没有传递有效的工作簿名称:
Set PartNameList = Workbooks( _
"C:Users" "DesktopPart name translation.xlsx"). _
Worksheets("Reference").Range("A1:B2000")
似乎你缺少一个环境变量,这应该修复它:
dim uName as String: uName = Environ("Username")
Set PartNameList = Workbooks( _
"C:Users" & uName & "DesktopPart name translation.xlsx"). _
Worksheets("Reference").Range("A1:B2000")
但是,如果工作簿已经打开,您不需要像这样完全限定它,您可以使用文件名:
Set PartNameList = _
Workbooks("Part name Translation.xlsx").Worksheets("Reference").Range("A1:B2000")
最后,如果工作簿没有打开,我想你需要首先打开它:
dim uName as String: uName = Environ("Username")
dim wbPartName as Workbook
Set wbPartName = Workbooks.Open( _
"C:Users" & uName & "DesktopPart name translation.xlsx")
Set partNameList = wbPartName.Worksheets("Reference").Range("A1:B2000")