Add excel IF statement via macro. Error while compiling

Question

My code works with UserForm, where all information will be written before saving in a table. I want to add two formulas with IF statement, but I keep getting a compile error.

I don't know why it checks that code at all... It shouldn't. It should only be pasted to the right column among with text from the user form.

Can someone tell me what I'm doing wrong here?

I tried .Formula and .Value but nothing works.

All the time "Error while compiling".

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

The problem is your quotation marks are not correct. Quotes-within-quotes need to be doubled up. ...but are you looking for the #N/A error because if so that's not the correct way. — ashleedawg
– ashleedawg, Commented Oct 12, 2018 at 8:54
@ashleedawg: I think he's just asking why the code won't compile whenever he uncomments each of those two lines. — VBasic2008
– VBasic2008, Commented Oct 12, 2018 at 9:14
Yes i didn't know how to make that formula compile. Thx for help — KROLN
– KROLN, Commented Oct 12, 2018 at 9:48

VBasic2008 · Accepted Answer · 2018-10-12 09:30:16Z

Chr(34) instead of " (double quotes)

You cannot have double quotes inside double quotes. Therefore you have to use the Chr function to replace the 'inside' double quotes with Chr(34) which gives you:

    '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]])"

For readability it is recommended not to pass 80 characters per line, so don't be afraid to split the lines with ' _' at the end of each line:

    '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]])"

Collectives™ on Stack Overflow

Add excel IF statement via macro. Error while compiling

1 Answer 1

Chr(34) instead of " (double quotes)

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Chr(34) instead of " (double quotes)

Comments

Your Answer

Sign up or log in

Post as a guest

Related