Excel宏vba上vlookup公式
问题描述:
嗨,我是新的Excel宏。我想为vlookup创建一个宏,以便我可以应用到很多单元格,但它不起作用。Excel宏vba上vlookup公式
Sub haha()
Dim wb As Workbook
Dim ws, ws1 As Worksheet
Set wb = ActiveWorkbook
Set ws = ActiveWorkbook.Sheets(1)
Set ws1 = ActiveWorkbook.Sheets(2)
wb.Activate
ws1.Select
Range("E1").Formula = "=+VLOOKUP(" & ws1.Range("C6") & "," & ws.Range("c7:d10") & ",2,FALSE)"
End sub
我想在当前工作表中查找一个值到另一个工作表的范围以返回唯一值。请帮忙! :)
答
只需更换这3行:
wb.Activate
ws1.Select
Range("E1").Formula = "=+VLOOKUP(" & ws1.Range("C6") & "," & ws.Range("c7:d10") & ",2,FALSE)"
与此:
ws1.Range("E1").Formula = "=VLOOKUP(C6," & ws.Range("C7:D10").Address(External:=1) & ",2,FALSE)"
在ws1.Range("C6")
部分不需要sheet
和address
,在这种情况下,该细胞是在公式所在的同一张表中ws1.Range("C6")
将始终返回C6
。
至于ws.Range("c7:d10")
部分,您需要“外部参照”,因为它指的是另一张纸。尽管Range.Address Property
的External
引用包含文件名,但它会在公式进入同一工作簿时被删除。
答
看看这个。
Sub haha()
Dim wb As Workbook
Dim ws As Worksheet, ws1 As Worksheet
Dim rng As Range
Set wb = ActiveWorkbook
Set ws = ActiveWorkbook.Sheets(1)
Set ws1 = ActiveWorkbook.Sheets(2)
wb.Activate
Set rng = ws.Range("c7:d10")
ws1.Range("E1").Formula = "=VLOOKUP(" & ws1.Range("C6").Address & "," & rng.Worksheet.Name & "!" & rng.Address & ",2,FALSE)"
End Sub
在vlookup中的两个范围之后放置'.Address'。 –
注意谢谢! – koky