PDA

View Full Version : [SOLVED] xldown is giving me an error



TButhe
12-16-2004, 01:16 PM
I am trying to use xldown to select a range of cells and clear the contents. I have recorded the code and it turns out exactly the same. However, when I try to run it I get an error "Compile error: Expected function or variable" (if it is expected then why is it an error???:D ) and the xldown is highlighted as the error. What could be wrong? I have used this and am using it in another macro and it works fine. Is it just something in this macro that I have wrong?



Sub Copy3()
' Copy3 Macro
' Macro recorded 12/15/2004 by Tracy Buthe
ActiveSheet.Unprotect
Columns("R:R").Select
Selection.ClearContents
Columns("P:P").Select
Selection.Copy
Columns("R:R").Select
ActiveSheet.Paste
Columns("N:N").Select
Selection.Copy
Columns("P:P").Select
ActiveSheet.Paste
Columns("L:L").Select
Selection.Copy
Columns("N:N").Select
ActiveSheet.Paste
Columns("J:J").Select
Selection.Copy
Columns("L:L").Select
ActiveSheet.Paste
Columns("H:H").Select
Selection.Copy
Columns("J:J").Select
ActiveSheet.Paste
Columns("F:F").Select
Selection.Copy
Columns("H:H").Select
ActiveSheet.Paste
Range("F4") = InputBox("Enter the last day of the month you are working with, such as 3/31/04")
Columns("F:F").Select
Selection.NumberFormat = "General"
Range("F4").Select
Selection.NumberFormat = "[$-409]mmm-yy;@"
Columns("F:F").Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("F1:F4").Select
Selection.Locked = True
Selection.FormulaHidden = False
Range("F5").Select
Selection.End(xldown).Select
Selection.ClearContents
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowInsertingRows:=True, AllowDeletingRows:=True, AllowFiltering:=True _
, AllowUsingPivotTables:=True, AllowSorting:=True
End Sub


Thanks!
TJ

:pacsmile:

mvidas
12-16-2004, 01:38 PM
Hi TJ,

I'm not sure why that wouldn't be working, it looks like you want to clear the last used cell below F5 before hitting any blanks.

Here is your same code, a bit cleaner and no cells are selected so it will run a bit faster as well:


Sub Copy3()
ActiveSheet.Unprotect
Columns("R").ClearContents
Columns("P").Copy Columns("R")
Columns("N").Copy Columns("P")
Columns("L").Copy Columns("N")
Columns("J").Copy Columns("L")
Columns("H").Copy Columns("J")
Columns("F").Copy Columns("H")
Range("F4") = InputBox("Enter the last day of the month you are working with, such as 3/31/04")
Columns("F:F").NumberFormat = "General"
Range("F4").NumberFormat = "[$-409]mmm-yy;@"
Columns("F").Locked = False
Columns("F").FormulaHidden = False
Range("F1:F4").Locked = True
Range("F5").End(xlDown).ClearContents
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowInsertingRows:=True, AllowDeletingRows:=True, AllowFiltering:=True _
, AllowUsingPivotTables:=True, AllowSorting:=True
End Sub

I noticed that your "xldown" is lowercase for some reason, perhaps you may want to try to substitute the value for xldown instead?

Range("F5").End(-4121).ClearContents

I don't know why you'd get that error with xldown being highlighted. Can you post a sample workbook perhaps?

Matt

TButhe
12-16-2004, 02:00 PM
cleaned up code. I don't know if I will every learn this stuff but it is fun trying. I still got the error though when I tried your first solution.

Range("F5").End(-4121).ClearContents
doesn't give me an error but it doesn't delete the cell contents. I've attached the file.

BTW - I'm using Excel XP and Windows 2000

mvidas
12-16-2004, 04:55 PM
TJ,

OK sorry about the delay, had a lot of work to do, then a few other questions as well.

I figured out what was wrong, this has been driving me nuts for a while now!

In your Module4, you have a sub named xldown. If you change the name to this, you'll get rid of the error.

I don't know why the -4121 line didnt delete the contents, but I've got a question. Are you trying to delete from F5 through F5.end(xldown) or just the last one? The way your macro is coded, it will only clear the contents of the single cell that is reached when xldown is done. If you're trying to clear the entire range, use the line:
Range("F5", Range("F5").End(xlDown)).ClearContents

Chances are, thats why you're not getting your desired results. You may also want to consider using

Range("F5", Range("F65536").End(xlup)).ClearContents
Just in case you have a blank cell in the range. Say you wanted to delete F5:F100, but for some reason F60 was blank. The line with xlDown will only delete F5:F59, rather than everything in F from F5 and below. The modified line (with xlup) will do just that.

I hope I haven't confused you, please let me know if you need any help or any more clarification!
Matt

Jacob Hilderbrand
12-16-2004, 04:55 PM
You have a sub callled xldown in Module4. That is causing a conflict. Rename that sub. You can then clear the range like this:


Range("F5", Range("F5").End(xldown)).ClearContents

Ken Puls
12-18-2004, 04:23 PM
Hi TButhe,

Just curious to know if your issues have been solved...?

TButhe
12-20-2004, 08:19 AM
DUH!!!!!!! Thanks everyone!! Let's marked this solved and a lesson well learned. WATCH WHAT YOU NAME YOUR MACROS!!!

:yay