Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variance in an array
In one column, there are values like: Andy Bob Cindy Dan Ed etc... I'm wanting some sort of procedure to go through this column, find how many variations there are and store that number as a variable. Also, I'd like to be able to store all of the string values into an array to be used later. For example; if the above was my data, there would be five variations (Andy, Bob, Cindy, Dan, Ed). Then use that array to take all the rows that have "Andy" in them and move them to a sheet that will be created named "Andy". And loop through each name. I've worked on a somewhat similar project before, but I'm having trouble figuring this one out. Here is the code I used before that would look for one word and move all those rows to another column. This is code that was previously suggested to me on this message board (Thanks Jim Thomlinson!). 'Link' (http://www.excelforum.com/showthread.php?t=385313) Sub CopyCells() Dim rngFirst As Range, rngCurrent As Range, rngFoundCells As Range Dim rngToSearch As Range, rngToPaste As Range Dim wksToSearch As Worksheet, wksToPaste As Worksheet Set wksToSearch = Sheets("Rough") Set wksToPaste = Worksheets.Add(, Sheets.Count) Set rngToSearch = wksToSearch.Cells Set rngToPaste = wksToPaste.Range("A65536").End(xlUp).Offset(1, 0) Set rngCurrent = rngToSearch.Find(strWordToFind, , , xlWhole) If rngCurrent Is Nothing Then MsgBox strWordToFind & " was not found" Else Set rngFirst = rngCurrent Set rngFoundCells = rngCurrent.EntireRow Do Set rngFoundCells = Union(rngCurrent.EntireRow, rngFoundCells) Set rngCurrent = rngToSearch.FindNext(rngCurrent) Loop Until rngFirst.Address = rngCurrent.Address rngFoundCells.Copy rngToPaste rngFoundCells.Delete End If End Sub TIA for all help! DejaVu -- DejaVu ------------------------------------------------------------------------ DejaVu's Profile: http://www.excelforum.com/member.php...o&userid=22629 View this thread: http://www.excelforum.com/showthread...hreadid=389078 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
% Variance | Excel Worksheet Functions | |||
Variance | Excel Discussion (Misc queries) | |||
Pivot Tables - Variance and Variance % | Excel Discussion (Misc queries) | |||
Pivot Tables - Variance and % Variance fields | Excel Discussion (Misc queries) | |||
% variance | Excel Programming |