Compile Error Expected Function or Variable

[Puuuuma]

I'm working on a macro to sort through a report I inport into Excel and prepare it to be put into a pivot table. It combines a variable number of lines into one summary line. Then I want it to select the data it has combined and delete it. I've gotten the first part to work pretty well, but I started to work on the second half of it and it stopped working.

"Code"
Sub test() 'This is what highlights in yellow
'
' test Macro
'
' Copy total poundage up
ActiveCell.Offset(0, 1).Range("A1").Select
selection.End(xlDown).Select ' but "selection" is selected
selection.End(xlDown).Select
selection.Copy
selection.End(xlUp).Select
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False

There is more to the code, but I don't think it goes further than this.

It was working fine, then I started getting the "Compile Error: Expected Function or variable" error. Please help!!

 

[stanleydgromjr]

Puuuuma,

Please post all the macro code.

At the beginning of your posted code, enter the following without the four double quote marks:
"["code"]"


'Your code goes here.


At the end of your posted code, enter the following without the four double quote marks:
"["/code"]"


Have a great day,
Stan

 

[Puuuuma]

Full Code of the macro. Please help!

Sub test()
'
' test Macro
'
' Copy total poundage up
ActiveCell.Offset(0, 1).Range("A1").Select
selection.End(xlDown).Select
selection.End(xlDown).Select
selection.Copy
selection.End(xlUp).Select
ActiveCell.Offset(-1, 0).Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
' Extended prices
ActiveCell.Offset(1, 0).Range("A1").Select
selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[-8]*RC[-5]"
' If there is more than one row to summarize, it copies the formula for all the rows
If selection.Offset(1, -1).Range("A1") <> 0 Then
ActiveCell.Select
selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Range("A1").Select
Range(selection, selection.End(xlUp)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End If
' weighted average
ActiveCell.Select
ActiveCell.Offset(-1, 0).Range("A1").Select
selection.End(xlToLeft).Select
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(R[1]C[7]:R[3]C[7])/RC[1]"
ActiveCell.Select
' pasting values, so can delete cells
selection.Copy
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
' selecting the cells to be deleted - this code was what put the program over the edge
ActiveCell.Offset(1, -8).Select
Range(selection, selection.End(xlDown)).Select
Range(selection, selection.End(xlToRight)).Select
Range(selection, selection.End(xlToRight)).Select
Range(selection, selection.End(xlToRight)).Select
End Sub

 

[Norie]

puuma

That code compiles fine for me.

But there is one thing I notice, in your code selection is lower case.

Normally it should be Selection.

You don't have anything eg sub, function etc named selection do you?

 

[Puuuuma]

Norie,

I tried coping the code into another macro, and I'm still getting the same error. I tried changing "selection" to "Selection", but as soon as I move off the line it goes back to "selection". Thanks for the reply, though!

 

[Puuuuma]

I tried running another macro and I'm getting the same error message. All of my "selection"s are lower case, but it won't allow me to make them uppercase. This is two days in a row that I've been having this problem. I used a formating macro this morning (in a different workbook), no problem - is it possible that the workbook is corrupt?

 

[Norie]

I'll ask again, do you have anything called selection?

Or have you used that to name anything in the past.

Also it might be worth looking under Tools>References... to see if anything's amiss.

 

[Puuuuma]

That was it, I had created another macro called "selection" to write the code for the last little section, which is why it appeared to have caused the problem. THANK YOU SO MUCH - I was really getting frustrated.

 

[Norie]

It didn't just appear to cause the problem it definitely caused the problem.

Anyways glad to see you appear to have things sorted now.