VBA to update previous entered data

Skrej

Skrej

Board Regular
Joined
May 31, 2013
Messages
176
Office Version
  1. 365
Platform
  1. Windows
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.


<tbody>
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="width: 80, align: center"]E[/TD]

[TD="width: 64, align: center"]1[/TD]
[TD="width: 64, align: center"]Name[/TD]
[TD="width: 66, align: center"]ID[/TD]
[TD="width: 107, align: center"]Department[/TD]
[TD="width: 85, align: center"]Review[/TD]
[TD="width: 80, align: center"]Current?[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Doe, John[/TD]
[TD="align: center"]123[/TD]
[TD="align: center"]VM[/TD]
[TD="align: center"]OK[/TD]
[TD="align: center"]Yes[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Doe, John[/TD]
[TD="align: center"]123[/TD]
[TD="align: center"]VM[/TD]
[TD="align: center"]Poor[/TD]
[TD="align: center"]Yes[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"] Doe, John
 [/TD]
[TD="align: center"] 123 [/TD]
[TD="align: center"] VM [/TD]
[TD="align: center"] Dismissed [/TD]
[TD="align: center"] No [/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]Doe, John[/TD]
[TD="align: center"]123[/TD]
[TD="align: center"]OP[/TD]
[TD="align: center"]OK[/TD]
[TD="align: center"]Yes[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]Doe, John[/TD]
[TD="align: center"]123[/TD]
[TD="align: center"]OP[/TD]
[TD="align: center"]Poor[/TD]
[TD="align: center"]Yes[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]Doe, John[/TD]
[TD="align: center"]123[/TD]
[TD="align: center"]OP[/TD]
[TD="align: center"]OK[/TD]
[TD="align: center"]Yes[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"] Doe, John
 [/TD]
[TD="align: center"] 123 [/TD]
[TD="align: center"] OP [/TD]
[TD="align: center"] fair [/TD]
[TD="align: center"] Yes [/TD]

</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.


<tbody>
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="width: 80, align: center"]E[/TD]

[TD="width: 64, align: center"]1[/TD]
[TD="width: 64, align: center"]Name[/TD]
[TD="width: 66, align: center"]ID[/TD]
[TD="width: 107, align: center"]Department[/TD]
[TD="width: 85, align: center"]Review[/TD]
[TD="width: 80, align: center"]Current?[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Doe, John[/TD]
[TD="align: center"]123[/TD]
[TD="align: center"]VM[/TD]
[TD="align: center"]OK[/TD]
[TD="align: center"] No
 [/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]Doe, John[/TD]
[TD="align: center"]123[/TD]
[TD="align: center"]VM[/TD]
[TD="align: center"]Poor[/TD]
[TD="align: center"] No
 [/TD]

[TD="align: center"]4[/TD]
[TD="align: center"] Doe, John
 [/TD]
[TD="align: center"] 123
 [/TD]
[TD="align: center"] VM
 [/TD]
[TD="align: center"] Dismissed
 [/TD]
[TD="align: center"] No
 [/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]Doe, John[/TD]
[TD="align: center"]123[/TD]
[TD="align: center"]OP[/TD]
[TD="align: center"]OK[/TD]
[TD="align: center"]yes[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]Doe, John[/TD]
[TD="align: center"]123[/TD]
[TD="align: center"]OP[/TD]
[TD="align: center"]Poor[/TD]
[TD="align: center"]yes[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]Doe, John[/TD]
[TD="align: center"]123[/TD]
[TD="align: center"]OP[/TD]
[TD="align: center"]OK[/TD]
[TD="align: center"]yes[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"] Doe, John
 [/TD]
[TD="align: center"] 123
 [/TD]
[TD="align: center"] OP
 [/TD]
[TD="align: center"] fair
 [/TD]
[TD="align: center"] Yes
 [/TD]

</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.
Code: 
  [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.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Seperate Data In One Cell Across Multiple Cells
Replies
6
Views
2K
Alex Blakenburg
A
C
  • Question Question
Ton of script in case anyone wants to review, maybe something useful to someone
Replies
0
Views
3K
cizzett
C
N
  • Question Question
Userform VBA Causing Unwanted Result and Splitting data between two worksheets
Replies
4
Views
884
NavyJack
N
R
  • Question Question
Excel VBA : Identify duplicate & Update list
Replies
2
Views
632
RAKESH230583
R
B
  • Question Question
Big Request, need help before I pull my hair out.
Replies
5
Views
1K
BrisAdrian
B

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top