2 Attachment(s)
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.
Attachment 163595
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.
Attachment 163597
Shel - Penguin98
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) = ""
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
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.
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
Re: VBA Loop with multiple conditions
Quote:
Originally Posted by
Penguin98
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
Re: VBA Loop with multiple conditions
Quote:
Originally Posted by
westconn1
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
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
