Confused About Run-time error '1004': Application-defined or object-defined error

[MAIN_CPE]

Hello All,

I am new to forums and figured I'd turn to someone out here to possible give me a handle with this problem.

The problem that I am having is that I keep getting the error: Run-time error '1004': Application-defined or object-defined error. When I check my code it is having difficulties with the line Cells(C, 2).Select. I was wondering if any knows how to fix this. THANKS FOR THE HELP!!

HERE'S MY CODE:



[VBA]
Loop
'****SELECT SUPPORT INFORMATION*******
Do While Sheets("CURRENT SHIFT").Cells(D, 1).Value <> ""

D = D + 1
E = D - 1
Range(Cells(E, 2), Cells(E, 9)).Select
Selection.Copy
Sheets("BACKLOG").Select

Do While Sheets("BACKLOG").Cells(C, 2).Value <> ""
C = C + 1
Loop
Cells(C, 2).Select
ActiveSheet.Paste
Cells(C, 10) = DTE
Cells(C, 11) = SUP
Range(Cells(C, 2), Cells(C, 11)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With

With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

Sheets("CURRENT SHIFT").Select
Selection.ClearContents

Loop
End Sub
[/VBA]

[Bob Phillips]

Post your workbook to test it on.

[MAIN_CPE]

Thanks for the reply, here it is.

[GTO]

Greetings,

Welcome to vbaexpress! I'm sure you'll like it here, as there's just plain great folks here, who will go out of their way to be helpful. Not sure if you've taken a gander yet, but there is also a KB with tons of great code examples.

As to your question, you left out the bit that it works fine in a Standard Module, but fails when plunked into the worksheet's module that the commandbutton belongs to. At minimum, I believe these lines need changed.

Sheets("BACKLOG").Select
Do While Sheets("BACKLOG").Cells(c, 2).Value <> ""
c = c + 1
Loop
Cells(c, 2).Select
ActiveSheet.Paste
Cells(F, 10) = DTE
Cells(F, 11) = SUP
Range(Cells(F, 2), Cells(F, 11)).Select


The range Cell(c,2) needs qualified, as you are trying to select a cell on the BACKLOG sheet, but Cells(c,2) belongs to CURRENT SHIFT.



See, when you ran the code in a Standard Module, an unqualified Range/Cells results in excel referencing the Range to the activesheet. But when you have the code in a sheet's module, an unqualified Range is referenced to that sheet, as it is in that object's module. Does that make sense?


The lines thereafter containing 'F', such as Cells(F, 10) = DTE will fail no matter where the procedure is housed, as F is never given a value. You didn't have these in the code in the Standard Module.


Might I make a couple of suggestions? These are not negative, they simply would have saved me countless headaches had I known earlier.

While looking at the code, go to the menubar, Tools|Options, select the Editor tab, and tick the 'Require Variable Declaration' checkbox. This will then automatically insert Option Explicit at the top of any module you create. Option Explicit's inclusion requires you to declare all variables used. This is a good idea that will save you time looking for an errant miss-spelled variable later, as well as get you used to declaring variables as the appropriate type.

Along the same lines, declare all variables, and give them meaningful names. If you read through a handful of threads here, you'll see that while different folks use different naming styles or notation, you can usually easily follow the code as the variables' names indicate what they represent, such as a Long Integer, an Object, a Range and so on.

Hope that helps,

Mark

[MAIN_CPE]

Thanks for the greetings.

I noticed the code that you posted. I messed up posting it. I sent out my dirty code that I was using to test. SORRY! It is suppose to be:

Sheets("BACKLOG").Select
Do While Sheets("BACKLOG").Cells(c, 2).Value <> ""
c = c + 1
Loop
Cells(c, 2).Select
ActiveSheet.Paste
Cells(c, 10) = DTE
Cells(c, 11) = SUP
Range(Cells(c, 2), Cells(c, 11)).Select

I was just doing some testing and I changed that variable to "F". But I forgot to change it back to the orginal code when I posted it back on here.

The updated code should be attached. The real issue I am having is:

Cells(c, 2).Select

[Bob Phillips]

Runs through fine in my test.

[GTO]

Hi again,

Unless I am missing something, I believe the code runs fine under Module2, but fouls if run from the sheet's module. You still have not qualified the ranges that need to refer to BACKLOG.

You could just call the sub in the standard module from the button.
[vba]
Private Sub CommandButton2_Click()
Call CPLY
End Sub
[/vba]

Mark

[MAIN_CPE]

That fixed it.

Thanks a lot.