Forum Discussion
johnnycasali365
Sep 19, 2022Copper Contributor
Formula not working
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!
What happens if you use Formula instead of Formula2Local ?