Assessing range values
I have a selected range which consists of one column of various designated
two character codes .e.g. NW, UW,NR, NT and the like. I need to be able to analyise the range and count the number of instances of each code. The I then need to enter these values into another sheet in a row under the various applicable column headings. For eaxmple. Source range looks like this: UW NW NT NW NT UT NW target looks like this NW UW NT UT etc 2 1 2 1 etc The target cells are to be added as the next new row in the target sheet each time the procedure is run so I need VBA code to do this please. |
Assessing range values
Hi Alan,
The following may work for you (assumes initial data on sheet1 col A, and desired output on sheet2 row1): Sub occur() Dim rng As Range Dim tempRng As Range Dim i As Integer: i = 1 Range("Sheet2!1:2").Delete For Each rng In Range("Sheet1!a:a").SpecialCells(xlCellTypeConstan ts) 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 Sub -- This posting is provided 'AS IS' with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm. "Alan M" wrote in message ... I have a selected range which consists of one column of various designated two character codes .e.g. NW, UW,NR, NT and the like. I need to be able to analyise the range and count the number of instances of each code. The I then need to enter these values into another sheet in a row under the various applicable column headings. For eaxmple. Source range looks like this: UW NW NT NW NT UT NW target looks like this NW UW NT UT etc 2 1 2 1 etc The target cells are to be added as the next new row in the target sheet each time the procedure is run so I need VBA code to do this please. |
All times are GMT +1. The time now is 06:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com