#Value error only first time round code

Blandit

New Member
Joined
Jan 7, 2005
Messages
15
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
 
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
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top