help with a piece of code

J

Joseph Atie

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
 
B

Barb Reinhardt

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.
 
J

Joseph Atie

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
 
J

JLGWhiz

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.
 
J

Jim Cone

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-
 
J

Jim Cone

Furthermore, you are changing the value of "counter" within the loop.
That is bad practice and can cause problems with the loop.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0
Counter stops working 4
append multiple worksheets data into a single multi-dimensional array 4
Do Until or For Loop 3
MACRO RUN random percentage and copy of column values 1
Automatically selecting active chart 4
sub for calculating distance via post code / variable range. 7
Vlookup error 3

Top