将If语句与Vlookup结合使用

Question

我正在尝试将vlookup公式与If条件组合。更确切地说，我有一个工作表，如果列EANDF的单元格为0，我希望在G列的单元格中执行vlookup公式。需要明确的是，变量lastrow3和ws1是WELL定义的，并且具有适当的值。此外，我运行了没有if条件的代码(只有vlookup(，它运行得很好。因此，这些变量不可能存在问题。此外，我希望vlookup是动态的。我已经写了4种不同类型的代码。我在下面提供它们。

CODE1

For i = 2 To lastrow3
ws1.Range("G" & i).Formula = "=IF(E" & i & "+ F" & i & " = 0, " & Chr(34) & "VLOOKUP(C"&i&",'[NOT OK.xlsx]Sheet1'!F:I,4,FALSE)" & Chr(34) & ", " & Chr(34) & "No" & Chr(34) & ")"
Next i

这段代码在这部分给了我一个错误："VLOOKUP(C"&i&",说有语法错误。

CODE2

For Each cell In ws1.Range("G2:G" & lastrow3)
If cell.Offset(0, -1).Value = 0 Then
If cell.Offset(0, -2).Value = 0 Then
cell.Formula = "=IFERROR(VLOOKUP(C2,'[NOT OK.xlsx]Sheet1'!F:I,4,FALSE),"""")"
End If
End If
Next cell

此代码在此部分中给出一个错误：If cell.Offset(0, -1).Value = 0 Then表示存在类型不匹配。此外，该代码没有动态vlookup，因此它只为小区C2进行vlookup。

CODE3

With ws1
For i = 2 To lastrow3
If .Cells(i, "E").Value2 = 0 And .Cells(i, "F").Value2 = 0 Then
.Cells(i, "G").Formula = "=IFERROR(VLOOKUP($C$" & i&",'[NOT OK.xlsx]Sheet1'!F:I,4,FALSE),"""")"
End If
Next cell
End With

这段代码在这部分给了我一个错误：.Cells(i, "G").Formula = "=IFERROR(VLOOKUP($C$" & i&",'[NOT OK.xlsx]Sheet1'!F:I,4,FALSE),"""")"说存在语法错误。

CODE4

With ws1
.Range("G2:G" & lastrow3).Formula = "=IFERROR(VLOOKUP(C2,'[NOT OK.xlsx]Sheet1'!F:I,4,FALSE),"""")"
.Range("G2:G" & lastrow3).Value = .Range("G2:G" & lastrow3).Value
End With

此代码运行良好(这是我运行并验证变量定义良好的代码(位不包括If条件。我想声明这段代码运行得非常快(使用with-ws1和End with(，所以如果可以通过添加if条件来运行这段代码，那么它将是完美的。

CODE5(->我尝试在CODE4中添加If条件(

With ws1
If  .Range("G2:G" & lastrow3).Offset(0, -1).Value = 0 And .Range("G2:G" & lastrow3).Offset(0, -2).Value = 0 Then
.Range("G2:G" & lastrow3).Formula = "=IFERROR(VLOOKUP(C2,'[NOT OK.xlsx]Sheet1'!F:I,4,FALSE),"""")"
End If
.Range("G2:G" & lastrow3).Value = .Range("G2:G" & lastrow3).Value
End With

这段代码在这部分给了我一个错误：If .Range("G2:G" & lastrow3).Offset(0, -1).Value = 0 And .Range("G2:G" & lastrow3).Offset(0, -2).Value = 0 Then说存在类型不匹配。

摘要

我正在努力将代码的速度和准确性结合起来。根据我搜索到的，带有with和End with的代码是最快的。然而，如果我设法用另一个代码来解决它，那么就没有问题了。我得到的主要错误是在vlookup公式中，当我试图使其动态时，以及在if条件中，当我们试图查找偏移是否为0值时。

到目前为止，我正在添加整个代码(尽管我认为这并不重要(

完整代码

Sub Pharma_Stock_Report()

Dim StartTime As Double
Dim SecondsElapsed As Double
StartTime = Timer

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim lastrow1 As Long
Dim lastrow2 As Long
Dim lastrow3 As Long
Dim CopyRange As Range
Dim i As Long
spath1 = Application.ThisWorkbook.Path & "Pharma replenishment.xlsm"
spath2 = Application.ThisWorkbook.Path & "NOT OK.xlsx"
Workbooks.Open spath1
Workbooks.Open spath2
Set ws1 = Workbooks("Pharma Stock Report.xlsm").Worksheets("Pharma Stock Report")
Set ws2 = Workbooks("Pharma replenishment.xlsm").Worksheets("Replenishment")
Set ws3 = Workbooks("NOT OK.xlsx").Worksheets("Sheet1")
With ws1
.Cells.Clear
End With
With ws2
lastrow1 = .Range("A" & Rows.Count).End(xlUp).Row
For i = 4 To lastrow1
If .Cells(i, "D").Interior.ColorIndex = -4142 Or .Cells(i, "D").Interior.ColorIndex = 2 Then
If CopyRange Is Nothing Then
Set CopyRange = .Range("A" & i & ":F" & i)
Else
Set CopyRange = Union(CopyRange, .Range("A" & i & ":F" & i))
End If
End If
Next i
End With
CopyRange.Copy
With ws1.Range("A2")
.PasteSpecial xlPasteValues
End With
ws2.Range("A4:F4").Copy
With ws1.Range("A1")
.PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False
Workbooks("Pharma replenishment.xlsm").Close
ws3.Range("I1").Copy
With ws1.Range("G1")
.PasteSpecial xlPasteValues
End With
lastrow3 = ws1.Range("D" & Rows.Count).End(xlUp).Row
With ws1
.Range("G2:G" & lastrow3).Formula = "=IFERROR(VLOOKUP(C2,'[NOT OK.xlsx]Sheet1'!F:I,4,FALSE),"""")"
.Range("G2:G" & lastrow3).Value = .Range("G2:G" & lastrow3).Value
End With
Application.CutCopyMode = False
Workbooks("NOT OK.xlsx").Close
With ws1.Range("A1:G" & lastrow3)
.HorizontalAlignment = xlCenter
.Font.Color = vbBlack
.Font.Name = "Calibri"
.Font.Italic = False
.Borders.LineStyle = xlDouble
.Borders.Weight = xlThin
.Borders.Color = vbBlack
End With
With ws1.Range("A1:G1")
.Interior.ColorIndex = 41
.Font.Bold = True
.Font.Size = 14
.Font.Italic = True
End With
With ws1.Range("A1", Range("A1").End(xlDown).End(xlToRight))
.EntireColumn.AutoFit
End With
ws1.Range("A1:G1").AutoFilter
ws1.AutoFilter.Sort.SortFields.Add Key:=Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws1.AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True

SecondsElapsed = Round(Timer - StartTime, 2)
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation

End Sub

Answer 1

CODE1存在一些问题。您在VLOOKUP周围插入了一些Chr(34)，除非您希望单元格显示查找公式，而不是查找的结果，否则它们需要继续。

ws1.Range("G" & i).Formula = "=IF(E" & i & "+ F" & i & " = 0, " & "VLOOKUP(C" & i & ",'[NOT OK.xlsx]Sheet1'!F:I,4,FALSE)" & ", " & Chr(34) & "No" & Chr(34) & ")"

清空零和#N/A-

ws1.Range("G" & i).Formula = "=IFNA(IF(E" & i & "+ F" & i & " = 0, " & "IF(IFNA(VLOOKUP(C" & i & ",'[NOT OK.xlsx]Sheet1'!F:I,4,FALSE),0)=0,"""",IFNA(VLOOKUP(C" & i & ",'[NOT OK.xlsx]Sheet1'!F:I,4,FALSE),0))" & ", " & Chr(34) & "No" & Chr(34) & "),"""")"

Answer 2

第一个代码很容易修复：实际上存在语法错误，因为vba需要变量名和&-运算符之间有空格。添加空间，如

ws1.Range("G" & i).Formula = "=IF(E" & i & "+ F" & i & " = 0, " & Chr(34) & "VLOOKUP(C" & i & ",'[NOT OK.xlsx]Sheet1'!F:I,4,FALSE)" & Chr(34) & ", " & Chr(34) & "No" & Chr(34) & ")"

将解决这个问题。

Answer 3

您当前的代码正在测试一系列值，这可能是您遇到类型问题的原因

相反，在公式中添加if测试会更容易(然后使用R1C1表示法创建引用查找(

.Range("G2:G" & lastrow3).FormulaR1C1 = "=IF(AND(RC[-2]=0,RC[-1]=0),IFERROR(VLOOKUP(RC[-4],'[NOT OK.xlsx]Sheet1'!C[-1]:C[2],4,FALSE),""""),"Null Values")"