Macro Help Requires

[Dan Wood]

I have two macros to run when somebody changes my excel sheet. The first one
is assigned to a button, and just checks the spreadsheet for field "A/L" :-

Sub RunIf()
For Each c In Range("C3:F14")
If c.Value = "A/L" Then
Call Add_Appointment
End If
Next
End Sub

Question 1) Will this script look at every field from c3 to f14 eg c3, c4,
c5, c6 etc.

Question 2) Is there a way to update this script to only pick up amendments,
or will this need to be in my second macro?

My second macro creates an appointment within outlook calender:-

Sub Add_Appointment()
Dim myOlapp As Object
Dim myitem As Object

Set myOlapp = CreateObject("Outlook.Application")
Set myitem = myOlapp.createitem(1)

With myitem
.Body = "Annual Leave"
'.Duration = dur'
.AllDayEvent = True
.Subject = Range("C1").Value & " - A/L"
.Save

End With

Set myitem = Nothing
Set myOlapp = Nothing

End Sub

Question 3) How can i get the subject to look at a range of c1 if a cell in
column c has been changed, eg range d1 if d7 has been changed, range g1 if
g13 has been changed?

Question 4) How can i specifiy which calender needs updating?

Any answers to the questions are much apprecitated.

 

[Dave Peterson]

I think you'll have to keep track of which cells have been processed.

You could insert a new column between each column and check the
..offset(0,1).value to keep track there:

if c.value = "A/L" then
if c.offset(0,1).value = "done" then
'skip it
else
'do it
call add_appointment
c.offset(0,1).value = "done"
end if
end if

Or you could use column G:J and use .offset(0,4)...

 

[Dave Peterson]

ps. Yes, it will look at all the cells in that range. But the order will be
across the rows, then down the column.

You could change the loop if you wanted, though.

 

[Dan Wood]

Thanks for your help with this.

I have set the code as follows:-

Sub RunIf()
For Each c In Range("C3:C14")
If c.Value = "A/L" Then
If c.Offset(0, 1).Value = "done" Then
'skip it'
Else
'do it'
Call Add_Appointment
c.Offset(0, 1).Value = "done"
Call Add_Appointment
End If
End If
Next
End Sub

This seems to work, but does put duplicates on the calender.Is there a way
to stop this? Also how can i set the code so it doesn't set reminders?

 

[Dan Wood]

I have managed to stop the reminders but the event is still duplicatating on
the calendar

 

[Dave Peterson]

You call the Add_Appointment twice in this portion:

Call Add_Appointment
c.Offset(0, 1).Value = "done"
Call Add_Appointment