Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Visual Basic Compile Error
I created an Expense Voucher spreadsheet that automatically calcs
reimbursements due,using the following code: Function Calc_ME_Charges() 'Initialize variables x = 12 y = 6 ME_total = 0 'Loop through Travel Expenses For Each Cells(x, y) In Cells(21, 24) If Cells(x, y).Value = "ME" Then ME_total = ME_total + Cells(x + 1, y + 1).Value y = y + 3 'Check for end of row If y = 27 Then x = x + 1 'Reset y to beginning of row y = 6 End If End If Loop I get a Visual Basic Compile Error: Variable Required - Can't Assign to this expression, and I am unable to close out of VB. The result on the cells in the Excel Spreadsheet is #NAME?. Any suggestions or ideas to resolve would be appreciated. Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Visual Basic Compile Error
I assume you start at y=6, then scan down 1 by 1 ? then if ="ME" get the
offset value (0,+1)? "k3639" wrote: I created an Expense Voucher spreadsheet that automatically calcs reimbursements due,using the following code: Function Calc_ME_Charges() 'Initialize variables x = 12 y = 6 ME_total = 0 'Loop through Travel Expenses For Each Cells(x, y) In Cells(21, 24) If Cells(x, y).Value = "ME" Then ME_total = ME_total + Cells(x + 1, y + 1).Value y = y + 3 'Check for end of row If y = 27 Then x = x + 1 'Reset y to beginning of row y = 6 End If End If Loop I get a Visual Basic Compile Error: Variable Required - Can't Assign to this expression, and I am unable to close out of VB. The result on the cells in the Excel Spreadsheet is #NAME?. Any suggestions or ideas to resolve would be appreciated. Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Visual Basic Compile Error
Yes, when I enable macros in the spreadsheet it immediately jumps to VB and
highlights the Cells Reference with a Compile Error, Variable Required unable to assign to this expression. When I try to locate the Cells Reference it says it is Hidden. "scottfoxall" wrote: I assume you start at y=6, then scan down 1 by 1 ? then if ="ME" get the offset value (0,+1)? "k3639" wrote: I created an Expense Voucher spreadsheet that automatically calcs reimbursements due,using the following code: Function Calc_ME_Charges() 'Initialize variables x = 12 y = 6 ME_total = 0 'Loop through Travel Expenses For Each Cells(x, y) In Cells(21, 24) If Cells(x, y).Value = "ME" Then ME_total = ME_total + Cells(x + 1, y + 1).Value y = y + 3 'Check for end of row If y = 27 Then x = x + 1 'Reset y to beginning of row y = 6 End If End If Loop I get a Visual Basic Compile Error: Variable Required - Can't Assign to this expression, and I am unable to close out of VB. The result on the cells in the Excel Spreadsheet is #NAME?. Any suggestions or ideas to resolve would be appreciated. Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Visual Basic Compile Error
Firstly, try declaring the variables by using the Dim statment. See if that
works, that the usual error under variables not delclared error message! Function Calc_ME_Charges() Dim x As Long, y As Long, Me_total As Long 'Initialize variables x = 12 y = 6 ME_total = 0 'Loop through Travel Expenses For Each Cells(x, y) In Cells(21, 24) If Cells(x, y).Value = "ME" Then ME_total = ME_total + Cells(x + 1, y + 1).Value y = y + 3 'Check for end of row If y = 27 Then x = x + 1 'Reset y to beginning of row y = 6 End If End If Loop "k3639" wrote: Yes, when I enable macros in the spreadsheet it immediately jumps to VB and highlights the Cells Reference with a Compile Error, Variable Required unable to assign to this expression. When I try to locate the Cells Reference it says it is Hidden. "scottfoxall" wrote: I assume you start at y=6, then scan down 1 by 1 ? then if ="ME" get the offset value (0,+1)? "k3639" wrote: I created an Expense Voucher spreadsheet that automatically calcs reimbursements due,using the following code: Function Calc_ME_Charges() 'Initialize variables x = 12 y = 6 ME_total = 0 'Loop through Travel Expenses For Each Cells(x, y) In Cells(21, 24) If Cells(x, y).Value = "ME" Then ME_total = ME_total + Cells(x + 1, y + 1).Value y = y + 3 'Check for end of row If y = 27 Then x = x + 1 'Reset y to beginning of row y = 6 End If End If Loop I get a Visual Basic Compile Error: Variable Required - Can't Assign to this expression, and I am unable to close out of VB. The result on the cells in the Excel Spreadsheet is #NAME?. Any suggestions or ideas to resolve would be appreciated. Thanks! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Visual Basic Compile Error
This line doesn't make sense to me:
For Each Cells(x, y) In Cells(21, 24) Cells(21,24) is one cell--so why try to loop. But worse, cells(x,y) is a specific cell in the worksheet. It's not a variable that you can use in the for/next loop. More normal samples would look like: dim myCell as range for each mycell in worksheets("sheet1").range("a1:a99") But I don't understand what you're really doing. k3639 wrote: I created an Expense Voucher spreadsheet that automatically calcs reimbursements due,using the following code: Function Calc_ME_Charges() 'Initialize variables x = 12 y = 6 ME_total = 0 'Loop through Travel Expenses For Each Cells(x, y) In Cells(21, 24) If Cells(x, y).Value = "ME" Then ME_total = ME_total + Cells(x + 1, y + 1).Value y = y + 3 'Check for end of row If y = 27 Then x = x + 1 'Reset y to beginning of row y = 6 End If End If Loop I get a Visual Basic Compile Error: Variable Required - Can't Assign to this expression, and I am unable to close out of VB. The result on the cells in the Excel Spreadsheet is #NAME?. Any suggestions or ideas to resolve would be appreciated. Thanks! -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Visual Basic Compile Error
I will attach a copy of the spreadsheet, just one page, to show you, but the
code was written by one of our accountants, and she has handwritten instructions on what she is trying to accomplish. basically, she wants to calculate meal and mileage totals, that is why the loop is included, because these are two separate modules. "Dave Peterson" wrote: This line doesn't make sense to me: For Each Cells(x, y) In Cells(21, 24) Cells(21,24) is one cell--so why try to loop. But worse, cells(x,y) is a specific cell in the worksheet. It's not a variable that you can use in the for/next loop. More normal samples would look like: dim myCell as range for each mycell in worksheets("sheet1").range("a1:a99") But I don't understand what you're really doing. k3639 wrote: I created an Expense Voucher spreadsheet that automatically calcs reimbursements due,using the following code: Function Calc_ME_Charges() 'Initialize variables x = 12 y = 6 ME_total = 0 'Loop through Travel Expenses For Each Cells(x, y) In Cells(21, 24) If Cells(x, y).Value = "ME" Then ME_total = ME_total + Cells(x + 1, y + 1).Value y = y + 3 'Check for end of row If y = 27 Then x = x + 1 'Reset y to beginning of row y = 6 End If End If Loop I get a Visual Basic Compile Error: Variable Required - Can't Assign to this expression, and I am unable to close out of VB. The result on the cells in the Excel Spreadsheet is #NAME?. Any suggestions or ideas to resolve would be appreciated. Thanks! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compile Error Argument Not optional | Excel Discussion (Misc queries) | |||
Compile error in hidden module | Excel Discussion (Misc queries) | |||
MS Visual Basic Error...from MAcro | Excel Discussion (Misc queries) | |||
Linking to Visual Basic for Applications Help Files | Excel Discussion (Misc queries) | |||
changing the visual basic in office 2003 to visual studio net | Excel Discussion (Misc queries) |