Results 1 to 8 of 8

Thread: VBA Loop with multiple conditions

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2018
    Location
    Southern California
    Posts
    6

    VBA Loop with multiple conditions

    I'm going to try this again. I am a super newbie with VBA and am trying to self teach for a project for work. The most experience I have is with recording and reading macros in excel.

    I tried to post what I needed earlier but wasn't clear enough and didn't really have any code written in VBA "speak".

    I need to find the most recent begin LOA date for each employee. Most employees will have more than 1 line on the spreadsheet. The spreadsheet is specifically sorted to have the "Hist EffDt" (historical LOA Date) with newest at top. I can't just assume the top line of each employee is the most recent BEGIN LOA date because they may have multiple LOA types starting at different times with no return to work.

    Attached should be the sample data. I included what my desired result would be in Column L. I want the desired date to populate Column J and I tried to explain for each line or group of lines what I am trying to accomplish.
    Name:  TestLOAData.jpg
Views: 422
Size:  31.8 KB

    Here is the code I wrote. I think I have the copy paste down correctly but it's the If/Then/Else I am having issues with in VBA. Course, please feel free to correct any of it!

    Dim Cell1 As Integer
    Dim Cell2 As Integer
    Dim StsCell As Integer
    Dim DtCell As Date
    Dim LOADtCell As Date
    Dim NCell As Integer
    Dim NCell2 as Integer

    Sub FindLOADt()
    '
    Cell1 = ActiveCell
    Cell2 = Cell1.Offset(1, 0)
    StsCell = ActiveCell.Offset(1, 6)
    DtCell = ActiveCell.Offset(0, 11)
    LOADtCell = DtCell.Offset(0, 4)
    NCell = Cell1.Offset(1, 0)
    NCell2 = NCell.Offset(1, 0)
    '
    Do Until IsEmpty(Cell2.Value)
    If Cell1 = Cell2 And StsCell = "A"
    'If A2=A3 And I3 = "A" (When loops should be 1 row down)
    ActiveCell = DtCell.Select
    Selection.Copy
    'Then Select and Copy value in Cell H2 (When loops should be 1 row down)
    LOADtCell.Select
    Selection.Paste
    'Then Select cell J2 and paste value from H2 (When loops should be 1 row down)
    Cell1 = ActiveCell.Offset(1, -10)
    'Then make the ActiveCell A3 (When loops should be 1 row down)
    Else If Cell1 = Cell2 And StsCell = "L"
    'Else If A2=A3 And I3 = "L"
    Then If NCell = NCell2 And StsCell = "A"
    'Then If A3=A4 And I4 = "A"
    ActiveCell = DtCell.Select
    Selection.Copy
    'Then Select and Copy value in Cell H3 (When loops should be 1 row down)
    LOADtCell.Select
    Selection.Paste
    'Then Select cell J3 and paste value from H3 (When loops should be 1 row down)
    Cell1 = ActiveCell.Offset(1, -10)
    'Then make the ActiveCell A3 (When loops should be 1 row down)
    Else If Cell1 <> Cell2
    'Else If A2<>A3
    ActiveCell = DtCell.Select
    Selection.Copy
    'Then Select and Copy value in Cell H2 (When loops should be 1 row down)
    LOADtCell.Select
    Selection.Paste
    'Then Select cell J2 and paste value from H2 (When loops should be 1 row down)
    Cell1 = ActiveCell.Offset(1, -10)
    'Then make the ActiveCell A3 (When loops should be 1 row down)
    End If
    End If
    End If
    Loop
    End Sub

    Any help or advice would be GREATLY appreciated.
    FYI - I tried to post the VBA code with indents but it isn't working so I am also posting a picture of what I wrote here.
    Name:  LOADt VBA.jpg
Views: 313
Size:  57.8 KB

    Shel - Penguin98

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,417

    Re: VBA Loop with multiple conditions

    Use Code-Tags for identation [ code] ... My Code [ /code]

    As for your Problem:
    If i'm reading it right:
    Per Employee go down column I and find the oldest "L"-entry before an "A"-Entry occurs (or you hit the next employee)

    Code:
    Dim j As Long
    Dim y As Long   'Save first row of employee
    Dim x As Long
        j = 2
        x = 2
        y = 2
        Do
            Do  'How many rows for each employee?
                j = j + 1
            Loop Until MySheet.Cells(j, 1) <> MySheet.Cells(j + 1, 1)
               
            For x = y To j
                
                If MySheet.Cells(x, 9) = "A" Then
                    Exit For
                ElseIf MySheet.Cells(x, 9) = "L" Then
                    MySheet.Cells(y, 10) = MySheet.Cells(x, 8)
                End If
                
            Next
            
            y = j + 1
              
        Loop Until MySheet.Cells(y, 1) = ""
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3

    Thread Starter
    New Member
    Join Date
    Nov 2018
    Location
    Southern California
    Posts
    6

    Re: VBA Loop with multiple conditions

    The number of rows per employee varies. Does that affect the above? That is why I was trying to loop until EmplID<>EmplID or if EmplID=EmplID then Sts = A

    I also don't understand what you mean about "code tags" (sorry I'm a super newbie trying to learn this on my own)

    Shels/Penguin98

  4. #4
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,392

    Re: VBA Loop with multiple conditions

    Regarding the Code tags, when you make a post here, above the window where you type your text, at the right-hand end of the list of symbols above the text area there is a # symbol. If you hover the mouse pointer over that, you will see that you can wrap Code tags around whatever text you have selected in the text box below. This will place the text into a Code box like the one Zvoni used which makes it a lot more readable for others when looking at your code.

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: VBA Loop with multiple conditions

    personally i believe i would use ADO with an SQL query

    i would first return a recordset of unique list of employees, then loop that list to return a recordset of the minimum (or order by) Hist EffD for each employee, put the result in the row returned by a recordset of minimum or order by row
    that way there is no need to loop all lines

    can you post a workbook (zip first and attach to post) of some sample data for testing
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,417

    Re: VBA Loop with multiple conditions

    Quote Originally Posted by Penguin98 View Post
    The number of rows per employee varies. Does that affect the above?
    Shels/Penguin98
    No, that's what the second Do/Loop is for. See my comment in that line? "How many rows for each employee?"
    The basic condition for this to work is the List being sorted by Employee (whatever criteria, Name, EmpID, etc.) and as a second sorting criteria descending by date.

    FWIW, i'd sort it by EmplID, not name, just because of female employees.
    Jane Miller has an emplID of 123, 3 years later she marries and her name is Jane Brown from then on
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,417

    Re: VBA Loop with multiple conditions

    Quote Originally Posted by westconn1 View Post
    personally i believe i would use ADO with an SQL query

    i would first return a recordset of unique list of employees, then loop that list to return a recordset of the minimum (or order by) Hist EffD for each employee, put the result in the row returned by a recordset of minimum or order by row
    that way there is no need to loop all lines

    can you post a workbook (zip first and attach to post) of some sample data for testing
    Pete, i was thinking about too, but he has a somewhat hidden second criterium: He needs the oldest "L" before an "A" for each employee.
    Right now, i don't have a clue how to get that via SQL.
    I mean, yeah:
    SELECT employee, MIN(MyDate) From Table WHERE Type="L" GROUP BY employee is kinda obvious, but it would ignore any "A"'s intersparsed in the records.
    In case of his sample data, for Jane Jetson it would return "1/6/2017" which is not his desired result
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  8. #8

    Thread Starter
    New Member
    Join Date
    Nov 2018
    Location
    Southern California
    Posts
    6

    Re: VBA Loop with multiple conditions

    Thanks! I'll try this out today. I do make sure to sort the real data by emplID then descending by date because I currently chop it up manually to get what I need but there is now a need for this to be weekly and my manager said we can't do it the manual way then. Unfortunately it is something we need for a policy thing so we HAVE to do it. We are also changing systems in Jan 2020 where the new system will have this date stored somewhere - just trying to limp along with an old system till then

    -Shells/Penguin98

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width