VBA to update previous entered data

[Skrej]

I have a spreadsheet of employees.

Through a userform, I enter various updates on performance, status, etc. Each update is entered on a new line. An employee might be assigned to different departments.

What I'd like is some guidance on VBA that will retroactively update all prior entries for a given person in the 'current?' column ONLY for the given department once I enter one of several various keywords for the 'review' column, such as 'dismissed', 'promoted', or 'resigned'. Any other keywords such as 'good, OK, fair", etc. would have no effect.

So, John Doe's entry might look something like this before the code, with the red entries (color only for post clarity) being the latest update in a dynamic list. Other macros automatically sort and update the list after a new entry to give something like this.

	A	B	C	D	E
1	Name	ID	Department	Review	Current?
2	Doe, John	123	VM	OK	Yes
3	Doe, John	123	VM	Poor	Yes
4	Doe, John	123	VM	Dismissed	No
5	Doe, John	123	OP	OK	Yes
6	Doe, John	123	OP	Poor	Yes
7	Doe, John	123	OP	OK	Yes
8	Doe, John	123	OP	fair	Yes

<tbody>
</tbody>

So, after running the code, it would retroactively update his 'current' status to 'No' for any department where certain reviews are entered such as "Dismissed", "Promoted", or "Left", but if the review were something else, it wouldn't change, and would only update entries for the same department where I enter the review status.

	A	B	C	D	E
1	Name	ID	Department	Review	Current?
2	Doe, John	123	VM	OK	No
3	Doe, John	123	VM	Poor	No
4	Doe, John	123	VM	Dismissed	No
5	Doe, John	123	OP	OK	yes
6	Doe, John	123	OP	Poor	yes
7	Doe, John	123	OP	OK	yes
8	Doe, John	123	OP	fair	Yes

<tbody>
</tbody>

This is very simplified snapshot, but if somebody could suggest the way through VBA to check and update, I can adapt and fit into my other code.

I worked out how to update 'current' column for the actual review line where I make the entry, I'm just not sure how to go back and update the others retroactively.

  [B]Dim wks As Worksheet[/B]
  [B]Dim lRow as Long[/B]
  [B]Dim nr As Long[/B]
  
  [B]Set wks = Sheet2[/B]
  [B]lRow = wks.Cells.SpecialCells(xlCellTypeLastCell).Row[/B]
  
[B]For y = 7 To lRow[/B]
  [B]
Select Case wks.Cells(nr, 3).Value[/B]
[B]Case "VM", “OP”
[/B]
[B]Select Case wks.Cells(nr, 4).Value[/B]
[B]Case "Terminated", "Left", "Promoted", "Dismissed"[/B]
[B]wks.Cells(y, 5).Value = "No"
Case Else
[B] wks.Cells(y, 5).Value = "Yes"
[/B][/B]
[B]End Select[/B]
[B]End Select
[/B]
[B]nr = nr + 1
[/B]
[B]Next y[/B]

Any suggestions are appreciated.

 

[spiralrain]

Re: Help with VBA to update previous entered data

You could try this:

Sub UpdateCurrent()


Dim wks As Worksheet
Dim lrow As Long
Dim y As Long
Dim y2 As Long
Dim Name As String
Dim ID As String
Dim Dep As String


Set wks = ActiveWorkbook.Sheets("Sheet2")
lrow = wks.Cells.SpecialCells(xlCellTypeLastCell).Row


Application.ScreenUpdating = False


For y = 1 To lrow
If wks.Range("D" & y) = "Dismissed" Or wks.Range("D" & y) = "Promoted" Or wks.Range("D" & y) = "Left" Or wks.Range("D" & y) = "Terminated" Then
Name = wks.Range("A" & y)
ID = wks.Range("B" & y)
Dep = wks.Range("C" & y)
For y2 = y To 1 Step -1
If wks.Range("A" & y2) = Name And wks.Range("B" & y2) = ID And wks.Range("C" & y2) = Dep Then
wks.Range("E" & y2) = "No"
End If
Next y2
Else
wks.Range("E" & y) = "Yes"
End If
Next y


Application.ScreenUpdating = True


End Sub