Many thanks to those who contribute this forum. I have learned a lot by reading through the countless threads here at MrExcel. I haven't been able to find the answer to this problem though. First, a picture is worth a thousand words here is a screen grab.
http://i262.photobucket.com/albums/ii84/neogeek62/excelshot.jpg
What is depicted in the above link is a snippet. There are actually 5 different values in column A (A1,B2, C3, D3, E4). What I need to do is select all the values in column B that correspond with a given value in column A. In other words I need to copy all of the values in column B that correspond with A1, then all that correspond toB2, and so on. Manually I do this by doing a find for the first instance of the last set, in this case E4, move right on column and do the old "shift+end+down"/copy. Go back and find the next to last, D3 and so on. The problem comes in when I try to automate this, ( I am no vba guy, the macro recorder is about as far as I go). If the number of reference values is always the same this works just fine. Say the stack of E4's is 250 rows long when I recorded it, and as long as the stack is 250 rows long then the macro works. If the stack of E4's is more or less than 250 rows then macro doesn't parse the correct values. I have deduced that this is because the macro recorder uses a set range of cells to copy, depending on where the Find command locates the value correct in column A. I think what I need to do is be able to define a dynamic range, and then copy the same. Am I making any sense? Any help would be greatly appreciated.
http://i262.photobucket.com/albums/ii84/neogeek62/excelshot.jpg
What is depicted in the above link is a snippet. There are actually 5 different values in column A (A1,B2, C3, D3, E4). What I need to do is select all the values in column B that correspond with a given value in column A. In other words I need to copy all of the values in column B that correspond with A1, then all that correspond toB2, and so on. Manually I do this by doing a find for the first instance of the last set, in this case E4, move right on column and do the old "shift+end+down"/copy. Go back and find the next to last, D3 and so on. The problem comes in when I try to automate this, ( I am no vba guy, the macro recorder is about as far as I go). If the number of reference values is always the same this works just fine. Say the stack of E4's is 250 rows long when I recorded it, and as long as the stack is 250 rows long then the macro works. If the stack of E4's is more or less than 250 rows then macro doesn't parse the correct values. I have deduced that this is because the macro recorder uses a set range of cells to copy, depending on where the Find command locates the value correct in column A. I think what I need to do is be able to define a dynamic range, and then copy the same. Am I making any sense? Any help would be greatly appreciated.
