Visual Basic Compile Error

[k3639]

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!

 

[scottfoxall]

I assume you start at y=6, then scan down 1 by 1 ? then if ="ME" get the
offset value (0,+1)?

 

[k3639]

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]

Firstly, try declaring the variables by using the Dim statment. See if that
works, that the usual error under variables not delclared error message!

Dim x As Long, y As Long, Me_total As Long

 

[Dave Peterson]

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]

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.