Need to create a macro that does this.....

[Willow]

I have a worksheet that contains the following information:

All from row 8 down

Cell I4 - todays date
Column F - Service frequency (i.e. 6 or 12)
Column G - Date new machine was provided
Column H - Date of last service
Column I - Date of next service - calculated using the formula
(G8>H8,DATE(YEAR(G8),MONTH(G8)+$F8,DAY(G8)),DATE(YEAR(H8),MONTH(H8)+
$F8,DAY(H8)))
Column J - Date complete

I want to be able to create a macro which I can apply to an update
button which does the following:

1. Uses this formula to update the value of Column H -
IF(MONTH(J8)<=MONTH($I$4)-2,IF(DAY(J8)<=DAY($I$4),J8,H8)) - i.e. where
the value in column J is more than two months ago, update column H
with this value, otherwise leave column H unchanged. I need this to
just place the result in column H and not the formula, otherwise it
will create a circular reference.

2. Where the updated value of column H is equal to the value in column
J i.e. has been changed, delete the value in column J.

While the formulas above relate to row 8, obviously I need to apply it
to all of the rows 8 and below.

I have very limited VBA knowledge and can normally work stuff out, if
I have a starting point, but I have no idea where to even start with
this. I have tried to work out the VBA by recording a macro but it
didn't help.

Any help would be greatly appreciated.

Many Thanks
H

 

[Guest]

Your code should look something like this

Sub test()

ServiceFrequency = Range("F4").Value
NewMachineDate = Range("G4").Value
LastService = Range("H4").Value
NextService = Range("I4").Value
DateComplete = Range("J4").Value

LastRow = Cells(Rows.Count, "H").End(xlUp).Row

For RowCount = 4 To LastRow
LastLastService = Range("H" & RowCount).Value
LastDateComplete = Range("J" & RowCount).Value
If Month(LastDateComplete) <= (Month(NextService) - 2) Then
If Day(LastDateComplete) <= Day(NextService) Then
NewLastService = LastDateComplete
Else
NewLastService = LastLastService
End If
Range("H" & RowCount).Value = NewLastService
If LastDateComplete = NewLastService Then
Range("J" & RowCount).ClearContents
End If
End If
Next RowCount
End Sub