hobbiton73
08-01-2014, 05:32 AM
Hi, I wonder whether someone may be able to help me please.
On a given sheet I have a static data set which starts at "B9" and has a dynamic number of rows.
I then use the following code to create and extract data into a second, and if i can get this to work, a third data set.
Sub CTOForecastsExtract()
Dim AFTE As Single
Dim BlnProjExists As Boolean
Dim ColDates As New Collection
Dim CTOF As Worksheet
Dim Flex As String
Dim i As Long
Dim j As Long
Dim JRole As String
Dim LastRow As Long
Dim m As Long
Dim PDate As Date
Dim PLOB As String
Dim Portfolio As String
Dim Project As String
Dim RLOB As String
Dim RngDates As Range
Dim Task As String
Dim ws As Worksheet
Application.ScreenUpdating = False
Const StartRow As Long = 8
Set CTOF = Sheets("All CTO Forecasts Data")
For i = 3 To CTOF.Cells(StartRow - 1, Columns.Count).End(xlToLeft).Column
m = m + 1
ColDates.Add m, CTOF.Cells(StartRow - 1, i).Text
Next i
With Sheets("All Data").Range("H7")
For i = 1 To .CurrentRegion.Rows.Count - 1
Portfolio = .Offset(i, -6) ' Column B
PLOB = .Offset(i, -5) ' Column C
RLOB = .Offset(i, -4) ' Column D
JRole = .Offset(i, -2) ' Column F
Project = .Offset(i, 0) ' Column H
Task = .Offset(i, 4) ' Column L
PDate = .Offset(i, 5) ' Column M
AFTE = .Offset(i, 9) ' Column Q
Flex = .Offset(i, 10) ' Column R
If Portfolio <> "" And InStr(.Offset(i, -4), "Consultancy & Requirements") + _
InStr(.Offset(i, -4), "Strategy & Architecture") > 0 And _
InStr(.Offset(i, -2), "Consultancy & Innovation") = 0 And _
InStr(.Offset(i, 0), "TM - DIR") > 0 And AFTE > 0 And Flex = "Yes" _
And .Offset(i, 5).Value >= Application.Min(CTOF.Rows(7)) Then
Portfolio = .Offset(i, -6)
PLOB = .Offset(i, -5)
RLOB = .Offset(i, -4)
JRole = .Offset(i, -2)
Task = .Offset(i, 4)
With CTOF.Range("B9").End(xlUp).Offset(4, 0)
If .CurrentRegion.Rows.Count = 1 Then
.Offset(1, 0) = Portfolio
j = 1
Else
BlnProjExists = False
For j = 1 To .CurrentRegion.Rows.Count - 1
If .Offset(j, 0) = Portfolio Then
BlnProjExists = True
Exit For
End If
Next j
If BlnProjExists = False Then
.Offset(j, 0) = Portfolio
End If
End If
On Error Resume Next
m = ColDates(Format(PDate, "mmm yy"))
If Err = 0 Then .Offset(j, m) = .Offset(j, m) + AFTE
On Error GoTo 0
End With
End If
If Portfolio <> "" And InStr(.Offset(i, -4), "Consultancy & Requirements") + _
InStr(.Offset(i, -4), "Strategy & Architecture") > 0 And _
InStr(.Offset(i, -2), "Consultancy & Innovation") = 0 And _
InStr(.Offset(i, 0), "Enhancements") > 0 And AFTE > 0 And Flex = "Yes" _
And .Offset(i, 5).Value >= Application.Min(CTOF.Rows(7)) Then
Portfolio = .Offset(i, -6)
PLOB = .Offset(i, -5)
RLOB = .Offset(i, -4)
JRole = .Offset(i, -2)
Task = .Offset(i, 4)
With CTOF.Range("B9").End(xlUp).Offset(4, 0).End(xlUp).Offset(4, 0)
If .CurrentRegion.Rows.Count = 1 Then
.Offset(1, 0) = Portfolio
j = 1
Else
BlnProjExists = False
For j = 1 To .CurrentRegion.Rows.Count - 1
If .Offset(j, 0) = Portfolio Then
BlnProjExists = True
Exit For
End If
Next j
If BlnProjExists = False Then
.Offset(j, 0) = Portfolio
End If
End If
On Error Resume Next
m = ColDates(Format(PDate, "mmm yy"))
If Err = 0 Then .Offset(j, m) = .Offset(j, m) + AFTE
On Error GoTo 0
End With
End If
As you can see at the following line, I set the second data set to start 4 rows after the end of the first:
With CTOF.Range("B9").End(xlUp).Offset(4, 0)
The problem I have is with this line:
With CTOF.Range("B9").End(xlUp).Offset(4, 0).End(xlUp).Offset(4, 0)
What I'm trying to do is set the start of the third data set 4 rows after the second, but the information is not being paste into the sheet.
I just wondered whether someone may be able to look at this please and offer some guidance on how I may be able to get this to work.
Many thanks and regards
On a given sheet I have a static data set which starts at "B9" and has a dynamic number of rows.
I then use the following code to create and extract data into a second, and if i can get this to work, a third data set.
Sub CTOForecastsExtract()
Dim AFTE As Single
Dim BlnProjExists As Boolean
Dim ColDates As New Collection
Dim CTOF As Worksheet
Dim Flex As String
Dim i As Long
Dim j As Long
Dim JRole As String
Dim LastRow As Long
Dim m As Long
Dim PDate As Date
Dim PLOB As String
Dim Portfolio As String
Dim Project As String
Dim RLOB As String
Dim RngDates As Range
Dim Task As String
Dim ws As Worksheet
Application.ScreenUpdating = False
Const StartRow As Long = 8
Set CTOF = Sheets("All CTO Forecasts Data")
For i = 3 To CTOF.Cells(StartRow - 1, Columns.Count).End(xlToLeft).Column
m = m + 1
ColDates.Add m, CTOF.Cells(StartRow - 1, i).Text
Next i
With Sheets("All Data").Range("H7")
For i = 1 To .CurrentRegion.Rows.Count - 1
Portfolio = .Offset(i, -6) ' Column B
PLOB = .Offset(i, -5) ' Column C
RLOB = .Offset(i, -4) ' Column D
JRole = .Offset(i, -2) ' Column F
Project = .Offset(i, 0) ' Column H
Task = .Offset(i, 4) ' Column L
PDate = .Offset(i, 5) ' Column M
AFTE = .Offset(i, 9) ' Column Q
Flex = .Offset(i, 10) ' Column R
If Portfolio <> "" And InStr(.Offset(i, -4), "Consultancy & Requirements") + _
InStr(.Offset(i, -4), "Strategy & Architecture") > 0 And _
InStr(.Offset(i, -2), "Consultancy & Innovation") = 0 And _
InStr(.Offset(i, 0), "TM - DIR") > 0 And AFTE > 0 And Flex = "Yes" _
And .Offset(i, 5).Value >= Application.Min(CTOF.Rows(7)) Then
Portfolio = .Offset(i, -6)
PLOB = .Offset(i, -5)
RLOB = .Offset(i, -4)
JRole = .Offset(i, -2)
Task = .Offset(i, 4)
With CTOF.Range("B9").End(xlUp).Offset(4, 0)
If .CurrentRegion.Rows.Count = 1 Then
.Offset(1, 0) = Portfolio
j = 1
Else
BlnProjExists = False
For j = 1 To .CurrentRegion.Rows.Count - 1
If .Offset(j, 0) = Portfolio Then
BlnProjExists = True
Exit For
End If
Next j
If BlnProjExists = False Then
.Offset(j, 0) = Portfolio
End If
End If
On Error Resume Next
m = ColDates(Format(PDate, "mmm yy"))
If Err = 0 Then .Offset(j, m) = .Offset(j, m) + AFTE
On Error GoTo 0
End With
End If
If Portfolio <> "" And InStr(.Offset(i, -4), "Consultancy & Requirements") + _
InStr(.Offset(i, -4), "Strategy & Architecture") > 0 And _
InStr(.Offset(i, -2), "Consultancy & Innovation") = 0 And _
InStr(.Offset(i, 0), "Enhancements") > 0 And AFTE > 0 And Flex = "Yes" _
And .Offset(i, 5).Value >= Application.Min(CTOF.Rows(7)) Then
Portfolio = .Offset(i, -6)
PLOB = .Offset(i, -5)
RLOB = .Offset(i, -4)
JRole = .Offset(i, -2)
Task = .Offset(i, 4)
With CTOF.Range("B9").End(xlUp).Offset(4, 0).End(xlUp).Offset(4, 0)
If .CurrentRegion.Rows.Count = 1 Then
.Offset(1, 0) = Portfolio
j = 1
Else
BlnProjExists = False
For j = 1 To .CurrentRegion.Rows.Count - 1
If .Offset(j, 0) = Portfolio Then
BlnProjExists = True
Exit For
End If
Next j
If BlnProjExists = False Then
.Offset(j, 0) = Portfolio
End If
End If
On Error Resume Next
m = ColDates(Format(PDate, "mmm yy"))
If Err = 0 Then .Offset(j, m) = .Offset(j, m) + AFTE
On Error GoTo 0
End With
End If
As you can see at the following line, I set the second data set to start 4 rows after the end of the first:
With CTOF.Range("B9").End(xlUp).Offset(4, 0)
The problem I have is with this line:
With CTOF.Range("B9").End(xlUp).Offset(4, 0).End(xlUp).Offset(4, 0)
What I'm trying to do is set the start of the third data set 4 rows after the second, but the information is not being paste into the sheet.
I just wondered whether someone may be able to look at this please and offer some guidance on how I may be able to get this to work.
Many thanks and regards