Question

=if(g12="","",vlookup(g12,sheet2!$a2:$b45,2,0,)) 这个函数怎么用vba表述出来，

Answer 1

假设你是想H12中输入这个公式则
第一种：
sub test ()
set a=thisworkbooks.sheets.("sheet1")
set b=thisworkbooks.sheets.("sheet2")
if a.range("G12")<>"" then
for i=2 to 45
if a.range("G12")= b.cells(i,1) then
a.range("H12")=b.cells(i,2)
end if
next
end if
end sub
第二种：
sub test()
set a=thisworkbooks.sheets.("sheet1")
a.range("H12")="=if(g12="","",vlookup(g12,sheet2!$a2:$b45,2,0,)) "

end sub

追问

如果现在我有两个excel A;B,我要把A sheet1的内容完全copy B的sheet2中，该如何编写vba代码，

追答

sub test()
set a = workbooks.open(A表格的路径)
set b = workbooks.open(B表格的路径)
a.sheets("sheet1").cells.copy b.sheets("sheet2").cells(1,1)

end sub

Answer 2

Dim t As String
Dim c As Range
If Len(Range("G12").Text) = 0 Then '如果G12="",公式单元格=""
ActiveCell.Value = ""
Else
t = Range("G12").Value
Set c = Sheet2.Range("A2:A45").Find(t, , xlValues, xlWhole) '在sheet2的A2：A45搜索G12的值，精确查找
If Not c Is Nothing Then
ActiveCell.Value = c.Offset(0, 1).Value '如果找到匹配的G12，那么公式单元格返回对应的B列的结果
Else
ActiveCell.Value = "#N/A!" '找不到，就输出错误值
End If
End If