Formula not working

Occasional Contributor

Hi,

 

Im trying to copy a formula to a list in a sheet after filling a questionare but it gives me a FALSE error. Formula works when written on Excel.

This is the code, formula in line 20:

 

 

Sub TablaDatos()

Dim HojaEntrada As Worksheet
Dim HojaReservas As ListObject
Dim NuevaFila As ListRow
Dim Pregunta As Byte

Set HojaEntrada = ThisWorkbook.Sheets("Reservas")
Set HojaReservas = HojaEntrada.ListObjects("TablaReservas")
Set NuevaFila = HojaReservas.ListRows.Add

With NuevaFila
.Range(1) = Hoja1.Range("B4").Value
.Range(2) = Hoja1.Range("C4").Value
.Range(3) = Hoja1.Range("D4").Value
.Range(4) = Hoja1.Range("E4").Value
.Range(5) = Hoja1.Range("F4").Value
.Range(6) = Hoja1.Range("G4").Value
.Range(7) = Hoja1.Range("H4").Value
.Range(8) = Hoja1.Range("I4").Formula2Local = "=IFERROR(SUM(VLOOKUP([@CATERING],Datos!A2:B18,2)*[@PAX]),"""")"
.Range(9) = Hoja1.Range("J4").Value

End With

MsgBox "Reserva guardada", vbInformation

Pregunta = MsgBox("¿Quieres limpiar el formulario?", vbYesNo + vbQuestion)

If Pregunta = vbNo Then Exit Sub
Hoja1.Range("B4").ClearContents
Hoja1.Range("C4").ClearContents
Hoja1.Range("D4").ClearContents
Hoja1.Range("E4").ClearContents
Hoja1.Range("F4").ClearContents
Hoja1.Range("G4").ClearContents
Hoja1.Range("H4").ClearContents
Hoja1.Range("I4").ClearContents
Hoja1.Range("J4").ClearContents
Hoja1.Range("I4").Formula2Local = "=iferror(vlookup([@CATERING],Datos!A2:B18,2)*[@PAX],"""")"

End Sub

 

 

Any help will be highly appreciated.

Cheers!

1 Reply

@johnnycasali365 

What happens if you use Formula instead of Formula2Local ?