通过宏添加 excel IF 语句.编译时出错

Question

我的代码适用于UserForm，其中所有信息将在保存在表中之前写入。我想用 IF 语句添加两个公式，但我不断收到编译错误。

我根本不知道为什么它会检查该代码...不应该。它只应粘贴到用户表单文本之间的右列。

有人可以告诉我我在这里做错了什么吗？

我尝试了.Formula和.Value但没有任何效果。

一直"编译时出错"。

Private Sub SaveData()
'Copy input values to sheet.
Dim lrow As Long
Dim ws As Worksheet
Set ws = Tabelle1
Dim i As Integer
lrow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).row
For i = 1 To lrow
If ws.Cells(i, 1).Value = Me.E1GCharge.Value Then
MsgBox "Diese Charge ist schon vorhanden, bitte einfach Bestand 
aktualisieren"
Exit Sub
End If
Next i
With ws
.Cells(lrow, 1).Value = Me.E1GCharge.Value
.Cells(lrow, 3).Value = Me.E1GMatName.Value
.Cells(lrow, 4).Value = Me.E1Gtype.Value
.Cells(lrow, 5).Value = Me.E1GMatNumber.Value
.Cells(lrow, 6).Value = Me.E1GExpiryDate.Value
.Cells(lrow, 6).NumberFormat = "mmm.yyyy"
.Cells(lrow, 7).Value = Me.E1GBoxPcs.Value
.Cells(lrow, 8).Value = Me.E1GAmmount.Value
.Cells(lrow, 9).Value = Me.E1GUnit.Value
.Cells(lrow, 10).Value = Me.E1Gkonz.Value
'IF Statement compiling Error "=IF([@[Rest Tubes]]<>"N/A""
.Cells(lrow, 11).FormulaLocal  = "=IF([@[Rest Tubes]]<>"N/A",[@Pieces]-[@[Auslagerung Total]]/[@[Number of tubes Ammount]],[@Pieces]-[@[Auslagerung pcs]])"
'IF Statement compiling Error "=IF([@[Rest Tubes]]="N/A""
.Cells(lrow, 12).FormulaLocal  = "=IF([@[Number of tubes Ammount]]="N/A";"N/A";[@Pieces]*[@[Number of tubes Ammount]]-[@[Auslagerung Total]])"          
End With
'Clear input controls.
Me.E1GMatName.Value = ""
Me.E1Gtype.Value = ""
Me.E1GMatNumber.Value = ""
Me.E1GExpiryDate.Value = ""
Me.E1GBoxPcs.Value = ""
Me.E1GAmmount.Value = ""
Me.E1Gkonz.Value = ""
Me.E1GUnit.Value = ""
Call GetData 
End Sub

Answer 1

Chr(34( 而不是 "(双引号(

双引号内不能有双引号。因此，您必须使用 Chr 函数将"内部"双引号替换为 Chr(34(，这为您提供：

'IF Statement compiling Error "=IF([@[Rest Tubes]]<>"N/A""
.Cells(lrow, 11).FormulaLocal = "=IF([@[Rest Tubes]]<>" & Chr(34) & "N/A" & Chr(34) & ",[@Pieces]-[@[Auslagerung Total]]/" & "[@[Number of tubes Ammount]],[@Pieces]-[@[Auslagerung pcs]])"
'IF Statement compiling Error "=IF([@[Rest Tubes]]="N/A""
.Cells(lrow, 12).FormulaLocal = "=IF([@[Number of tubes Ammount]]=" & Chr(34) & "N/A" & Chr(34) & ";" & Chr(34) & "N/A" & Chr(34) & ";[@Pieces]*[@[Number of tubes Ammount]]-[@[Auslagerung Total]])"

为了便于阅读，建议每行不要传递 80 个字符，所以不要害怕在每行末尾用"_"分隔行：

'IF Statement compiling Error "=IF([@[Rest Tubes]]<>"N/A""
.Cells(lrow, 11).FormulaLocal = "=IF([@[Rest Tubes]]<>" & Chr(34) _
& "N/A" & Chr(34) & ",[@Pieces]-[@[Auslagerung Total]]/" _
& "[@[Number of tubes Ammount]],[@Pieces]-[@[Auslagerung pcs]])"
'IF Statement compiling Error "=IF([@[Rest Tubes]]="N/A""
.Cells(lrow, 12).FormulaLocal = "=IF([@[Number of tubes Ammount]]=" _
& Chr(34) & "N/A" & Chr(34) & ";" & Chr(34) & "N/A" & Chr(34) _
& ";[@Pieces]*[@[Number of tubes Ammount]]-[@[Auslagerung Total]])"