#Value error only first time round code

[Blandit]

ok

this is a weird one for me, hopefully someone can help

i'm running the two subs below one after the other.
they are working on a column if indetrminate size to calculate the number of working days between 2 days

Teh first sub planned calculates the networkdays between columns B and C and populates column AA with integers.

i get an error (as bold below in sub planned_date below) only on the first pass through the list. i get #Values on the first 8 cells (incidently cell 9 = -192)

if i re-run the sub below, it works it out ok

so why does it fail first time round?

sorry for the messy code, i'm still learning...and needed to split things up. i promise i'll tidy it up later (lots of duplicate and select statements)
Sub planned()
Dim rng1 As Range, rng2 As Range, Holidays As Range
'Call planned_date
Worksheets("Filtered").Activate

Dim xpos As Integer
Dim ypos As Integer
Dim ypos2 As Integer
Dim offset As Integer
Dim cell As Range
Dim i As Integer


Range("Filtered!AA2:AA1000").NumberFormat = "########"

Range("Filtered!B2:B1000").NumberFormat = "dd/mm/yyyy"
Selection.HorizontalAlignment = xlLeft

Range("C2:C1000").Select
Range("Filtered!C2:C1000").NumberFormat = "dd/mm/yyyy"
Selection.HorizontalAlignment = xlLeft


For Each cell In Range("B2:C1000")
If Len(cell) > 10 Then
Call Format_Datac
End If
Next cell

xpos = 2
ypos = 2
ypos2 = 3
offset = ypos + 25
ThisWorkbook.Worksheets("Filtered").Cells(xpos, ypos).Select
Do Until IsEmpty(ActiveCell) 'loop until activecell is empty
ThisWorkbook.Worksheets("Filtered").Cells(xpos, offset).Value = NETWORKDAYS(ThisWorkbook.Worksheets("Filtered").Cells(xpos, ypos).Value, ThisWorkbook.Worksheets("Filtered").Cells(xpos, ypos2).Value) 'insert into 13 rows across NETWORKDAYS value
xpos = xpos + 1
ThisWorkbook.Worksheets("Filtered").Cells(xpos, ypos).Select
Loop

'Call fillplanned
Call planned_date



Sub planned_date()
Dim days As Integer
Range("filtered!AA2").Select
Range("Filtered!AA2:AA1000").NumberFormat = "########"
Do Until ActiveCell.Value = "" errors here with activecell.value = Error2015

If ActiveCell.Value > 2 Then
ActiveCell.offset(0, -11).Value = "yes"
Else
ActiveCell.offset(0, -11).Value = "no"
End If
Selection.offset(1, 0).Select
Loop
'Use a select case statement if it gets more complex
End Sub

 

[erik.van.geit]

Blandit,

It's so difficult to see from here what's happening on your worksheet.

close every window within the VBAWindow except the modulewindow with your code
drag your VBAwindow to the right of your screen so you can see most of your Excelscreen
click within the code
pass through your code hitting F8 line after line
see what it is doing on your sheet
probably the ActiveCell has got erroring contents itself
(although I can't reproduce your error 2015: what's written next to it ?)

do you find something this way ?

regards,
Erik