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

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

Hi Bernie, thanks for your help.


The aim of the procdiure is this:

The first part of the code chooses only those lines which have a certain
value in column 2.All these values will be consecutive rows as they are all
events taking place ina certain week.The week number is determined by the
inputbox response. The code then select the range of values in column 13
which relate to events in that week.
This selected range in column 13 contains a range of presecribed event codes
e.g. NW,UW,NT,UT,NR etc. There may be any number of each indivudual code in
the range.

I then want to assess the values in that range and record the frequency of
occurrence of each individual code. i.e. it the range in column 13 is:

NW
NR
NW
UT

then I want to show the results in a single row in another sheet as shown

NW NR UT
2 1 1

I need the code to run each week and analyse the event during that week and
add them to the next row in sheet 2 like this

NW NR UT etc
Week 1 2 1 1
Week 2 1 2 5

and so on. I hope you can follow all that and make sense of it.

Reagrds




"Bernie Deitrick" wrote:

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

Alan,

There are two easy ways to do this: the first is with formulas:

My assumptions: the sheet with the week codes in column B is named Sheet2.
If that is wrong, in the formula below simply change it to reflect the
actual name.

Make a table on a new sheet, with your NW,UW,NT,UT,NR, etc values starting
in cell B1 and going across row 1.

Then in cell A2, insert the week code that matches the values in column B of
Sheet2.

Then in cell B2, enter the formula:
=SUMPRODUCT(--(Sheet2!$B$1:$B$1000=$A2),--(Sheet2!$M$1:$M$1000=B$1))

If your data extends for more than 1000 rows, simply increase the two
instances of 1000 to reflect that.

Then copy that formula across row 2 to match your column headings (NW, UW,
etc.)

Then copy row 2 down as far as you need, and change the value in column A to
reflect the week numbers.

Method 2:

Select your data table, and use Data | Pivottable.... Click finish to
create the table, and then drag the week number button to the Row Field.
Then drag the header button from column 13 to the column field, and,
finally, drag that same button to the data field. Excel will create a table
of counts, similar to that from method 1, without any code, formulas, or
hard work on your part.

HTH,
Bernie
MS Excel MVP


"Alan M" wrote in message
...
Hi Bernie, thanks for your help.


The aim of the procdiure is this:

The first part of the code chooses only those lines which have a certain
value in column 2.All these values will be consecutive rows as they are
all
events taking place ina certain week.The week number is determined by the
inputbox response. The code then select the range of values in column 13
which relate to events in that week.
This selected range in column 13 contains a range of presecribed event
codes
e.g. NW,UW,NT,UT,NR etc. There may be any number of each indivudual code
in
the range.

I then want to assess the values in that range and record the frequency of
occurrence of each individual code. i.e. it the range in column 13 is:

NW
NR
NW
UT

then I want to show the results in a single row in another sheet as shown

NW NR UT
2 1 1

I need the code to run each week and analyse the event during that week
and
add them to the next row in sheet 2 like this

NW NR UT etc
Week 1 2 1 1
Week 2 1 2 5

and so on. I hope you can follow all that and make sense of it.

Reagrds




"Bernie Deitrick" wrote:

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

Thanks Bernie but these options will not work.

The sumproduct option will not work as the code labels are not numeric and
so will be treated all as zero.

The pivot table otion is better. this allows the data to be viewed for each
week, but I need to record that information weekly in a permanent table in a
new sheet for trend analysis at a later date.

If I could record the total counts for each week from the pivot table into a
series of rows that would be fine.

"Bernie Deitrick" wrote:

Alan,

There are two easy ways to do this: the first is with formulas:

My assumptions: the sheet with the week codes in column B is named Sheet2.
If that is wrong, in the formula below simply change it to reflect the
actual name.

Make a table on a new sheet, with your NW,UW,NT,UT,NR, etc values starting
in cell B1 and going across row 1.

Then in cell A2, insert the week code that matches the values in column B of
Sheet2.

Then in cell B2, enter the formula:
=SUMPRODUCT(--(Sheet2!$B$1:$B$1000=$A2),--(Sheet2!$M$1:$M$1000=B$1))

If your data extends for more than 1000 rows, simply increase the two
instances of 1000 to reflect that.

Then copy that formula across row 2 to match your column headings (NW, UW,
etc.)

Then copy row 2 down as far as you need, and change the value in column A to
reflect the week numbers.

Method 2:

Select your data table, and use Data | Pivottable.... Click finish to
create the table, and then drag the week number button to the Row Field.
Then drag the header button from column 13 to the column field, and,
finally, drag that same button to the data field. Excel will create a table
of counts, similar to that from method 1, without any code, formulas, or
hard work on your part.

HTH,
Bernie
MS Excel MVP


"Alan M" wrote in message
...
Hi Bernie, thanks for your help.


The aim of the procdiure is this:

The first part of the code chooses only those lines which have a certain
value in column 2.All these values will be consecutive rows as they are
all
events taking place ina certain week.The week number is determined by the
inputbox response. The code then select the range of values in column 13
which relate to events in that week.
This selected range in column 13 contains a range of presecribed event
codes
e.g. NW,UW,NT,UT,NR etc. There may be any number of each indivudual code
in
the range.

I then want to assess the values in that range and record the frequency of
occurrence of each individual code. i.e. it the range in column 13 is:

NW
NR
NW
UT

then I want to show the results in a single row in another sheet as shown

NW NR UT
2 1 1

I need the code to run each week and analyse the event during that week
and
add them to the next row in sheet 2 like this

NW NR UT etc
Week 1 2 1 1
Week 2 1 2 5

and so on. I hope you can follow all that and make sense of it.

Reagrds




"Bernie Deitrick" wrote:

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

Alan,

The labels don't need to be numeric. The formula is generating an array of
TRUE and FALSE values from the comparison, so as long as the values in
column A are the same as those on sheet 2 column B, it doesn't matter what
they are.

As for the pivot table, as long as the data is in your table, it will show
up in your pivot table. So there is no need to 'record' the information in
a permanent table.

HTH,
Bernie
MS Excel MVP

"Alan M" wrote in message
...

Thanks Bernie but these options will not work.

The sumproduct option will not work as the code labels are not numeric and
so will be treated all as zero.

The pivot table otion is better. this allows the data to be viewed for
each
week, but I need to record that information weekly in a permanent table in
a
new sheet for trend analysis at a later date.

If I could record the total counts for each week from the pivot table into
a
series of rows that would be fine.

"Bernie Deitrick" wrote:

Alan,

There are two easy ways to do this: the first is with formulas:

My assumptions: the sheet with the week codes in column B is named
Sheet2.
If that is wrong, in the formula below simply change it to reflect the
actual name.

Make a table on a new sheet, with your NW,UW,NT,UT,NR, etc values
starting
in cell B1 and going across row 1.

Then in cell A2, insert the week code that matches the values in column
B of
Sheet2.

Then in cell B2, enter the formula:
=SUMPRODUCT(--(Sheet2!$B$1:$B$1000=$A2),--(Sheet2!$M$1:$M$1000=B$1))

If your data extends for more than 1000 rows, simply increase the two
instances of 1000 to reflect that.

Then copy that formula across row 2 to match your column headings (NW,
UW,
etc.)

Then copy row 2 down as far as you need, and change the value in column
A to
reflect the week numbers.

Method 2:

Select your data table, and use Data | Pivottable.... Click finish to
create the table, and then drag the week number button to the Row Field.
Then drag the header button from column 13 to the column field, and,
finally, drag that same button to the data field. Excel will create a
table
of counts, similar to that from method 1, without any code, formulas, or
hard work on your part.

HTH,
Bernie
MS Excel MVP


"Alan M" wrote in message
...
Hi Bernie, thanks for your help.


The aim of the procdiure is this:

The first part of the code chooses only those lines which have a
certain
value in column 2.All these values will be consecutive rows as they
are
all
events taking place ina certain week.The week number is determined by
the
inputbox response. The code then select the range of values in column
13
which relate to events in that week.
This selected range in column 13 contains a range of presecribed event
codes
e.g. NW,UW,NT,UT,NR etc. There may be any number of each indivudual
code
in
the range.

I then want to assess the values in that range and record the
frequency of
occurrence of each individual code. i.e. it the range in column 13 is:

NW
NR
NW
UT

then I want to show the results in a single row in another sheet as
shown

NW NR UT
2 1 1

I need the code to run each week and analyse the event during that
week
and
add them to the next row in sheet 2 like this

NW NR UT etc
Week 1 2 1 1
Week 2 1 2 5

and so on. I hope you can follow all that and make sense of it.

Reagrds




"Bernie Deitrick" wrote:

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