Can't see error
 

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

Can't see error
 

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

Can't see error
 

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
|
|