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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variance in an array
You can do this with Advance filters.
To get the code just record macro(s) while you perform it, than edit the code to simplify it. Do an advanced filter on the name column and put the results in another place using Unique Records. Than just count the number (you can erase this at anytime through code.) But you can use that list to perform Advanced Filter on the database and hide all but one of the names. You can either have the filter put the data elsewhere or filter it in place. Than use Edit Goto Special visible cells only, copy and paste. And you should be able to do all of this in code without any selecting... -- steveB Remove "AYN" from email to respond "DejaVu" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Variance in an array
Ron de Bruin has already written sample code for this:
http://www.rondebruin.nl/copy5.htm#all -- Regards, Tom Ogilvy "DejaVu" wrote in message ... 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 |
Reply |
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 |