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