Alan,

Change

Range("Sheet2!1:1").Cells(1, i)

to

Worksheets("Sheet2").Range("1:1").Cells(1, i)

While I haven't figured out what the rest of your code does, it seems more
convoluted than it needs to be. Perhaps if you described what you want to
do, we could help simplify your code.

HTH,
Bernie
MS Excel MVP

"Alan M" wrote in message
...
I am using the following code to assess the number of occurrences of
various
values in a selected range in one sheet and place the results in a single
row on another sheet,

The first part select the source range which is fine but when the code
gets
to line -
Set tempRng = Range("Sheet2!1:1").Cells(1, i)

I get a runtime error 1004 message application -defined error

Can you help resolve this please



Sub GetCells()
Dim res As Variant, rng1 As Range
Dim lastrow As Long, rng As Range
Dim r As Integer
Dim W As Integer
Dim tempRng As Range
Dim i As Integer: i = 1


W = InputBox("Which week do you want to summarise?", "Week Summary", "?")

res = Application.Match(W, Columns(2), 0)
lastrow = res

Do While Cells(lastrow, 2) = W
lastrow = lastrow + 1
Loop

Set rng = Range(Cells(res, 2), Cells(lastrow - 1, 2))
Set rng1 = Intersect(Columns(13), rng.EntireRow)

rng1.Select

With rng1(lastrow, 1)


For Each rng In rng1.SpecialCells(xlCellTypeConstants)
If Not IsNumeric(Evaluate("MATCH(" & rng.Address & ", Sheet2!1:1, 0)"))
_
Then
Set tempRng = Range("Sheet2!1:1").Cells(1, i)
tempRng.Value2 = rng.Value2
tempRng.Offset(1, 0).Value2 = Evaluate("COUNTIF(a:a, " &
rng.Address _
& ")")
i = i + 1
End If
Next
End With