VB Excel Code Problem

Hello stuartc1,

----------
'this line is adding a formula to a range in the PLSheet
PLSheet.Cells(210, Cnt5 + 137).Formula = _
  "=F" & Trim(Str(Refsheet.Cells(FRows(1), 4))) & _
  "+F" & Trim(Str(Refsheet.Cells(FRows(2), 4))) & _
  "+F" & Trim(Str(Refsheet.Cells(FRows(3), 4))) & _
  "+F" & Trim(Str(Refsheet.Cells(FRows(4), 4)))
'so you need to put a breakpoint above this line and check the values
'in the cells
'Refsheet.Cells(FRows(1), 4))
'Refsheet.Cells(FRows(2), 4))
'Refsheet.Cells(FRows(3), 4))
'Refsheet.Cells(FRows(4), 4))
'somehow the values in those cells are not pointing to valid cells
----------

hope this helps a bit
bruintje

this block:

If IsEmpty(Refsheet.Cells(Cnt7, 2)) Then Cnt7 = Cnt7 - 1
If IsEmpty(Refsheet.Cells(Cnt7, 2)) Then Cnt7 = Cnt7 - 1
If IsEmpty(Refsheet.Cells(Cnt7, 2)) Then Cnt7 = Cnt7 - 1

makes no sense to me, since you are testing the same cell 3 times, in succession(so if the first test is true, then ALL of the tests will be true).  Can't  this be 'simplified' to read:

If IsEmpty(Refsheet.Cells(Cnt7, 2)) Then Cnt7 = Cnt7 - 3

AW

Thanks bruintje,

The really strange thing is - sometimes when I run it: FRows(1) = 130 and the others = 0. Other times they all = 0. Even though the code seems to assign a 0 to all.

Would the code...
FRows(1) = 0
FRows(2) = 0
FRows(3) = 0
FRows(4) = 0
FRows(5) = 0

...
Reset the current values to 0 or does this just create a value if no value currently exists?
As I say in the last run FRows(1) = 130 after this block of code!! new one to me, but I'm no vb programmer.

no because what you do in that part is lowering Cnt7 3 times

not 3 times the same Cnt7

sorry that was to ArthurWood didn't see the other post yet

in excel the error comes mostly from references failing to point to objects or cells or ranges

so i would debug that line a few times

PLSheet.Cells(210, Cnt5 + 137).Formula = _
  "=F" & Trim(Str(Refsheet.Cells(FRows(1), 4))) & _
  "+F" & Trim(Str(Refsheet.Cells(FRows(2), 4))) & _
  "+F" & Trim(Str(Refsheet.Cells(FRows(3), 4))) & _
  "+F" & Trim(Str(Refsheet.Cells(FRows(4), 4)))

it is building a formula that should come out something like

=F2+F3+F4+F5 depending what is in the FRows Array

and put this formula in the block of cells

PLSheet.Cells(210, Cnt5 + 137)

so basically copying a range to another range on another sheet

Hi Arthur,

Regarding this code:
If IsEmpty(Refsheet.Cells(Cnt7, 2)) Then Cnt7 = Cnt7 - 1
If IsEmpty(Refsheet.Cells(Cnt7, 2)) Then Cnt7 = Cnt7 - 1
If IsEmpty(Refsheet.Cells(Cnt7, 2)) Then Cnt7 = Cnt7 - 1

I agree that on first looking at this it seems strange.... but I'm thinking it may be valid, for example:

Say Cnt = 120  and that refercence is indead Empty, then it changes tha value of Cnt to = 119. And again does the check. Perhaps the script is looking for the next available value, if it just moved 3 cells, it could miss one! Just a theory though. But I do not think this part of code is causing the current problem!! but who knows.

ok, now I see what that is doing - it just looks very odd to do it that way.

on thing that makes this code extremely hard to follow is the GOTO block - which is VERY VERY programming style, and whoever did this should be taken out back behind the shed and SHOT.

AW

Goto wouldnt be nearly as useful if vba had a continue statement

stuart,

can you post the variable definition and at least the first assignment for all the variables used in this code block?

I guess the definition isnt really important, just the assignments

Also, it would really help debug if you could tell us the value of all the visible variables at the time the code breaks

The problems seems to be with the values of the FRows(?).. at the top of the code block I posted, it starts like:

FRows(1) = 0
FRows(2) = 0
FRows(3) = 0
FRows(4) = 0
FRows(5) = 0

and when it crashes (at the last line posted), the values are:

FRows(1) = 130
FRows(2) = 0
FRows(3) = 0
FRows(4) = 0
FRows(5) = 0

I dont understand why FRows(1) = 130 - I would have thought the assignments above would override this.

The only declaration is like this:

Public FRows(5) As Integer (at the start of the code)

I'm thinking that FRows is an array of Integers - seems a strange way to create an array..



If I comment out like this:

FRows(1) = 0
' FRows(2) = 0
' FRows(3) = 0
' FRows(4) = 0
FRows(5) = 0

It does not get the error - but I do not get off that easy as other errors occur.

how large is the sub where this code is in? too large to post? debugging this in parts is very difficult maybe if we see the sub it would be easier.

variables like FRows and Cnt1 - Cnt7 can be very hard to read at this point :)

oh man that is irritating.  I just wrote out my response and sometime when i was typing i lost my internet connection.  When I clicked submit EE lost all i wrote.

and apparently bruintje posted while i was typing my message (it is late too!).

Basically the problem is caused by the zeros.  FRows(1) is assigned a variable based on what is in cnt3, cnt4, and cnt7 so you need to tell us what is in those variables in order to figure out why they are getting zeros.

It looks like this bit of code is trying to add up 4 non-empty cells in column F (of the Spring worksheet).  The Frows array holds (or should hold) the row numbers to sum in column F.

I am guessing that the cause of the zeros is that cnt6 is starting out at 1 (when it should start with at least 4).

Another possible cause is that column F of the Spring worksheet is completely empty up to row 130.

if you cant find the error and fix it with this info, please post any code used to create cnt3, cnt4, and cnt7.

Good luck!

Actually now that i think about it, i dont think that we would see
FRows(1) = 130
FRows(2) = 0
FRows(3) = 0
FRows(4) = 0
FRows(5) = 0
if F was emtpy.  So the cause of the problem is that cnt6 is starting out with 1 (meaning Cnt3 - Cnt4 + 1 = 1)

Here is the full Sub (I've removed a few irrelavant lines as indicated):

Sub PlanAmend3()
ThisWeek = CurrWeek + 151 + NextLeap
For Cnt = 210 To 1 Step -1
If ThisWeek = Refsheet.Cells(Cnt, 2) Then Exit For
Next
Cnt1 = Cnt
Cnt2 = 0
For Cnt = Cnt1 To 1 Step -1
If IsEmpty(Refsheet.Cells(Cnt, 2)) = False Then Cnt2 = Cnt2 + 1
If Cnt2 = 53 Then Exit For
Next
Cnt7 = Cnt
Cnt2 = 13
Cnt3 = Cnt1
Cnt4 = Cnt1
For Cnt = Cnt1 To 1 Step -1
If Cnt2 = 0 Then Exit For
If IsEmpty(Refsheet.Cells(Cnt, 2)) And Cnt4 = 0 Then GoTo IgnoreIt
If IsEmpty(Refsheet.Cells(Cnt, 2)) And Cnt4 <> 0 Then
Cnt5 = Cnt2
If Cnt2 > 7 Then Cnt5 = Cnt5 + 1
Set PLSheet = PL.Sheets("Total")
PLSheet.Cells(241, Cnt5 + 137) = "'" & Right("00" & Trim(Str(Refsheet.Cells(Cnt4, 2) Mod 100)), 2) & "-" & Right("00" & Trim(Str(Refsheet.Cells(Cnt3, 2) Mod 100)), 2)
' // MORE LINES SIMILAR TO ABOVE
Set PLSheet = PL.Sheets("Spring")
PLSheet.Cells(241, Cnt5 + 137) = "'" & Right("00" & Trim(Str(Refsheet.Cells(Cnt4, 2) Mod 100)), 2) & "-" & Right("00" & Trim(Str(Refsheet.Cells(Cnt3, 2) Mod 100)), 2)
' // MORE LINES SIMILAR TO ABOVE
Set PLSheet = PL.Sheets("Autumn")
PLSheet.Cells(241, Cnt5 + 137) = "'" & Right("00" & Trim(Str(Refsheet.Cells(Cnt4, 2) Mod 100)), 2) & "-" & Right("00" & Trim(Str(Refsheet.Cells(Cnt3, 2) Mod 100)), 2)
' // MORE LINES SIMILAR TO ABOVE
Set PLSheet = PL.Sheets("Basics")
PLSheet.Cells(241, Cnt5 + 137) = "'" & Right("00" & Trim(Str(Refsheet.Cells(Cnt4, 2) Mod 100)), 2) & "-" & Right("00" & Trim(Str(Refsheet.Cells(Cnt3, 2) Mod 100)), 2)
' // MORE LINES SIMILAR TO ABOVE
Set PLSheet = PL.Sheets("FullP")
PLSheet.Cells(241, Cnt5 + 137) = "'" & Right("00" & Trim(Str(Refsheet.Cells(Cnt4, 2) Mod 100)), 2) & "-" & Right("00" & Trim(Str(Refsheet.Cells(Cnt3, 2) Mod 100)), 2)
' // MORE LINES SIMILAR TO ABOVE
Set PLSheet = PL.Sheets("Code2P")
PLSheet.Cells(241, Cnt5 + 137) = "'" & Right("00" & Trim(Str(Refsheet.Cells(Cnt4, 2) Mod 100)), 2) & "-" & Right("00" & Trim(Str(Refsheet.Cells(Cnt3, 2) Mod 100)), 2)
' // MORE LINES SIMILAR TO ABOVE
Set PLSheet = PL.Sheets("Code2R")
PLSheet.Cells(241, Cnt5 + 137) = "'" & Right("00" & Trim(Str(Refsheet.Cells(Cnt4, 2) Mod 100)), 2) & "-" & Right("00" & Trim(Str(Refsheet.Cells(Cnt3, 2) Mod 100)), 2)
' // MORE LINES SIMILAR TO ABOVE
Set PLSheet = PL.Sheets("Code34")
PLSheet.Cells(241, Cnt5 + 137) = "'" & Right("00" & Trim(Str(Refsheet.Cells(Cnt4, 2) Mod 100)), 2) & "-" & Right("00" & Trim(Str(Refsheet.Cells(Cnt3, 2) Mod 100)), 2)
' // MORE LINES SIMILAR TO ABOVE
Set PLSheet = PL.Sheets("TotalMD")
PLSheet.Cells(241, Cnt5 + 137) = "'" & Right("00" & Trim(Str(Refsheet.Cells(Cnt4, 2) Mod 100)), 2) & "-" & Right("00" & Trim(Str(Refsheet.Cells(Cnt3, 2) Mod 100)), 2)
' // MORE LINES SIMILAR TO ABOVE

Cnt6 = Cnt3 - Cnt4 + 1

FRows(1) = 0
FRows(2) = 0
FRows(3) = 0
FRows(4) = 0
FRows(5) = 0

FRows(Cnt6) = Cnt7
NextWeek:
Cnt7 = Cnt7 - 1
If IsEmpty(Refsheet.Cells(Cnt7, 2)) Then GoTo NextWeek
Cnt6 = Cnt6 - 1
FRows(Cnt6) = Cnt7
If Cnt6 > 1 Then GoTo NextWeek
Cnt7 = Cnt7 - 1
If IsEmpty(Refsheet.Cells(Cnt7, 2)) Then Cnt7 = Cnt7 - 1
If IsEmpty(Refsheet.Cells(Cnt7, 2)) Then Cnt7 = Cnt7 - 1
If IsEmpty(Refsheet.Cells(Cnt7, 2)) Then Cnt7 = Cnt7 - 1
If FRows(5) = 0 Then

Set PLSheet = PL.Sheets("Spring")
+++++++++++++++++++++++++++++++++++
+ ERROR LINE BELOW @ Trim(Str(Refsheet.C....
+++++++++++++++++++++++++++++++++++
PLSheet.Cells(210, Cnt5 + 137).Formula = "=F" & Trim(Str(Refsheet.Cells(FRows(1), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(2), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(3), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(4), 4)))
PLSheet.Cells(212, Cnt5 + 137).Formula = "=BG" & Trim(Str(Refsheet.Cells(FRows(1), 4))) & "+BG" & Trim(Str(Refsheet.Cells(FRows(2), 4))) & "+BG" & Trim(Str(Refsheet.Cells(FRows(3), 4))) & "+BG" & Trim(Str(Refsheet.Cells(FRows(4), 4)))
PLSheet.Cells(216, Cnt5 + 137).Formula = "=B" & Trim(Str(Refsheet.Cells(FRows(1), 4)))
PLSheet.Cells(218, Cnt5 + 137).Formula = "=BF" & Trim(Str(Refsheet.Cells(FRows(1), 4)))
PLSheet.Cells(228, Cnt5 + 137).Formula = "=P" & Trim(Str(Refsheet.Cells(FRows(1), 4))) & "+P" & Trim(Str(Refsheet.Cells(FRows(2), 4))) & "+P" & Trim(Str(Refsheet.Cells(FRows(3), 4))) & "+P" & Trim(Str(Refsheet.Cells(FRows(4), 4)))
PLSheet.Cells(230, Cnt5 + 137).Formula = "=BH" & Trim(Str(Refsheet.Cells(FRows(1), 4))) & "+BH" & Trim(Str(Refsheet.Cells(FRows(2), 4))) & "+BH" & Trim(Str(Refsheet.Cells(FRows(3), 4))) & "+BH" & Trim(Str(Refsheet.Cells(FRows(4), 4)))
Set PLSheet = PL.Sheets("Autumn")
PLSheet.Cells(210, Cnt5 + 137).Formula = "=F" & Trim(Str(Refsheet.Cells(FRows(1), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(2), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(3), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(4), 4)))
' // MORE LINES SIMILAR TO ABOVE
Set PLSheet = PL.Sheets("Basics")
PLSheet.Cells(210, Cnt5 + 137).Formula = "=F" & Trim(Str(Refsheet.Cells(FRows(1), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(2), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(3), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(4), 4)))
' // MORE LINES SIMILAR TO ABOVE
Set PLSheet = PL.Sheets("FullP")
PLSheet.Cells(210, Cnt5 + 137).Formula = "=F" & Trim(Str(Refsheet.Cells(FRows(1), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(2), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(3), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(4), 4)))
' // MORE LINES SIMILAR TO ABOVE
Set PLSheet = PL.Sheets("Code2P")
PLSheet.Cells(210, Cnt5 + 137).Formula = "=F" & Trim(Str(Refsheet.Cells(FRows(1), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(2), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(3), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(4), 4)))
' // MORE LINES SIMILAR TO ABOVE
Set PLSheet = PL.Sheets("Code2R")
PLSheet.Cells(210, Cnt5 + 137).Formula = "=F" & Trim(Str(Refsheet.Cells(FRows(1), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(2), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(3), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(4), 4)))
' // MORE LINES SIMILAR TO ABOVE
Set PLSheet = PL.Sheets("Code34")
PLSheet.Cells(210, Cnt5 + 137).Formula = "=F" & Trim(Str(Refsheet.Cells(FRows(1), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(2), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(3), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(4), 4)))
' // MORE LINES SIMILAR TO ABOVE
Else
Set PLSheet = PL.Sheets("Spring")
PLSheet.Cells(210, Cnt5 + 137).Formula = "=F" & Trim(Str(Refsheet.Cells(FRows(1), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(2), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(3), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(4), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(5), 4)))
' // MORE LINES SIMILAR TO ABOVE
Set PLSheet = PL.Sheets("Autumn")
PLSheet.Cells(210, Cnt5 + 137).Formula = "=F" & Trim(Str(Refsheet.Cells(FRows(1), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(2), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(3), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(4), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(5), 4)))
' // MORE LINES SIMILAR TO ABOVE
Set PLSheet = PL.Sheets("Basics")
PLSheet.Cells(210, Cnt5 + 137).Formula = "=F" & Trim(Str(Refsheet.Cells(FRows(1), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(2), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(3), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(4), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(5), 4)))
' // MORE LINES SIMILAR TO ABOVE
Set PLSheet = PL.Sheets("FullP")
PLSheet.Cells(210, Cnt5 + 137).Formula = "=F" & Trim(Str(Refsheet.Cells(FRows(1), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(2), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(3), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(4), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(5), 4)))
' // MORE LINES SIMILAR TO ABOVE
Set PLSheet = PL.Sheets("Code2P")
PLSheet.Cells(210, Cnt5 + 137).Formula = "=F" & Trim(Str(Refsheet.Cells(FRows(1), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(2), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(3), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(4), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(5), 4)))
' // MORE LINES SIMILAR TO ABOVE
Set PLSheet = PL.Sheets("Code2R")
PLSheet.Cells(210, Cnt5 + 137).Formula = "=F" & Trim(Str(Refsheet.Cells(FRows(1), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(2), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(3), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(4), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(5), 4)))
' // MORE LINES SIMILAR TO ABOVE
Set PLSheet = PL.Sheets("Code34")
PLSheet.Cells(210, Cnt5 + 137).Formula = "=F" & Trim(Str(Refsheet.Cells(FRows(1), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(2), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(3), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(4), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(5), 4)))
' // MORE LINES SIMILAR TO ABOVE

End If
If FRows(5) = 0 Then
Set PLSheet = PL.Sheets("Total")
PLSheet.Cells(203, Cnt5 + 137) = "'" & Right("00" & Trim(Str(Refsheet.Cells(FRows(1), 2) Mod 100)), 2) & "-" & Right("00" & Trim(Str(Refsheet.Cells(FRows(4), 2) Mod 100)), 2)
Set PLSheet = PL.Sheets("Spring")
PLSheet.Cells(203, Cnt5 + 137) = "'" & Right("00" & Trim(Str(Refsheet.Cells(FRows(1), 2) Mod 100)), 2) & "-" & Right("00" & Trim(Str(Refsheet.Cells(FRows(4), 2) Mod 100)), 2)
Set PLSheet = PL.Sheets("Autumn")
PLSheet.Cells(203, Cnt5 + 137) = "'" & Right("00" & Trim(Str(Refsheet.Cells(FRows(1), 2) Mod 100)), 2) & "-" & Right("00" & Trim(Str(Refsheet.Cells(FRows(4), 2) Mod 100)), 2)
Set PLSheet = PL.Sheets("Basics")
PLSheet.Cells(203, Cnt5 + 137) = "'" & Right("00" & Trim(Str(Refsheet.Cells(FRows(1), 2) Mod 100)), 2) & "-" & Right("00" & Trim(Str(Refsheet.Cells(FRows(4), 2) Mod 100)), 2)
Set PLSheet = PL.Sheets("FullP")
PLSheet.Cells(203, Cnt5 + 137) = "'" & Right("00" & Trim(Str(Refsheet.Cells(FRows(1), 2) Mod 100)), 2) & "-" & Right("00" & Trim(Str(Refsheet.Cells(FRows(4), 2) Mod 100)), 2)
Set PLSheet = PL.Sheets("Code2P")
PLSheet.Cells(203, Cnt5 + 137) = "'" & Right("00" & Trim(Str(Refsheet.Cells(FRows(1), 2) Mod 100)), 2) & "-" & Right("00" & Trim(Str(Refsheet.Cells(FRows(4), 2) Mod 100)), 2)
Set PLSheet = PL.Sheets("Code2R")
PLSheet.Cells(203, Cnt5 + 137) = "'" & Right("00" & Trim(Str(Refsheet.Cells(FRows(1), 2) Mod 100)), 2) & "-" & Right("00" & Trim(Str(Refsheet.Cells(FRows(4), 2) Mod 100)), 2)
Set PLSheet = PL.Sheets("Code34")
PLSheet.Cells(203, Cnt5 + 137) = "'" & Right("00" & Trim(Str(Refsheet.Cells(FRows(1), 2) Mod 100)), 2) & "-" & Right("00" & Trim(Str(Refsheet.Cells(FRows(4), 2) Mod 100)), 2)
Set PLSheet = PL.Sheets("TotalMD")
PLSheet.Cells(203, Cnt5 + 137) = "'" & Right("00" & Trim(Str(Refsheet.Cells(FRows(1), 2) Mod 100)), 2) & "-" & Right("00" & Trim(Str(Refsheet.Cells(FRows(4), 2) Mod 100)), 2)
Else
Set PLSheet = PL.Sheets("Total")
PLSheet.Cells(203, Cnt5 + 137) = "'" & Right("00" & Trim(Str(Refsheet.Cells(FRows(1), 2) Mod 100)), 2) & "-" & Right("00" & Trim(Str(Refsheet.Cells(FRows(5), 2) Mod 100)), 2)
Set PLSheet = PL.Sheets("Spring")
PLSheet.Cells(203, Cnt5 + 137) = "'" & Right("00" & Trim(Str(Refsheet.Cells(FRows(1), 2) Mod 100)), 2) & "-" & Right("00" & Trim(Str(Refsheet.Cells(FRows(5), 2) Mod 100)), 2)
Set PLSheet = PL.Sheets("Autumn")
PLSheet.Cells(203, Cnt5 + 137) = "'" & Right("00" & Trim(Str(Refsheet.Cells(FRows(1), 2) Mod 100)), 2) & "-" & Right("00" & Trim(Str(Refsheet.Cells(FRows(5), 2) Mod 100)), 2)
Set PLSheet = PL.Sheets("Basics")
PLSheet.Cells(203, Cnt5 + 137) = "'" & Right("00" & Trim(Str(Refsheet.Cells(FRows(1), 2) Mod 100)), 2) & "-" & Right("00" & Trim(Str(Refsheet.Cells(FRows(5), 2) Mod 100)), 2)
Set PLSheet = PL.Sheets("FullP")
PLSheet.Cells(203, Cnt5 + 137) = "'" & Right("00" & Trim(Str(Refsheet.Cells(FRows(1), 2) Mod 100)), 2) & "-" & Right("00" & Trim(Str(Refsheet.Cells(FRows(5), 2) Mod 100)), 2)
Set PLSheet = PL.Sheets("Code2P")
PLSheet.Cells(203, Cnt5 + 137) = "'" & Right("00" & Trim(Str(Refsheet.Cells(FRows(1), 2) Mod 100)), 2) & "-" & Right("00" & Trim(Str(Refsheet.Cells(FRows(5), 2) Mod 100)), 2)
Set PLSheet = PL.Sheets("Code2R")
PLSheet.Cells(203, Cnt5 + 137) = "'" & Right("00" & Trim(Str(Refsheet.Cells(FRows(1), 2) Mod 100)), 2) & "-" & Right("00" & Trim(Str(Refsheet.Cells(FRows(5), 2) Mod 100)), 2)
Set PLSheet = PL.Sheets("Code34")
PLSheet.Cells(203, Cnt5 + 137) = "'" & Right("00" & Trim(Str(Refsheet.Cells(FRows(1), 2) Mod 100)), 2) & "-" & Right("00" & Trim(Str(Refsheet.Cells(FRows(5), 2) Mod 100)), 2)
Set PLSheet = PL.Sheets("TotalMD")
PLSheet.Cells(203, Cnt5 + 137) = "'" & Right("00" & Trim(Str(Refsheet.Cells(FRows(1), 2) Mod 100)), 2) & "-" & Right("00" & Trim(Str(Refsheet.Cells(FRows(5), 2) Mod 100)), 2)
End If
Cnt4 = 0
Cnt3 = 0
Cnt2 = Cnt2 - 1
GoTo IgnoreIt
End If
If Cnt3 = 0 Then Cnt3 = Cnt
Cnt4 = Cnt
IgnoreIt:
Next
Set PLSheet = PL.Sheets("Total")
PLSheet.Cells(241, 145) = "'" & Left(PLSheet.Cells(241, 138), 3) & Right(PLSheet.Cells(241, 144), 2)
' // MORE LINES SIMILAR TO ABOVE
Set PLSheet = PL.Sheets("Spring")
PLSheet.Cells(241, 145) = "'" & Left(PLSheet.Cells(241, 138), 3) & Right(PLSheet.Cells(241, 144), 2)
' // MORE LINES SIMILAR TO ABOVE
Set PLSheet = PL.Sheets("Autumn")
PLSheet.Cells(241, 145) = "'" & Left(PLSheet.Cells(241, 138), 3) & Right(PLSheet.Cells(241, 144), 2)
' // MORE LINES SIMILAR TO ABOVE
Set PLSheet = PL.Sheets("Basics")
PLSheet.Cells(241, 145) = "'" & Left(PLSheet.Cells(241, 138), 3) & Right(PLSheet.Cells(241, 144), 2)
' // MORE LINES SIMILAR TO ABOVE
Set PLSheet = PL.Sheets("FullP")
PLSheet.Cells(241, 145) = "'" & Left(PLSheet.Cells(241, 138), 3) & Right(PLSheet.Cells(241, 144), 2)
' // MORE LINES SIMILAR TO ABOVE
Set PLSheet = PL.Sheets("code2P")
PLSheet.Cells(241, 145) = "'" & Left(PLSheet.Cells(241, 138), 3) & Right(PLSheet.Cells(241, 144), 2)
' // MORE LINES SIMILAR TO ABOVE
Set PLSheet = PL.Sheets("Code2R")
PLSheet.Cells(241, 145) = "'" & Left(PLSheet.Cells(241, 138), 3) & Right(PLSheet.Cells(241, 144), 2)
' // MORE LINES SIMILAR TO ABOVE
Set PLSheet = PL.Sheets("Code34")
PLSheet.Cells(241, 145) = "'" & Left(PLSheet.Cells(241, 138), 3) & Right(PLSheet.Cells(241, 144), 2)
' // MORE LINES SIMILAR TO ABOVE
Set PLSheet = PL.Sheets("TotalMD")
PLSheet.Cells(241, 145) = "'" & Left(PLSheet.Cells(241, 138), 3) & Right(PLSheet.Cells(241, 144), 2)
' // MORE LINES SIMILAR TO ABOVE

End Sub

What is Cnt when it fails?

OK, here are some values when it breaks:

Cnt3 = 209
Cnt4 = 209
Cnt6 = 0
Cnt7 = 126

Cnt = 208
Cnt1 = 209
Cnt2 = 13
Cnt5 = 14

try changing the line that says "Cnt4 = Cnt1" to say "Cnt4 = Cnt1 - 5"

Does this give you the results you are looking for?

if FRows(2) = 0 ir any of the FRows used in the lines following this will fail like this one

PLSheet.Cells(210, Cnt5 + 137).Formula = "=F" & Trim(Str(Refsheet.Cells(FRows(1), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(2), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(3), 4))) & "+F" & Trim(Str(Refsheet.Cells(FRows(4), 4)))

because if FRows(2) = 0 then then part that fails is

Trim(Str(Refsheet.Cells(FRows(2), 4)))

a sheet doesn't start with a row count of 0 but always at 1

is refsheet pointing to a range or a sheet?

refsheet is pointing to a sheet called "Spring"

thanks

bruintye:
Set Refsheet = ActiveWorkbook.Sheets(1)
Dave:
I'll try your suggestion

nevermind, I didnt read that very well bruintje

I was thinking PLSheet

i guess we dont know what refsheet is (must a global set elsewhere).

I've tried changing "Cnt4 = Cnt1" to say "Cnt4 = Cnt1 - 5" and get the exact same problem.

Refsheet:

Workbooks.Open Left(wp.FullName, Len(wp.FullName) - 11) & Trim(TheType) & "Creation\AddinTemp.xla", , True
Set Ref = ActiveWorkbook
Set Refsheet = ActiveWorkbook.Sheets(1)

Set in a previous Sub

when it fails after the change what are
Cnt, Cnt1, Cnt2, Cnt5, Cnt3, Cnt4, Cnt6, Cnt7?

and FRows indexes 1 - 5

oh how much easier this would be if we could just chat

if you want to IM me, my yahoo id is the same as my EE name

Cnt = 208
Cnt1 = 209
Cnt2 = 13
Cnt3 = 209
Cnt4 = 209
Cnt5 = 14
Cnt6 = 0
Cnt7 = 126

FRows(1) = 130
FRows(2) = 0
FRows(3) = 0
FRows(4) = 0
FRows(5) = 0
FRows(Cnt6) = 127

Im at work right now and they have banned Messaging systems :(

thats too bad about the IM.  If you want, you can mail me the file (zipped up) to dbb4 at dana.ucc.nau.edu

its a little odd that Cnt4 still = 209 when (since you changed "Cnt4 = Cnt1" to say "Cnt4 = Cnt1 - 5") it should equal 204.  Are you sure that you made the change i suggested (make sure that you didnt change "Cnt4 = Cnt" to "Cnt4 = Cnt - 5").

I did make the change correctly - you are right it is all very strange, its almost like it is ignoring the assignments to change values. I've noticed this with many of the other parts of the Sub.

When this program runs, it runs 2 other very similar Subs before this one - many of the same variables are used and they do not crash (there is even a block almost identical to above which does not complain).

Could it be possible that excel is not closing the objects from previous Subs correctly, (locked due to timeing problems).

Is there a way for me to force these variables to be unset, then reset them? I may be clutching a straws here though.

Thanks for all your help dave - I'll do the debugging in the VB environment and hopefully I'll find something. Thanks again.

good luck, seems like you you need ;-)

but it ran a few years without a problem so if nothing changed it should be in the logic when it was written something like the leap variable in the beginning of the sub or something else

yes, it deals with company weeks that have 53 some years - another part of the code removes lines from the sheet (which is also causing problems) - so I guess there is a clue with that.
Thanks for your help bruintje

no problem Stuart, good luck!