Combination of If Then and Do Loop (I think)

Question

This is probably an easy question for someone who is familiar with VBA, but as a newbie, I am totally coming up empty.

I've got two sheets, one is the raw data that I am already using code to pull from Project with no issues. The other is the output sheet. On the Sheet with the data, I've got 3 columns (A, E, and H). Column A has a list of tasks, column E has a description, and column H has a Fiscal Year and Quarter.

On the output page, I have 10 years worth of fiscal years.

What I'm looking to do is scan the tasks for a certain type of task, then once I find that task, I output the description information in the correct corresponding fiscal year.

I feel like it will need to be a combination of a DO Loop and an If Then, but what I was trying was not working. Below is what I started with and knew pretty quickly that it wouldn't work.

Do Until Worksheets("Project Data").Range("A1").Offset(Row, 0).Value = Empty

    If Worksheets("Project Data").Range("A1").Value = "Task example*" Then

        If Worksheets("Project Data").Range("H1") = "FY15*" Then

            If Worksheets("Project Data").Range("E1") = "" Then Worksheets("Output").Range("C5") = 1

        ElseIf Worksheets("Project Data").Range("E1") = "description 1*" Then Worksheets("Output").Range("C5") = 2
        ElseIf Worksheets("Project Data").Range("E1") = "description 2*" Then Worksheets("Output").Range("C5") = 3

        End If

        If Worksheets("Project Data").Range("H1") = "FY16*" Then

            If Worksheets("Project Data").Range("E1") = "" Then Worksheets("Output").Range("C6") = 1

        ElseIf Worksheets("Project Data").Range("E1") = "description 1*" Then Worksheets("Output").Range("C6") = 2
        ElseIf Worksheets("Project Data").Range("E1") = "description 2*" Then Worksheets("Output").Range("C6") = 3

        End If

    Loop

As I said, this was not working for multiple reasons. Any help would be greatly appreciated! Thanks in advance!

Edit: Adding some dummy data. Can't figure out how to add an attachment and don't have a rating to add an image so I've got lists below, hopefully that works. Sorry, this is ugly!

Raw data

Column A (task) / Column E (description) / Column H (FYQ)
Example / A / FY15Q4
Meeting / blank / FY17Q1
Testing / blank / FY16Q3
Example / B / FY15Q3
Example / B / FY16Q1
Meeting / blank / FY15Q2
Testing / blank / FY16Q3
Example / C / FY16Q2

Output data

FY15 / A / B
FY16 / B / C

May be up to 6 on each fiscal year line

legendjr · Answer 1 · 2015-08-03T16:08:48.447

A couple of quick debug things. Try this and let me know if you still need help. I'm hoping this will make the code work somewhat, and I'm trusting your If Then logic is correct.

Dim row as Integer
row = 0
Do Until Worksheets("Project Data").Range("A1").Offset(row, 0).Value = vbNullString

If Worksheets("Project Data").Range("A1").Value = "Task example*" Then
    If Worksheets("Project Data").Range("H1") = "FY15*" Then
        If Worksheets("Project Data").Range("E1") = "" Then Worksheets("Output").Range("C5") = 1
            ElseIf Worksheets("Project Data").Range("E1") = "description 1*" Then Worksheets("Output").Range("C5") = 2
            ElseIf Worksheets("Project Data").Range("E1") = "description 2*" Then Worksheets("Output").Range("C5") = 3
        End If
    End If
Else
    If Worksheets("Project Data").Range("H1") = "FY16*" Then
        If Worksheets("Project Data").Range("E1") = "" Then Worksheets("Output").Range("C6") = 1
            ElseIf Worksheets("Project Data").Range("E1") = "description 1*" Then Worksheets("Output").Range("C6") = 2
            ElseIf Worksheets("Project Data").Range("E1") = "description 2*" Then Worksheets("Output").Range("C6") = 3
        End If
    End If
End If

row = row + 1

Loop

Edit: After the comments, here is what I did. I created a dummy sheet using your inputs added above. I called that sheet "RawData". I created a second sheet called "OutputData". In OutputData, I added FY15-FY18 in cells A1-A4. Macro code was this. Note, this could be prettier, but it should work and be dynamic enough to last the evolution of this spreadsheet.

Option Explicit
Sub GenerateOutputDat()
    Dim taskToFind As String, rawData As Worksheet, outputData As Worksheet, startPoint As Integer
    Dim fiscalYears() As String, arraySize As Integer, x As Integer, n As Variant, descr As Range
'Initialize variables
    Set rawData = ActiveWorkbook.Sheets("RawData")
    Set outputData = ActiveWorkbook.Sheets("OutputData")
    taskToFind = "Example"                              'Change this to find different string
'Setup fiscalYears array
    outputData.Activate
    arraySize = Range("A1").End(xlDown).Row - 1         'because VB Arrays start at 0, not 1
    ReDim fiscalYears(arraySize) As String
    For x = LBound(fiscalYears) To UBound(fiscalYears)
        fiscalYears(x) = outputData.Range("A1").Offset(x, 0).Value
    Next
'logic to populate OutputData
    For Each n In fiscalYears
        rawData.Activate
        Range("A1").Select
        startPoint = Cells.Find(n).Row
        On Error GoTo ErrorHandle
        Cells.Find(n, After:=ActiveCell, SearchOrder:=xlByColumns).Activate
        Do
            Set descr = Cells(ActiveCell.Row, 5)
            If Cells(ActiveCell.Row, 1).Value = taskToFind Then
                outputData.Activate
                Cells.Find(n).Activate
                If Cells(ActiveCell.Row, 2).Value = vbNullString Then
                    ActiveCell.Offset(0, 1).Activate
                Else
                    ActiveCell.End(xlToRight).Offset(0, 1).Activate
                End If
                ActiveCell.Value = descr.Value
            End If
            rawData.Activate
            Cells.Find(n, After:=ActiveCell, SearchOrder:=xlByColumns).Activate
        Loop Until ActiveCell.Row <= startPoint
ErrorHandle:
        Range("A1").Activate
    Next
End Sub

I'm thinking that my logic may be off. The other issue that I forgot to mention is that I will have multiple inputs for each FY, so I don't want to just move down to the next row until it's done searching for all of the FYs. I also am trying to see if there is a way to avoid doing a section of code for each FY like I had above. — sfowler38, Aug 03 '15 at 14:02
To your FY point, you could declare an integer (fiscalYear) and just have Range("H1").Value = "FY" & fiscalYear & "*". Then you could add 1 to your fiscalYear integer during the loop so it will jump to the next year each time. If you could post some dummy sample data from the sheet you're searching, I can try and look at it deeper. I hope I'm helping. — legendjr, Aug 03 '15 at 14:06
You are definitely helping! Trying to add some dummy data right now. — sfowler38, Aug 03 '15 at 14:36
Added some examples, it's ugly, but hopefully it will do. I couldn't figure out how to add an attachment or an image so I had to do it in lists. Any help is greatly appreciated!! — sfowler38, Aug 03 '15 at 14:55
Made some code and put it above in the edit. Take a look. I hope this works for you. — legendjr, Aug 03 '15 at 16:09
I tried this and received an error on the "startPoint = Cells.Find(n).Row" line. It was a Run-time error '91': Object variable or With block variable not set — sfowler38, Aug 03 '15 at 19:41
When you run the macro, open the locals window. It will show all variable names and what they are set to. I would guess that this error is happening because of something wrong with fiscalYears, or n. Please try this and let me know. — legendjr, Aug 04 '15 at 13:09
When I open the locals window, it says No Variables, which seems really odd because I know the variables have been set. The Step Into function doesn't really seem to be working properly either anymore, and it definitely was working correctly yesterday. — sfowler38, Aug 04 '15 at 16:56
I just tested the locals window view on another workbook that I have with macros that I know run and it did give me the list of variables. — sfowler38, Aug 04 '15 at 17:06
The step into isn't full proof. Some parts of VBA "can't be run in" when you do that. So it effectively just skips steps. You can click on the let most side of the screen to add a break point (red dot) at certain steps to make sure it will stop when you need it to. As to your error, I am not sure what it could be. If you copied the Option Explicit then it needs to be declared, which it is in my code above. Try removing the option explicit and see if that fixes it. — legendjr, Aug 05 '15 at 13:03

puzzlepiece87 · Answer 2 · 2015-08-04T18:07:20.360

0

This is a different approach then you've stated, but I think it might also fit your needs. Could you let me know if/how it doesn't so I can improve my answer?

Dim rngTasks As Range
Dim cellTasks As Range
Dim lngTasksRow As Long
Dim lngFYRow As Long
Dim cellFY As Range
Dim lngFYCol As Long

With Worksheets("Project Data")
    'How many rows of tasks are there?
    lngTasksRow = .Range("A" & .Rows.Count).End(xlUp).Row
    'Set a range covering every row of tasks
    Set rngTasks = .Range(.Cells(1, 1), .Cells(lngTasksRow, 1)) 'Range to find task in
End With
With Worksheets("Output")
    'For each row in the range
    For Each cellTasks In rngTasks
        'If the task is the one we are looking for
        If cellTasks.Value = Worksheets("Project Data").Range("A1").Value Then
            'How many FY rows are there on "Output"
            lngFYRow = .Range("A" & .Rows.Count).End(xlUp).Row
            'Search to see if the FY we want is already on "Output"
            Set cellFY = .Range(.Cells(1, 1), .Cells(lngFYRow, 1)).Find(What:=cellTasks.Offset(0, 7).Value, After:=.Range("A1"), Lookat:=xlWhole, LookIn:=xlValues, _
                     SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
            'If not, use the next blank row; if yes, then use the existing row
            If cellFY Is Nothing Then
                lngFYRow = lngFYRow + 1
            Else
                lngFYRow = cellFY.Row
            End If
            'Find next blank column in FY Row
            lngFYCol = Application.CountA(.Rows(lngFYRow)) + 1
            'Copy the description to that column from "Project Data"
            .Cells(lngFYRow, lngFYCol).Value = cellTasks.Offset(0, 4).Value
        End If
    Next cellTasks
End With

edited Aug 04 '15 at 18:07

answered Aug 03 '15 at 16:08

puzzlepiece87

1,537
2
19
36

I tried this as well and it was giving me errors on the variable settings. – sfowler38 Aug 03 '15 at 20:59
I'm getting an "Compile error: Object Required" error on line Set lngTasksRow = .Range("A" & .Rows.Count).End(xlUp).row. – sfowler38 Aug 04 '15 at 17:05
Oops, sorry about that. The "Set" shouldn't be there, I edited it out of my code. Try again. – puzzlepiece87 Aug 04 '15 at 17:10
Okay, tried again and got a Run-time error '91': Object variable or With block variable not set" error on the "cellFY = .Range(.Cells(1, 1)..." line. – sfowler38 Aug 04 '15 at 18:04
Opposite problem from before, fixed. If you have any more problems, can you hit me up here: http://chat.stackoverflow.com/rooms/85137/puzzlepiece87 I might not answer right away but I'm happy to help. – puzzlepiece87 Aug 04 '15 at 18:11
I would send you a message via chat, but I don't have a high enough reputation. When I run the updated version, I don't get any errors, but it doesn't yield any results either. Not sure where it's getting hung up. – sfowler38 Aug 04 '15 at 18:32
I'm not sure if you're up to debugging, but if you are, put `Msgbox` and then whatever variable was just set after each line to see if they were set appropriately. You can run the code and message boxes will pop up telling you what values the variables are. If you see one that's wrong, you can push Ctrl-Break, then hover over the variables in the code and try to figure out what's wrong. – puzzlepiece87 Aug 05 '15 at 14:16
Sorry, just seeing this. But I was able to piece something together that does the job. Thank you for all of your input! Much appreciated! – sfowler38 Aug 06 '15 at 18:14

Combination of If Then and Do Loop (I think)

2 Answers2