WHen ever I try and compile this code I get an error there
is a next without for, as far as I can see it should
compile fine

On Error GoTo errmsg
Dim value1, value2, value3 As String

value1 = Sheets("Menu").Range("G22")
value2 = Sheets("Menu").Range("G23")
value3 = Sheets("Menu").Range("G24")

Application.ScreenUpdating = True
Dim cell As Range, rng As Range
Dim sh As Worksheet
With Worksheets("Menu")
Set rng = .Range(.Cells(2, 14), .Cells(2, 14).End(xlDown))
End With
For Each cell In rng
Set sh = Worksheets(cell.Value)
Sheets(sh.Name).Activate

If Not Sheets("Menu").Range("G22") Is Nothing Then
Sheets(sh.Name).Range("B2").End(xlToRight).Offset( 0, 1) =
value1
Sheets(sh.Name).Range("B3").End(xlToRight).Offset( 0, 1) =
value2
Sheets(sh.Name).Range("B4").End(xlToRight).Offset( 0, 1) =
value3
End If

Dim oCell As Range
Dim LastCol As Range
Set LastCol = Cells(2, Columns.Count).End(xlToLeft)
Set rng = Range(Cells(2, 2), LastCol)
If Not Intersect(Target, Range("B2:Z2")) Is Nothing
Then
For Each oCell In rng
If oCell < "" Then
oCell.ColumnWidth = 12

Else
oCell.ColumnWidth = 2
End If

Next oCell

Application.Run Macro:=("EssMenuRetrieve")

Next

Nigel,

Looks like you need to add "End If" immediately after "Next oCell".

Regards,
Jim Cone
San Francisco, USA



"Nigel Bennett" wrote in message
...
WHen ever I try and compile this code I get an error there
is a next without for, as far as I can see it should
compile fine

On Error GoTo errmsg
Dim value1, value2, value3 As String

value1 = Sheets("Menu").Range("G22")
value2 = Sheets("Menu").Range("G23")
value3 = Sheets("Menu").Range("G24")

Application.ScreenUpdating = True
Dim cell As Range, rng As Range
Dim sh As Worksheet
With Worksheets("Menu")
Set rng = .Range(.Cells(2, 14), .Cells(2, 14).End(xlDown))
End With
For Each cell In rng
Set sh = Worksheets(cell.Value)
Sheets(sh.Name).Activate

If Not Sheets("Menu").Range("G22") Is Nothing Then
Sheets(sh.Name).Range("B2").End(xlToRight).Offset( 0, 1) =
value1
Sheets(sh.Name).Range("B3").End(xlToRight).Offset( 0, 1) =
value2
Sheets(sh.Name).Range("B4").End(xlToRight).Offset( 0, 1) =
value3
End If

Dim oCell As Range
Dim LastCol As Range
Set LastCol = Cells(2, Columns.Count).End(xlToLeft)
Set rng = Range(Cells(2, 2), LastCol)
If Not Intersect(Target, Range("B2:Z2")) Is Nothing
Then
For Each oCell In rng
If oCell < "" Then
oCell.ColumnWidth = 12

Else
oCell.ColumnWidth = 2
End If

Next oCell

Application.Run Macro:=("EssMenuRetrieve")

Next

You're missing;

End If
above the line;
Application.Run Macro:=("EssMenuRetrieve")

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Nigel Bennett" wrote:
| WHen ever I try and compile this code I get an error there
| is a next without for, as far as I can see it should
| compile fine
|
| On Error GoTo errmsg
| Dim value1, value2, value3 As String
|
| value1 = Sheets("Menu").Range("G22")
| value2 = Sheets("Menu").Range("G23")
| value3 = Sheets("Menu").Range("G24")
|
| Application.ScreenUpdating = True
| Dim cell As Range, rng As Range
| Dim sh As Worksheet
| With Worksheets("Menu")
| Set rng = .Range(.Cells(2, 14), .Cells(2, 14).End(xlDown))
| End With
| For Each cell In rng
| Set sh = Worksheets(cell.Value)
| Sheets(sh.Name).Activate
|
| If Not Sheets("Menu").Range("G22") Is Nothing Then
| Sheets(sh.Name).Range("B2").End(xlToRight).Offset( 0, 1) =
| value1
| Sheets(sh.Name).Range("B3").End(xlToRight).Offset( 0, 1) =
| value2
| Sheets(sh.Name).Range("B4").End(xlToRight).Offset( 0, 1) =
| value3
| End If
|
| Dim oCell As Range
| Dim LastCol As Range
| Set LastCol = Cells(2, Columns.Count).End(xlToLeft)
| Set rng = Range(Cells(2, 2), LastCol)
| If Not Intersect(Target, Range("B2:Z2")) Is Nothing
| Then
| For Each oCell In rng
| If oCell < "" Then
| oCell.ColumnWidth = 12
|
| Else
| oCell.ColumnWidth = 2
| End If
|
| Next oCell
|
| Application.Run Macro:=("EssMenuRetrieve")
|
| Next
|
|