VBA Dynamic Start Rows

[hobbiton73]

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

[Bob Phillips]

Why do you go up, I would have thought you would want to go down.

Not tested, but try this

 With CTOF.Range("B1").End(xlDown).Offset(4, 0)

in both instances

[hobbiton73]

Hi @xld, thank you very much for coming back to me with this.

Using the suggestions you kindly made, I can get the first range in the script to paste correctly into the sheet, but the second as before, fails to paste to the sheet.

Many thanks and kind regards

[Bob Phillips]

I just noticed that you said the data range starts at B9. Does it make any difference if you change B1 to B9 in the code I suggested? Other than that, can you post your workbook so we can see it action?

[hobbiton73]

Hi @xld, thank you very much for coming back to me with this and my apologies for not replying sooner.

Yes, I had picked up that "B1" may need changing to "B9", but unfortunately this didn't overcome the original problem.

Unfortunately I'm unable to post a workbook because it is really very complicated and contains sensitive information. I have continued to work with this over the weekend, and copious forums without success.

I appreciate for you to proceed, you really need a copy of the workbook which as I said earlier, I'm unable to provide. So please don't spend anymore time on this and I'll have to see whether I may be able to do this differently.

All the best and kind regards

Chris

[Bob Phillips]

I hate to abandon and leave you on this Chris. I'll wager it s relatively straight-forward once we see the data. Is there now way that you can just take the relevant sheets, obfuscate the data, and post something that shows the problem?

[hobbiton73]

Hi @xld, I certainly don't feel you've abandoned me.

It's going to take a bit of time to put something together, so if will probably be the weekend when I post this, if that's ok.

All the best and kind regards

Chris

[snb]

Did you know ?
Instead of

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

you can use:

sn=Sheets("All CTO Forecasts Data").rows(7).specialcells(2).offset(,2),specialcells(2)

[hobbiton73]

Hi @sb, thank you very much for sharing this with me, I wansn't aware it could be done this way.

Kind Regards

Chris

[hobbiton73]

Hi @snb you kindly replied to my post with a suggestion for a change in my code.

Could you please have a look at the line you suggested because when I've tried to use this, it appears as if there is something missing from the end because VB editor highlights the line in red.

Could you also tell me please how the following section of code needs to be changed as shown in my original post as this is looking for the 'coldates' variable replaced by your line of code.

On Error Resume Next
                    m = ColDates(Format(PDate, "mmm yy")) 
                   Err = 0  .Offset(j, m) = .Offset(j, m) + AFTE 
                    On Error Goto 0

Many thanks and kind regards

Chris

[hobbiton73]

Hi @xld, I hope you are well. You may recall when you kindly offered help for this particualr issue I said I would get back to you with a worksheet to look at.

Although, I'm disappointed that I wasn't able to find the answer, last Wednesday my managers requirements for the report changed, which now means I don't need to put the sheet togther in the manner I had originally planned, and have not put togteher the sheet int he new requirfed format.

I therefore just wanted to let you know that I don't need to trouble you any further and thank you for your help.

Many thanks and kind regards

Chris