I can't find my compiler error.

ashley12

New Member
Joined
Dec 6, 2020
Messages
35
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,487
Office Version
  1. 365
Platform
  1. Windows
You've used the wrong type of double quotes for your sheet names and ranges. All should be of the vertical type "" that you have used to clear the cells at the end of your code.

Also, a couple of your ranges only have a row number, the column letter is missing.
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,487
Office Version
  1. 2019
Platform
  1. Windows
Hi,
as already mentioned, you have used wrong type of quote marks

See if this update to your code will do what you want

VBA Code:
Option Explicit

Sub save()
    Dim frm         As Worksheet, database As Worksheet
    Dim iRow        As Long, r As Long
    Dim i           As Integer

    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
    r = 3
    For i = 1 To 49
        r = r + 2
        With frm.Cells(r, 5)
         database.Cells(iRow, i).Value = .Value
        .Value = ""
        End With
    Next i
  
End Sub

I have only glanced through your code (which has other errors) but hopefully, indexing is correct

Dave
 

ashley12

New Member
Joined
Dec 6, 2020
Messages
35
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thank you so much jasonb75 and dmt32!!! The form has run now
 

ashley12

New Member
Joined
Dec 6, 2020
Messages
35
Office Version
  1. 365
Platform
  1. Windows
Thanks Dave. I'm trying to enhance the form with checkboxes, so users don't have to type too much. I come across some problems that I outlined in this new thread. I would appreciate it if I could get some advice from you on it as well.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,840
Messages
5,627,186
Members
416,228
Latest member
JOOTEISHERE

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top