Hi all,

I have the following code:

I would like to make the following alternations.

1. Instead of specifying where it should stop I would like this to be
the last row of the data on the Shift Change Request sheet.

2. The If statement is causing an error. I want it to check cell F10
to see if it has the value "Yes", and if it does execute Macro 3.

Sub Macro8()
'
' Macro8 Macro
'
'
'
Application.Run "'Shift Changes.xls'!Macro2"
For i = 1 To 10
If Sheets("Shift Change Request")!R[10]C[6]="Yes" Then
Application.Run "'Shift Changes.xls'!Macro3"
Sheets("Shift Change History").Select
End If
Next
Application.Run "'Shift Changes.xls'!Macro4"
End Sub

Any help is greatly appreciated.

Cheers,
Amy

Sub Macro8()

lastrow = Worksheets("Shift Change Request") _
.Cells(rows.count,6).End(xlup).row
Application.Run "'Shift Changes.xls'!Macro2"
Sheets("Shift Change History").Select
For i = 1 To LastRow
If Sheets("Shift Change Request").Cells(10,6).value ="Yes" Then
Application.Run "'Shift Changes.xls'!Macro3"
End If
Next
Application.Run "'Shift Changes.xls'!Macro4"
End Sub

is a guess. But I don't see the utility of running Shift
Changes.xls!Macro3 for as many times as there are rows of data in Shift
Change Request. You don't use the loop variable at all, so maybe there
should be some code changing the activecell or something.

--
Regards,
Tom Ogilvy


"Amy" wrote in message
om...
Hi all,

I have the following code:

I would like to make the following alternations.

1. Instead of specifying where it should stop I would like this to be
the last row of the data on the Shift Change Request sheet.

2. The If statement is causing an error. I want it to check cell F10
to see if it has the value "Yes", and if it does execute Macro 3.

Sub Macro8()
'
' Macro8 Macro
'
'
'
Application.Run "'Shift Changes.xls'!Macro2"
For i = 1 To 10
If Sheets("Shift Change Request")!R[10]C[6]="Yes" Then
Application.Run "'Shift Changes.xls'!Macro3"
Sheets("Shift Change History").Select
End If
Next
Application.Run "'Shift Changes.xls'!Macro4"
End Sub

Any help is greatly appreciated.

Cheers,
Amy

Sub Macro8()
'
' Macro8 Macro
'
'
'
Application.Run "'Shift Changes.xls'!Macro2"
For i = 1 To Sheets("Shift Change Request").UsedRange.Rows.Count
If Sheets("Shift Change Request").Range("F10").Value = "Yes" Then
Application.Run "'Shift Changes.xls'!Macro3"
Sheets("Shift Change History").Select
End If
Next
Application.Run "'Shift Changes.xls'!Macro4"
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Amy" wrote in message
om...
Hi all,

I have the following code:

I would like to make the following alternations.

1. Instead of specifying where it should stop I would like this to be
the last row of the data on the Shift Change Request sheet.

2. The If statement is causing an error. I want it to check cell F10
to see if it has the value "Yes", and if it does execute Macro 3.

Sub Macro8()
'
' Macro8 Macro
'
'
'
Application.Run "'Shift Changes.xls'!Macro2"
For i = 1 To 10
If Sheets("Shift Change Request")!R[10]C[6]="Yes" Then
Application.Run "'Shift Changes.xls'!Macro3"
Sheets("Shift Change History").Select
End If
Next
Application.Run "'Shift Changes.xls'!Macro4"
End Sub

Any help is greatly appreciated.

Cheers,
Amy

Thanks Tom, this works wonderfully. The row is moved by Macro 3, so
the variables don't need to be changed.

Cheers,
Amy