Updating Date based on replaced Values Excel VBA Question

[Rberndt]

Hi everyone,

I am a new Excel VBA user (Today is the first day I have tried making any macros ever!)

I would like to write a macro which will update a date to todays date if the corresponding number is replaced.

Here are 4 rows of the sheet I am trying to update:

4190-AR FRAME ASSY, FR DR WDO, RH 100011-0281-R00 80200 5AA0A 6/5/13 P42M
4190-AL FRAME ASSY, FR DR WDO, LH 100012-0273-RAC 80201 5AA0A 6/5/13 P42M
4190-USS1 FRAME SUB-ASSY, FR DR WDO, RH 101011-0051-RAC 80210 5AA0A 3/5/13 P42M
4190-USS2 FRAME SUB-ASSY, FR DR WDO, LH 101012-0063-RAA 80211 5AA0A 3/5/13 P42M


I have figured out how to replace the numbers (prob a 'bulky' code, but it works!)


Sub UpdateRevision()

'Assuming the numbers start in C4
Range("C4").Select

'Finds last row with data in it starting from C65536 (Last row in Excel)
Range(Selection, Range("C65536").End(xlUp)).Select

'Counts number of row from C4 to found last row
RowCounter = Selection.Count

'Updates Part Number Revision Level

'Updates RAB to RAC
For i = 4 To RowCounter + 1
Range("C" & i).Select
ActiveCell.Replace What:="RAB", Replacement:="RAC", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Next i

'Updates RAA to RAB
For i = 4 To RowCounter + 1
Range("C" & i).Select
ActiveCell.Replace What:="RAA", Replacement:="RAB", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Next i

'Updates R00 to RAA
For i = 4 To RowCounter + 1
Range("C" & i).Select
ActiveCell.Replace What:="R00", Replacement:="RAA", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False
Next i

End Sub



Now Id like to Update the date when the part number is replaced. For example

If 100011-0281-R00 changes to 100011-0281-RAA, then the Date would change from 6/5/13 to 9/4/13 (todays date)

I am thinking that it might have to be some sort of IF THEN Loop? But like I said this is literally my first go at making a macro, and I havent quite figured those out yet.

but the basic 'structure' might go something like this:?

IF (part number is updated)
THEN (change date to today)
ELSEIF (Part number is the same)
THEN (Dont change Date)

Anyways, any help would be appreciated. And Im not looking for just a simple code - if you could provide comments that actually explain what the code is doing, that would be great as I am trying to learn for my job.

THANKS!!!

- Ryan

[Rberndt]

Sorry for the format issues btw - I tried to make it look nice but it isnt seem to be cooperating with me.