I can't find my compiler error.

ashley12

New Member
Joined
Dec 6, 2020
Messages
43
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

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
Back
Top