Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with a piece of code
Im getting the error Method 'Cells' of object '_Global' failed on the
following function My understanding is this is because im havent explicitly defined the sheet im working in. I have placed **ERROR ON THE LINE BELOW*** to indicate where the error is little help please. If i add another call to the sheet calc inside the loop then i get overflow errors. The weird thing is the code still runs and still produces the correct output. If you need to see the other functions this function calls just ask and ill post them as well. Sub Build_Calc() Call Filter Dim diff As Integer Dim cell As Object Dim counter As Integer Dim counter1 As Integer Dim no_task As Integer Dim start_date Dim end_date Dim start_time Dim end_time Dim shift Dim day_shift Dim night_shift Dim labour As Integer Sheets("junk").Select Range("L7").Select diff = Selection.Value diff = diff + 1 Range("L3").Select start_date = Selection.Value Sheets("Calc").Select Cells.Select Selection.ClearContents For counter = 1 To (diff * 2) Cells(1, counter).Select ActiveCell.Value = start_date Cells(2, counter).Select ActiveCell.Value = "Day" counter = counter + 1 Cells(1, counter).Select ActiveCell.Value = start_date Cells(2, counter).Select ActiveCell.Value = "Night" start_date = start_date + 1 If counter = (diff * 2) Then Cells(2, counter + 1).Select ActiveCell.Value = "Hours" End If Next counter Columns("A:A").Select Selection.Insert shift:=xlToRight Sheets("junk").Select Columns("C:C").Select Selection.SpecialCells(xlCellTypeConstants, 23).Select Selection.Copy Sheets("Calc").Select Range("A2").Select ActiveSheet.Paste Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Rows("1:1").EntireRow.AutoFit Sheets("junk").Select Range("l9").Select no_task = Selection.Value Range("l11").Select day_shift = Selection.Value Range("l13").Select night_shift = Selection.Value For counter1 = 1 To no_task Sheets("junk").Select Cells(counter1 + 1, 5).Select labour = ActiveCell.Value Cells(counter1 + 1, 11).Select shift = ActiveCell.Value Cells(counter1 + 1, 6).Select start_date = ActiveCell.Value Cells(counter1 + 1, 8).Select end_date = ActiveCell.Value Sheets("calc").Select For counter = 1 To (diff * 2) If shift = "24" Then ' 24 hour calendar ' day shift *** ERROR ON THE LINE BELOW*** If Cells(1, counter).Value start_date And Cells(1, counter).Value < end_date Then Cells(counter1 + 2, counter).Value = labour counter = counter + 1 ElseIf (Cells(1, counter).Value = start_date And Cells(1, counter).Value < end_date) And start_time < night_shift Then Cells(counter1 + 2, counter).Value = labour counter = counter + 1 ElseIf (Cells(1, counter).Value start_date And Cells(1, counter).Value = end_date) And end_time = day_shift Then Cells(counter1 + 2, counter).Value = labour counter = counter + 1 ElseIf (Cells(1, counter).Value = start_date And Cells(1, counter).Value = end_date) And end_time = day_shift And start_time < night_shift Then Cells(counter1 + 2, counter).Value = labour counter = counter + 1 Else counter = counter + 1 End If ' night shift If Cells(1, counter).Value start_date And Cells(1, counter).Value < end_date Then Cells(counter1 + 2, counter).Value = labour ElseIf (Cells(1, counter).Value = start_date And Cells(1, counter).Value < end_date) And start_time day_shift Then Cells(counter1 + 2, counter).Value = labour ElseIf (Cells(1, counter).Value start_date And Cells(1, counter).Value = end_date) And end_time night_shift Then Cells(counter1 + 2, counter).Value = labour ElseIf (Cells(1, counter).Value = start_date And Cells(1, counter).Value = end_date) And end_time night_shift Then Cells(counter1 + 2, counter).Value = labour ElseIf (Cells(1, counter).Value = start_date And Cells(1, counter + 1).Value = end_date) And end_time < day_shift Then Cells(counter1 + 2, counter).Value = labour End If ' 12 hour calendar ElseIf Cells(1, counter).Value = start_date And Cells(1, counter).Value <= end_date Then Cells(counter1 + 2, counter).Value = labour counter = counter + 1 End If 'add no of hours per shift Cells(counter1 + 2, (diff * 2) + 2).Select ActiveCell.Value = shift 'add total If counter1 = no_task Then Cells(counter1 + 3, 1).Value = "Histogram Total" Call histo_total(diff, no_task) Call histo_shifter(diff, no_task) Call histo_chart_build(no_task) Call s_curve_values(diff, no_task) Call s_curve_totals(diff, no_task) Call s_curve_chart_build(no_task) End If Next counter Next counter1 End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with a piece of code
Have you tried putting in some Debug.Print statements to figure out the
values of some of your variables before it has an error? I'm wondering what the value of Counter might be. -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Joseph Atie" wrote: Im getting the error Method 'Cells' of object '_Global' failed on the following function My understanding is this is because im havent explicitly defined the sheet im working in. I have placed **ERROR ON THE LINE BELOW*** to indicate where the error is little help please. If i add another call to the sheet calc inside the loop then i get overflow errors. The weird thing is the code still runs and still produces the correct output. If you need to see the other functions this function calls just ask and ill post them as well. Sub Build_Calc() Call Filter Dim diff As Integer Dim cell As Object Dim counter As Integer Dim counter1 As Integer Dim no_task As Integer Dim start_date Dim end_date Dim start_time Dim end_time Dim shift Dim day_shift Dim night_shift Dim labour As Integer Sheets("junk").Select Range("L7").Select diff = Selection.Value diff = diff + 1 Range("L3").Select start_date = Selection.Value Sheets("Calc").Select Cells.Select Selection.ClearContents For counter = 1 To (diff * 2) Cells(1, counter).Select ActiveCell.Value = start_date Cells(2, counter).Select ActiveCell.Value = "Day" counter = counter + 1 Cells(1, counter).Select ActiveCell.Value = start_date Cells(2, counter).Select ActiveCell.Value = "Night" start_date = start_date + 1 If counter = (diff * 2) Then Cells(2, counter + 1).Select ActiveCell.Value = "Hours" End If Next counter Columns("A:A").Select Selection.Insert shift:=xlToRight Sheets("junk").Select Columns("C:C").Select Selection.SpecialCells(xlCellTypeConstants, 23).Select Selection.Copy Sheets("Calc").Select Range("A2").Select ActiveSheet.Paste Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Rows("1:1").EntireRow.AutoFit Sheets("junk").Select Range("l9").Select no_task = Selection.Value Range("l11").Select day_shift = Selection.Value Range("l13").Select night_shift = Selection.Value For counter1 = 1 To no_task Sheets("junk").Select Cells(counter1 + 1, 5).Select labour = ActiveCell.Value Cells(counter1 + 1, 11).Select shift = ActiveCell.Value Cells(counter1 + 1, 6).Select start_date = ActiveCell.Value Cells(counter1 + 1, 8).Select end_date = ActiveCell.Value Sheets("calc").Select For counter = 1 To (diff * 2) If shift = "24" Then ' 24 hour calendar ' day shift *** ERROR ON THE LINE BELOW*** If Cells(1, counter).Value start_date And Cells(1, counter).Value < end_date Then Cells(counter1 + 2, counter).Value = labour counter = counter + 1 ElseIf (Cells(1, counter).Value = start_date And Cells(1, counter).Value < end_date) And start_time < night_shift Then Cells(counter1 + 2, counter).Value = labour counter = counter + 1 ElseIf (Cells(1, counter).Value start_date And Cells(1, counter).Value = end_date) And end_time = day_shift Then Cells(counter1 + 2, counter).Value = labour counter = counter + 1 ElseIf (Cells(1, counter).Value = start_date And Cells(1, counter).Value = end_date) And end_time = day_shift And start_time < night_shift Then Cells(counter1 + 2, counter).Value = labour counter = counter + 1 Else counter = counter + 1 End If ' night shift If Cells(1, counter).Value start_date And Cells(1, counter).Value < end_date Then Cells(counter1 + 2, counter).Value = labour ElseIf (Cells(1, counter).Value = start_date And Cells(1, counter).Value < end_date) And start_time day_shift Then Cells(counter1 + 2, counter).Value = labour ElseIf (Cells(1, counter).Value start_date And Cells(1, counter).Value = end_date) And end_time night_shift Then Cells(counter1 + 2, counter).Value = labour ElseIf (Cells(1, counter).Value = start_date And Cells(1, counter).Value = end_date) And end_time night_shift Then Cells(counter1 + 2, counter).Value = labour ElseIf (Cells(1, counter).Value = start_date And Cells(1, counter + 1).Value = end_date) And end_time < day_shift Then Cells(counter1 + 2, counter).Value = labour End If ' 12 hour calendar ElseIf Cells(1, counter).Value = start_date And Cells(1, counter).Value <= end_date Then Cells(counter1 + 2, counter).Value = labour counter = counter + 1 End If 'add no of hours per shift Cells(counter1 + 2, (diff * 2) + 2).Select ActiveCell.Value = shift 'add total If counter1 = no_task Then Cells(counter1 + 3, 1).Value = "Histogram Total" Call histo_total(diff, no_task) Call histo_shifter(diff, no_task) Call histo_chart_build(no_task) Call s_curve_values(diff, no_task) Call s_curve_totals(diff, no_task) Call s_curve_chart_build(no_task) End If Next counter Next counter1 End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with a piece of code
i know the values of the variables because the output is accurate
at the end of the run after everything is complete it comes back to that piece of code and give the error it even calls all the functions at the bottom correctly. i know its all right because the end out put is 2 charts "Barb Reinhardt" wrote: Have you tried putting in some Debug.Print statements to figure out the values of some of your variables before it has an error? I'm wondering what the value of Counter might be. -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Joseph Atie" wrote: Im getting the error Method 'Cells' of object '_Global' failed on the following function My understanding is this is because im havent explicitly defined the sheet im working in. I have placed **ERROR ON THE LINE BELOW*** to indicate where the error is little help please. If i add another call to the sheet calc inside the loop then i get overflow errors. The weird thing is the code still runs and still produces the correct output. If you need to see the other functions this function calls just ask and ill post them as well. Sub Build_Calc() Call Filter Dim diff As Integer Dim cell As Object Dim counter As Integer Dim counter1 As Integer Dim no_task As Integer Dim start_date Dim end_date Dim start_time Dim end_time Dim shift Dim day_shift Dim night_shift Dim labour As Integer Sheets("junk").Select Range("L7").Select diff = Selection.Value diff = diff + 1 Range("L3").Select start_date = Selection.Value Sheets("Calc").Select Cells.Select Selection.ClearContents For counter = 1 To (diff * 2) Cells(1, counter).Select ActiveCell.Value = start_date Cells(2, counter).Select ActiveCell.Value = "Day" counter = counter + 1 Cells(1, counter).Select ActiveCell.Value = start_date Cells(2, counter).Select ActiveCell.Value = "Night" start_date = start_date + 1 If counter = (diff * 2) Then Cells(2, counter + 1).Select ActiveCell.Value = "Hours" End If Next counter Columns("A:A").Select Selection.Insert shift:=xlToRight Sheets("junk").Select Columns("C:C").Select Selection.SpecialCells(xlCellTypeConstants, 23).Select Selection.Copy Sheets("Calc").Select Range("A2").Select ActiveSheet.Paste Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Rows("1:1").EntireRow.AutoFit Sheets("junk").Select Range("l9").Select no_task = Selection.Value Range("l11").Select day_shift = Selection.Value Range("l13").Select night_shift = Selection.Value For counter1 = 1 To no_task Sheets("junk").Select Cells(counter1 + 1, 5).Select labour = ActiveCell.Value Cells(counter1 + 1, 11).Select shift = ActiveCell.Value Cells(counter1 + 1, 6).Select start_date = ActiveCell.Value Cells(counter1 + 1, 8).Select end_date = ActiveCell.Value Sheets("calc").Select For counter = 1 To (diff * 2) If shift = "24" Then ' 24 hour calendar ' day shift *** ERROR ON THE LINE BELOW*** If Cells(1, counter).Value start_date And Cells(1, counter).Value < end_date Then Cells(counter1 + 2, counter).Value = labour counter = counter + 1 ElseIf (Cells(1, counter).Value = start_date And Cells(1, counter).Value < end_date) And start_time < night_shift Then Cells(counter1 + 2, counter).Value = labour counter = counter + 1 ElseIf (Cells(1, counter).Value start_date And Cells(1, counter).Value = end_date) And end_time = day_shift Then Cells(counter1 + 2, counter).Value = labour counter = counter + 1 ElseIf (Cells(1, counter).Value = start_date And Cells(1, counter).Value = end_date) And end_time = day_shift And start_time < night_shift Then Cells(counter1 + 2, counter).Value = labour counter = counter + 1 Else counter = counter + 1 End If ' night shift If Cells(1, counter).Value start_date And Cells(1, counter).Value < end_date Then Cells(counter1 + 2, counter).Value = labour ElseIf (Cells(1, counter).Value = start_date And Cells(1, counter).Value < end_date) And start_time day_shift Then Cells(counter1 + 2, counter).Value = labour ElseIf (Cells(1, counter).Value start_date And Cells(1, counter).Value = end_date) And end_time night_shift Then Cells(counter1 + 2, counter).Value = labour ElseIf (Cells(1, counter).Value = start_date And Cells(1, counter).Value = end_date) And end_time night_shift Then Cells(counter1 + 2, counter).Value = labour ElseIf (Cells(1, counter).Value = start_date And Cells(1, counter + 1).Value = end_date) And end_time < day_shift Then Cells(counter1 + 2, counter).Value = labour End If ' 12 hour calendar ElseIf Cells(1, counter).Value = start_date And Cells(1, counter).Value <= end_date Then Cells(counter1 + 2, counter).Value = labour counter = counter + 1 End If 'add no of hours per shift Cells(counter1 + 2, (diff * 2) + 2).Select ActiveCell.Value = shift 'add total If counter1 = no_task Then Cells(counter1 + 3, 1).Value = "Histogram Total" Call histo_total(diff, no_task) Call histo_shifter(diff, no_task) Call histo_chart_build(no_task) Call s_curve_values(diff, no_task) Call s_curve_totals(diff, no_task) Call s_curve_chart_build(no_task) End If Next counter Next counter1 End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with a piece of code
I can't get the error to duplicate. But usually, the condition that causes
that message is when VBA is not sure of where it should execute the command. In other words something, at that point in your code, needs to be better defined, or qualified, so that VBA is sure of the range object it is executing on. "Joseph Atie" wrote: i know the values of the variables because the output is accurate at the end of the run after everything is complete it comes back to that piece of code and give the error it even calls all the functions at the bottom correctly. i know its all right because the end out put is 2 charts "Barb Reinhardt" wrote: Have you tried putting in some Debug.Print statements to figure out the values of some of your variables before it has an error? I'm wondering what the value of Counter might be. -- HTH, Barb Reinhardt If this post was helpful to you, please click YES below. "Joseph Atie" wrote: Im getting the error Method 'Cells' of object '_Global' failed on the following function My understanding is this is because im havent explicitly defined the sheet im working in. I have placed **ERROR ON THE LINE BELOW*** to indicate where the error is little help please. If i add another call to the sheet calc inside the loop then i get overflow errors. The weird thing is the code still runs and still produces the correct output. If you need to see the other functions this function calls just ask and ill post them as well. Sub Build_Calc() Call Filter Dim diff As Integer Dim cell As Object Dim counter As Integer Dim counter1 As Integer Dim no_task As Integer Dim start_date Dim end_date Dim start_time Dim end_time Dim shift Dim day_shift Dim night_shift Dim labour As Integer Sheets("junk").Select Range("L7").Select diff = Selection.Value diff = diff + 1 Range("L3").Select start_date = Selection.Value Sheets("Calc").Select Cells.Select Selection.ClearContents For counter = 1 To (diff * 2) Cells(1, counter).Select ActiveCell.Value = start_date Cells(2, counter).Select ActiveCell.Value = "Day" counter = counter + 1 Cells(1, counter).Select ActiveCell.Value = start_date Cells(2, counter).Select ActiveCell.Value = "Night" start_date = start_date + 1 If counter = (diff * 2) Then Cells(2, counter + 1).Select ActiveCell.Value = "Hours" End If Next counter Columns("A:A").Select Selection.Insert shift:=xlToRight Sheets("junk").Select Columns("C:C").Select Selection.SpecialCells(xlCellTypeConstants, 23).Select Selection.Copy Sheets("Calc").Select Range("A2").Select ActiveSheet.Paste Columns("A:A").EntireColumn.AutoFit Columns("B:B").EntireColumn.AutoFit Rows("1:1").EntireRow.AutoFit Sheets("junk").Select Range("l9").Select no_task = Selection.Value Range("l11").Select day_shift = Selection.Value Range("l13").Select night_shift = Selection.Value For counter1 = 1 To no_task Sheets("junk").Select Cells(counter1 + 1, 5).Select labour = ActiveCell.Value Cells(counter1 + 1, 11).Select shift = ActiveCell.Value Cells(counter1 + 1, 6).Select start_date = ActiveCell.Value Cells(counter1 + 1, 8).Select end_date = ActiveCell.Value Sheets("calc").Select For counter = 1 To (diff * 2) If shift = "24" Then ' 24 hour calendar ' day shift *** ERROR ON THE LINE BELOW*** If Cells(1, counter).Value start_date And Cells(1, counter).Value < end_date Then Cells(counter1 + 2, counter).Value = labour counter = counter + 1 ElseIf (Cells(1, counter).Value = start_date And Cells(1, counter).Value < end_date) And start_time < night_shift Then Cells(counter1 + 2, counter).Value = labour counter = counter + 1 ElseIf (Cells(1, counter).Value start_date And Cells(1, counter).Value = end_date) And end_time = day_shift Then Cells(counter1 + 2, counter).Value = labour counter = counter + 1 ElseIf (Cells(1, counter).Value = start_date And Cells(1, counter).Value = end_date) And end_time = day_shift And start_time < night_shift Then Cells(counter1 + 2, counter).Value = labour counter = counter + 1 Else counter = counter + 1 End If ' night shift If Cells(1, counter).Value start_date And Cells(1, counter).Value < end_date Then Cells(counter1 + 2, counter).Value = labour ElseIf (Cells(1, counter).Value = start_date And Cells(1, counter).Value < end_date) And start_time day_shift Then Cells(counter1 + 2, counter).Value = labour ElseIf (Cells(1, counter).Value start_date And Cells(1, counter).Value = end_date) And end_time night_shift Then Cells(counter1 + 2, counter).Value = labour ElseIf (Cells(1, counter).Value = start_date And Cells(1, counter).Value = end_date) And end_time night_shift Then Cells(counter1 + 2, counter).Value = labour ElseIf (Cells(1, counter).Value = start_date And Cells(1, counter + 1).Value = end_date) And end_time < day_shift Then Cells(counter1 + 2, counter).Value = labour End If ' 12 hour calendar ElseIf Cells(1, counter).Value = start_date And Cells(1, counter).Value <= end_date Then Cells(counter1 + 2, counter).Value = labour counter = counter + 1 End If 'add no of hours per shift Cells(counter1 + 2, (diff * 2) + 2).Select ActiveCell.Value = shift 'add total If counter1 = no_task Then Cells(counter1 + 3, 1).Value = "Histogram Total" Call histo_total(diff, no_task) Call histo_shifter(diff, no_task) Call histo_chart_build(no_task) Call s_curve_values(diff, no_task) Call s_curve_totals(diff, no_task) Call s_curve_chart_build(no_task) End If Next counter Next counter1 End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with a piece of code
Questions and comments...
'-- Did you leave out part of the code? I had to change the "ElseIf" immediately below "12 hour calendar" to "If". What is the value of the counters when the error occurs? You should change the counter variables from Integer to Long. If you can figure out what "Cells" belong to which sheet then you should qualify them with the sheet name... Sheets("calc").Cells(1, counter).Value Sheets("junk").Cells(counter1 + 1, 5) -- Jim Cone Portland, Oregon USA "Joseph Atie" wrote in message Im getting the error Method 'Cells' of object '_Global' failed on the following function My understanding is this is because im havent explicitly defined the sheet im working in. I have placed **ERROR ON THE LINE BELOW*** to indicate where the error is little help please. If i add another call to the sheet calc inside the loop then i get overflow errors. The weird thing is the code still runs and still produces the correct output. If you need to see the other functions this function calls just ask and ill post them as well. -snip- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with a piece of code
Furthermore, you are changing the value of "counter" within the loop. That is bad practice and can cause problems with the loop. -- Jim Cone Portland, Oregon USA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Interpretation of a piece of code | Excel Discussion (Misc queries) | |||
What is wrong with this vba piece of code? | Excel Discussion (Misc queries) | |||
Super piece of code..At least for me!! | Excel Programming | |||
changing a piece of code | Excel Programming | |||
Query on small piece of code | Excel Programming |