VBA Vlookup 条件（如果有的话）源数据和 vlookup 范围在不同的工作表上
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25151106/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Vlookup condition (if then) source data and vlookup range are on different worksheets
提问by LearnForever
I need your help with conditional Vlookup. I found a code that works fine if there is vlookup value in the source data but it fails once there is a missing value. Also I need to add a condition ('If the value is found by Lookup, then return "Old" (from 2nd column in vlookup table) 'If the value is NOT found, then return "New" (just text which is not coming from vlookup table). Could you help me? Thank you,'Russ
我需要你的条件 Vlookup 帮助。我发现一个代码在源数据中有 vlookup 值时可以正常工作，但一旦缺少值就会失败。我还需要添加一个条件（'如果通过查找找到该值，则返回“旧”（从 vlookup 表中的第二列）'如果未找到该值，则返回“新”（只是未出现的文本）来自 vlookup 表）。你能帮帮我吗？谢谢你，'Russ
Sub Vlookup_Condition()
Dim rng As Range
Dim i As Long
With ActiveSheet.Cells
Set rng = .Range("A1:A" & .Cells(.Rows.count, 1).End(xlUp).row)
For i = 2 To rng.Rows.count
'If the value is found by Lookup, then return "Old" (from 2nd column in vlookup table)
'If the value is NOT found, then return "New" (just text which is not coming from vlookup
'table)
rng.Cells(i, 2) = Application.WorksheetFunction.VLookup(.Cells(i, 1), Sheets("Lookuptable").Range("A:B"), 2, False)
Next
End With
End Sub
结束子
回答by Jagadish Dabbiru
As per your puzzle. I found a solution like this Russ
根据你的拼图。我找到了这样的解决方案 Russ
UPDATED & TESTED
更新和测试
Sub Vlookup_Condition()
Dim rng As Range
Dim i As Long
Application.ScreenUpdating = False
Worksheets("DataFile").Activate
Range("R2").Activate
With Worksheets("DataFile").Cells
Set rng = .Range("O1:O" & .Cells(.Rows.count, 1).End(xlUp).row)
For i = 2 To rng.Rows.count
rng.Cells(i, 4) = Application.VLookup(.Cells(i, 15), Sheets("Lookuptable").Range("A:B"), 2, False)
If IsError(rng.Cells(i, 4)) Then
If rng.Cells(i, 4) = CVErr(xlErrNA) Then ' Given if condition to change it from "#NA" to "New"
rng.Cells(i, 4) = "New"
End If
End If
Next
End With
Application.ScreenUpdating = True
End Sub
回答by user3271518
Try this
尝试这个
Sub Vlookup_Condition()
Dim rng As Range
Dim ws as Worksheet
Dim i As Long
Set ws = ActiveSheet
i =2
With ws.Range("A1:A" & Rows.Count)
.Formula = "=VLookup(" & ws.Cells(2,1).Address & ",Lookuptable!$A:$B,2,false)"
End With
Do while ws.Cells(i, 1) <> ""
if ws.Cells(i,2) <> "OLD" Then ws.Cells(i,2) = "New"
i = i +1
Loop
Hope this helps there is a more concise way to do this but this way might be easier to build off of.
希望这有助于有一种更简洁的方法来做到这一点，但这种方式可能更容易构建。
Ok after further review TRY THIS! lol
好的，经过进一步试试这个！哈哈
With ActiveSheet
Set rng = .Range("A1:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
For i = 2 to rng.Rows.Count
rng.Cells(i, 2) = Application.WorksheetFunction.VLookup(ActiveSheet.Cells(i,1), Sheets("Lookuptable").Range("A:B"), 2, False)
Next
End With
End Sub
I really hope this works for ya mate if not ill probably not do any actual work AT work tomorrow until i figure out how i totally punted this help lol
我真的希望这对你有用，如果没有生病，明天可能不会在工作中做任何实际工作，直到我弄清楚我是如何完全放弃这个帮助的，哈哈
