I'm new to VBA. I tried to make an Excel form that transfer the info to a table in another sheet. Below is my code. It says "Compiler error: variable not defined" But I've checked and I have defined all variable. The debugger pointed at the line 2.
VBA Code:
Option Explicit
Sub save()
Dim frm As Worksheet
Dim database As Worksheet
Dim iRow As Long
Set frm = ThisWorkbook.Sheets(“Form”)
Set database = ThisWorkbook.Sheets(“Database”)
'define row number
iRow = database.Range(“A” & Application.Rows.Count).End(xlUp).Row + 1
'transfer data from form to database
With database
.Cells(iRow, 1).Value = frm.Range(“E5”).Value
.Cells(iRow, 2).Value = frm.Range(“E7”).Value
.Cells(iRow, 3).Value = frm.Range(“E9”).Value
.Cells(iRow, 4).Value = frm.Range(“E11”).Value
.Cells(iRow, 5).Value = frm.Range(“E13”).Value
.Cells(iRow, 6).Value = frm.Range(“E15”).Value
.Cells(iRow, 7).Value = frm.Range(“E17”).Value
.Cells(iRow, 8).Value = frm.Range(“E19”).Value
.Cells(iRow, 9).Value = frm.Range(“E21”).Value
.Cells(iRow, 10).Value = frm.Range(“E23”).Value
.Cells(iRow, 11).Value = frm.Range(“E25”).Value
.Cells(iRow, 12).Value = frm.Range(“E27”).Value
.Cells(iRow, 13).Value = frm.Range(“E32”).Value
.Cells(iRow, 14).Value = frm.Range(“E34”).Value
.Cells(iRow, 15).Value = frm.Range(“E36”).Value
.Cells(iRow, 16).Value = frm.Range(“E38”).Value
.Cells(iRow, 17).Value = frm.Range(“E40”).Value
.Cells(iRow, 18).Value = frm.Range(“E45”).Value
.Cells(iRow, 19).Value = frm.Range(“E47”).Value
.Cells(iRow, 20).Value = frm.Range(“E49”).Value
.Cells(iRow, 21).Value = frm.Range(“E51”).Value
.Cells(iRow, 22).Value = frm.Range(“E56”).Value
.Cells(iRow, 23).Value = frm.Range(“E58”).Value
.Cells(iRow, 24).Value = frm.Range(“E60”).Value
.Cells(iRow, 25).Value = frm.Range(“E62”).Value
.Cells(iRow, 26).Value = frm.Range(“E64”).Value
.Cells(iRow, 27).Value = frm.Range(“E66”).Value
.Cells(iRow, 28).Value = frm.Range(“E68”).Value
.Cells(iRow, 29).Value = frm.Range(“E73”).Value
.Cells(iRow, 30).Value = frm.Range(“E75”).Value
.Cells(iRow, 31).Value = frm.Range(“E77”).Value
.Cells(iRow, 32).Value = frm.Range(“E82”).Value
.Cells(iRow, 33).Value = frm.Range(“E84”).Value
.Cells(iRow, 34).Value = frm.Range(“E90”).Value
.Cells(iRow, 35).Value = frm.Range(“E92”).Value
.Cells(iRow, 36).Value = frm.Range(“E94”).Value
.Cells(iRow, 37).Value = frm.Range(“E96”).Value
.Cells(iRow, 38).Value = frm.Range(“E98”).Value
.Cells(iRow, 39).Value = frm.Range(“E103”).Value
.Cells(iRow, 40).Value = frm.Range(“E105”).Value
.Cells(iRow, 41).Value = frm.Range(“E107”).Value
.Cells(iRow, 42).Value = frm.Range(“E109”).Value
.Cells(iRow, 43).Value = frm.Range(“E111”).Value
.Cells(iRow, 44).Value = frm.Range(“E113”).Value
.Cells(iRow, 45).Value = frm.Range(“E115”).Value
.Cells(iRow, 46).Value = frm.Range(“E117”).Value
.Cells(iRow, 47).Value = frm.Range(“E119”).Value
.Cells(iRow, 48).Value = frm.Range(“E121”).Value
.Cells(iRow, 49).Value = frm.Range(“123”).Value
End With
'reset the form to empty
With frm
.Range(“E5”).Value = ""
.Range(“E7”).Value = ""
.Range(“E9”).Value = ""
.Range(“E11”).Value = ""
.Range(“E13”).Value = ""
.Range(“E15”).Value = ""
.Range(“E17”).Value = ""
.Range(“E19”).Value = ""
.Range(“E21”).Value = ""
.Range(“E23”).Value = ""
.Range(“E25”).Value = ""
.Range(“E27”).Value = ""
.Range(“E32”).Value = ""
.Range(“E34”).Value = ""
.Range(“E36”).Value = ""
.Range(“E38”).Value = ""
.Range(“E40”).Value = ""
.Range(“E45”).Value = ""
.Range(“E47”).Value = ""
.Range(“E49”).Value = ""
.Range(“E51”).Value = ""
.Range(“E56”).Value = ""
.Range(“E58”).Value = ""
.Range(“E60”).Value = ""
.Range(“E62”).Value = ""
.Range(“E64”).Value = ""
.Range(“E66”).Value = ""
.Range(“E68”).Value = ""
.Range(“E73”).Value = ""
.Range(“E75”).Value = ""
.Range(“E77”).Value = ""
.Range(“E82”).Value = ""
.Range(“E84”).Value = ""
.Range(“E90”).Value = ""
.Range(“E92”).Value = ""
.Range(“E94”).Value = ""
.Range(“E96”).Value = ""
.Range(“E98”).Value = ""
.Range(“E103”).Value = ""
.Range(“E105”).Value = ""
.Range(“E107”).Value = ""
.Range(“E109”).Value = ""
.Range(“E111”).Value = ""
.Range(“E113”).Value = ""
.Range(“E115”).Value = ""
.Range(“E117”).Value = ""
.Range(“E119”).Value = ""
.Range(“E121”).Value = ""
.Range(“123”).Value = ""
End With
End Sub