N
Nigel Bennett
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
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