我有一个包含多个电子表格的工作簿。其中一张被称为"Master Filtered“,另一张被称为"MTL OR TOR”。我想用第二列中来自"MTL or TOR“表的查询值来填充"Master filtered”表的K列。我写了这段代码,但它不工作。
Sub MTL_OR_TOR()
Dim AcctNb As String
Dim result As String
Worksheets("Master Filtered").Activate
Dim lastrow As Long
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For G = 4 To lastrow
AcctNb = Cells(G, 3).Value
Set myrange = Worksheets("MTL OR TOR").Range("AA4:AB685") 'Range in which the table MTL or TOR should be entered
result = Application.WorksheetFunction.VLookup(AcctNb, myrange, 2, False)
Range("K" & G).Value = result
Next
End Sub
你知道为什么这段代码不能工作以及如何修复它吗?
我在想,也许我的错误是在以Set myrange= Worksheets("MTL OR TOR")
开头的行中,但找不到它。
发布于 2019-05-30 08:38:46
Sub MTL_OR_TOR()
' Name your variables in a meaningful way and indicate their type
Dim strAcctNb As String
Dim strResult As String
Dim lngLastRow As Long
Dim lngLoop As Long
Dim rngLookup As Range
'Set your range and variables before you execute the code for readability
Set rngLookup = Worksheets("MTL OR TOR").Range("AA4:AB685") 'Range in which the table MTL or TOR should be entered
'Do not Activate or Select unless you really have to
'Worksheets("Master Filtered").Activate
With Worksheets("Master Filtered")
lngLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For lngLoop = 4 To lngLastRow
strAcctNb = .Cells(lngLoop, 3).Value
strResult = Application.WorksheetFunction.VLookup(strAcctNb, rngLookup, 2, False)
.Range("K" & lngLoop).Value = strResult
Next
End With
End Sub
https://stackoverflow.com/questions/56365578
复制相似问题