?
Solved

VB Excel Code Problem

Posted on 2006-05-03
39
Medium Priority
?
265 Views
Last Modified: 2010-04-17
Hi,

I have the job of fixing a huge excel program which is failing after working successfully for 3 or 4 years (runs once per year).

I cant post all the code as this would be crazy - here is the part that is failing:

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")
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)))

On the last line (PLSheet......) it fails on the refsheet.Cells(etc...) with the error 'aplication-defined or object-defined error'


So I cannot give more details, as you can see I'm totally lost with this and have no idea what to do. I've commented out the FRows(1) parts which gets past the hurdle, but this make the results very strange!!

Any ideas? suggestions??
0
Comment
Question by:stuartc1
  • 15
  • 14
  • 8
  • +1
39 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 16595096
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
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 16595193
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


0
 
LVL 2

Author Comment

by:stuartc1
ID: 16595214
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 44

Expert Comment

by:bruintje
ID: 16595216
no because what you do in that part is lowering Cnt7 3 times

not 3 times the same Cnt7
0
 
LVL 44

Expert Comment

by:bruintje
ID: 16595220
sorry that was to ArthurWood didn't see the other post yet
0
 
LVL 44

Expert Comment

by:bruintje
ID: 16595239
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
0
 
LVL 2

Author Comment

by:stuartc1
ID: 16595242
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.
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 16596868
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
0
 
LVL 15

Expert Comment

by:dave4dl
ID: 16598119
Goto wouldnt be nearly as useful if vba had a continue statement
0
 
LVL 15

Expert Comment

by:dave4dl
ID: 16598131
stuart,

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

Expert Comment

by:dave4dl
ID: 16598141
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
0
 
LVL 2

Author Comment

by:stuartc1
ID: 16603569

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.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 16603590
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 :)
0
 
LVL 15

Expert Comment

by:dave4dl
ID: 16603677
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!
0
 
LVL 15

Expert Comment

by:dave4dl
ID: 16603687
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)
0
 
LVL 2

Author Comment

by:stuartc1
ID: 16603696
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
0
 
LVL 15

Expert Comment

by:dave4dl
ID: 16603704
What is Cnt when it fails?
0
 
LVL 2

Author Comment

by:stuartc1
ID: 16603738
OK, here are some values when it breaks:

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

Author Comment

by:stuartc1
ID: 16603799
Cnt = 208
Cnt1 = 209
Cnt2 = 13
Cnt5 = 14
0
 
LVL 15

Expert Comment

by:dave4dl
ID: 16603832
try changing the line that says "Cnt4 = Cnt1" to say "Cnt4 = Cnt1 - 5"

Does this give you the results you are looking for?
0
 
LVL 44

Expert Comment

by:bruintje
ID: 16603836
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?
0
 
LVL 15

Expert Comment

by:dave4dl
ID: 16603842
refsheet is pointing to a sheet called "Spring"
0
 
LVL 44

Expert Comment

by:bruintje
ID: 16603846
thanks
0
 
LVL 2

Author Comment

by:stuartc1
ID: 16603851
bruintye:
Set Refsheet = ActiveWorkbook.Sheets(1)
Dave:
I'll try your suggestion
0
 
LVL 15

Expert Comment

by:dave4dl
ID: 16603861
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).
0
 
LVL 2

Author Comment

by:stuartc1
ID: 16603877
I've tried changing "Cnt4 = Cnt1" to say "Cnt4 = Cnt1 - 5" and get the exact same problem.
0
 
LVL 2

Author Comment

by:stuartc1
ID: 16603888
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
0
 
LVL 15

Expert Comment

by:dave4dl
ID: 16603891
when it fails after the change what are
Cnt, Cnt1, Cnt2, Cnt5, Cnt3, Cnt4, Cnt6, Cnt7?

and FRows indexes 1 - 5
0
 
LVL 15

Expert Comment

by:dave4dl
ID: 16603898
oh how much easier this would be if we could just chat
0
 
LVL 15

Expert Comment

by:dave4dl
ID: 16603926
if you want to IM me, my yahoo id is the same as my EE name
0
 
LVL 2

Author Comment

by:stuartc1
ID: 16603941
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
0
 
LVL 2

Author Comment

by:stuartc1
ID: 16603944
Im at work right now and they have banned Messaging systems :(
0
 
LVL 15

Expert Comment

by:dave4dl
ID: 16603981
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").
0
 
LVL 2

Author Comment

by:stuartc1
ID: 16604023
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.
0
 
LVL 15

Accepted Solution

by:
dave4dl earned 2000 total points
ID: 16604050
It's not possible that the objects are not being closed from previous subs (and it is causing us problems).  Even if the variables were declared globally, they would get reassigned with the assignment statements in the sub you posted (and if for some strange reason you couldnt assign them values, there would be an error indicating that).  I suggest stepping through the application one line at a time checking the state of all the variables at each line by setting up some "watches" in the Visual Basic environment.  That way you can see the program flow (such as how many times it is looping) and when variables are being changed.  Step through the program by using breaks (F9) and the step over command (Shift+F8), the step into command (F8), the step out command (Shift+Ctrl+F8), and the run to cursor command (Ctrl + F8).
0
 
LVL 2

Author Comment

by:stuartc1
ID: 16604084
Thanks for all your help dave - I'll do the debugging in the VB environment and hopefully I'll find something. Thanks again.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 16604110
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
0
 
LVL 2

Author Comment

by:stuartc1
ID: 16604144
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
0
 
LVL 15

Expert Comment

by:dave4dl
ID: 16604164
no problem Stuart, good luck!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
When you discover the power of the R programming language, you are going to wonder how you ever lived without it! Learn why the language merits a place in your programming arsenal.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Screencast - Getting to Know the Pipeline

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question