Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to create a macro that does this.....
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 (G8H8,DATE(YEAR(G8),MONTH(G8)+$F8,DAY(G8)),DATE(Y EAR(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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need to create a macro that does this.....
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 "Willow" wrote: 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 (G8H8,DATE(YEAR(G8),MONTH(G8)+$F8,DAY(G8)),DATE(Y EAR(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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a macro to create excel line graph with coloured pointers anddata lables | Charts and Charting in Excel | |||
create Macro €“ select data, sort by acc no., yr, part no, create P | Excel Programming | |||
could any one please tell me how can i create a macro that runs other macro | Excel Programming | |||
how to create a macro that runs other macro in the same workbook | Excel Programming | |||
Use existing macro to create another macro | Excel Programming |