• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Could someone please help me with a 'variable not defined' error?

K

Kristen_cancerkiller

Member
I have the following code and when I save it in vba editor I receive a 'Variable not defined' error on 'Cell' in the row directly under 'If Worksheets'. I have tried to search the error online and on Chanoo and am not having any luck. I know it is a small fix but I just don't know what the fix is. Please help?

>>> use code -tags <<<​
Code: 
Public Sub DivAdminApproval()
  'Call DivAdminApproval
     
   With Worksheets("Travel Expense Voucher")
        If Worksheets("Travel Expense Voucher").Cells("F5") = 2 Then
        For Each Cell In .Range("O15:O45")
            If Cells.Value = 0.58 Then
                MsgBox "You have selected reimbursement at the 'HIGH' mileage rate ($.58/mile).  To receive reimbursement at this rate, Division Administrator Approval is Required.", vbCritical, "Important:"
                Exit Sub
            End If
        Next myCell
       
End Sub
 
Last edited by a moderator:
try:
Code: 
Public Sub DivAdminApproval()
'Call DivAdminApproval
Dim myCell As Range
With Worksheets("Travel Expense Voucher")
  If .Range("F5") = 2 Then
    For Each myCell In .Range("O15:O45")
      If myCell.Value = 0.58 Then
        MsgBox "You have selected reimbursement at the 'HIGH' mileage rate ($.58/mile). To receive reimbursement at this rate, Division Administrator Approval is Required.", vbCritical, "Important:"
        Exit Sub
      End If
    Next myCell
  End If
End With
End Sub
 
A VBA help must read : Dim !​
In order to declare each variable used in the procedure like you can see on any VBA blog / forum …​
​
By the way, you have forgotten the code tags ‼ (via the Insert icon after the smiley …)​
​
 
This solved my error but now not all of my macros are not running. :( Here is the code:

>>> 2nd time - use code -tags - please <<<
​
Code: 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Call HideRows
  Call ProjNumbrReq
  Call DivAdminApproval
End Sub

Public Sub HideRows()
'When a row begins with X in Travel Expense Codes worksheet, hide the row

  Const beginRow As Long = 3
  Const endRow As Long = 38
  Const chkCol As Long = 14

  Dim rowCnt As Long
  Dim ws As Worksheet

  Set ws = ThisWorkbook.Worksheets("Travel Expense Codes")

  For rowCnt = endRow To beginRow Step -1
       With ws.Cells(rowCnt, chkCol)
           .EntireRow.Hidden = (.Value = "X")
       End With
  Next rowCnt
End Sub

Public Sub ProjNumbrReq()
'Call ProjNumbrReq
Dim myCell As Range
With Worksheets("Travel Expense Voucher")
  If .Range("F5") = 2 Then
    For Each myCell In .Range("W15:W45")
      If myCell.Value > 0 And .Cells(myCell.row, "N") = "" Then
        MsgBox "Project Number must be provided on each line where reimbursement is being claimed.", vbCritical, "Important:"
        Exit Sub
       End If
     Next myCell
  End If
End With
End Sub

Public Sub DivAdminApproval()
'Call DivAdminApproval
Dim myCell As Range
With Worksheets("Travel Expense Voucher")
  If .Range("F5") = 2 Then
    For Each myCell In .Range("O15:O45")
      If myCell.Value = 0.58 Then
        MsgBox "You have selected reimbursement at the 'HIGH' mileage rate ($.58/mile). To receive reimbursement at this rate, Division Administrator Approval is Required.", vbCritical, "Important:"
        Exit Sub
      End If
    Next myCell
  End If
End With
End Sub
 
Last edited:
Seems all right here. Put a break point (F9 on the keyboard) on the first line of each of the 4 macros and step through the code (with keyboard F8) when it's called to see what's happenong/not happening.
 
p45cal said:
Seems all right here. Put a break point (F9 on the keyboard) on the first line of each of the 4 macros and step through the code (with keyboard F8) when it's called to see what's happenong/not happening.
Click to expand...

I have done the F9 portion...and when I selected 'Save' as this is how this is supposed to be run, the first macro, which does run, is highlighted but it stops there. I'm sorry, but I'm not sure what you mean by the F8 portion of your message. I'm sorry, this is all new to me.
 
When the first line of a macro is highlighted, you can press the key on the keyboard labelled F8 repeatedly to step through the code one line at a time.
 
When I run the macro and use the F8 key, I can see the highlight moving through the different macros, calls, etc...but I am not receiving the error messages in the second 2 macros and I have verified multiple times that the conditions exist to present those. :(
 
I have attached it here. Please disregard the extra columns on the Travel Expense Voucher spreadsheet... I'm working on another process at the same time as this one. :)
 

Attachments

  • TravelForm WorkingCopy8.xlsm
    172.8 KB · Views: 1
It works here all right with a message popping up for both macros (rows 21 and 16).
Are you pressing F8 enough times? It will loop through the loops too.
Perhaps instead of putting a break at the beginning, put a break at the Exit Sub lines only.
 
p45cal said:
It works here all right with a message popping up for both macros (rows 21 and 16).
Are you pressing F8 enough times? It will loop through the loops too.
Perhaps instead of putting a break at the beginning, put a break at the Exit Sub lines only.
Click to expand...

I'm not sure what you mean by putting in a break? Yes, I was able to 'F8' all the way through all of the macros but the messages I was expecting weren't being given. Did you get the message boxes?
 
You must log in or register to reply here.
Back
Top