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