Solved

# VB Excel Code Problem

Posted on 2006-05-03

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