Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return last column of dynamic range
I had some help yesterday from Bernie and Bob getting the first and last ROW
of a dynamic range and that worked great. Thanks again guys! I've attempted to apply the same logic to try and return the first and last COLUMN of a dynamic range and can't seem to get my hands around it for some reason. Here's what I have . An excel spreadsheet with the letter "A" in cells A3 through A15. I have this dynamic range formula: =OFFSET(Sheet1!$1:$1,,,,MATCH("A",Sheet1!$1:$1,0)) All three of the following lines seems to return the first column the letter "A" is found in the dynamic range: = 3 sTemp1 = Range("testcol2").Cells.Count sTemp2 = Cells(Range("testcol2").Cells.Count).Column sTemp3 = Range("testcol2").Cells(Range("testcol2").Cells.Co unt).Column For the life of me I can't seem to get anything to return the LAST column the letter "A" is found in. Can anyone steer me in the right direction? Bob, or Bernie, you guys out there? Thanks Again!! Jim |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return last column of dynamic range
Jim,
MATCH returns the first match, not the count of the matches. Try this for your defined name: =OFFSET(Sheet1!$1:$1,,,,COUNTIF(Sheet1!$1:$1,"A")) Use this version of your code sTemp3 = Range("testcol2").Cells(Range("testcol2").Cells.Co unt).Column HTH, Bernie MS Excel MVP "Jim" <. wrote in message ... I had some help yesterday from Bernie and Bob getting the first and last ROW of a dynamic range and that worked great. Thanks again guys! I've attempted to apply the same logic to try and return the first and last COLUMN of a dynamic range and can't seem to get my hands around it for some reason. Here's what I have . An excel spreadsheet with the letter "A" in cells A3 through A15. I have this dynamic range formula: =OFFSET(Sheet1!$1:$1,,,,MATCH("A",Sheet1!$1:$1,0)) All three of the following lines seems to return the first column the letter "A" is found in the dynamic range: = 3 sTemp1 = Range("testcol2").Cells.Count sTemp2 = Cells(Range("testcol2").Cells.Count).Column sTemp3 = Range("testcol2").Cells(Range("testcol2").Cells.Co unt).Column For the life of me I can't seem to get anything to return the LAST column the letter "A" is found in. Can anyone steer me in the right direction? Bob, or Bernie, you guys out there? Thanks Again!! Jim |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return last column of dynamic range
Jim,
I looked a little closer at your formulas and ignored your words (the two don't agree) : do you have the A's in a row (row 1 A1:L1) or in a column (Column A - A1:A15)? =OFFSET(Sheet1!$1:$1,,,,COUNTIF(Sheet1!$A:$A,"A")) might be better if you have the second case..... HTH, Bernie MS Excel MVP "Jim" <. wrote in message ... I had some help yesterday from Bernie and Bob getting the first and last ROW of a dynamic range and that worked great. Thanks again guys! I've attempted to apply the same logic to try and return the first and last COLUMN of a dynamic range and can't seem to get my hands around it for some reason. Here's what I have . An excel spreadsheet with the letter "A" in cells A3 through A15. I have this dynamic range formula: =OFFSET(Sheet1!$1:$1,,,,MATCH("A",Sheet1!$1:$1,0)) All three of the following lines seems to return the first column the letter "A" is found in the dynamic range: = 3 sTemp1 = Range("testcol2").Cells.Count sTemp2 = Cells(Range("testcol2").Cells.Count).Column sTemp3 = Range("testcol2").Cells(Range("testcol2").Cells.Co unt).Column For the life of me I can't seem to get anything to return the LAST column the letter "A" is found in. Can anyone steer me in the right direction? Bob, or Bernie, you guys out there? Thanks Again!! Jim |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return last column of dynamic range
Bernie, you are absolutely right. My fingers and my brain had a disconnect.
The A's would all be in row 1 - something like C1:Q1. Stupid mistake!! I used your formula below and it now correctly gives the number of columns that contain an "A" but I've now lost the ability to determine the first column that the first A appears in. What I was trying to do was to return the first column and last column that an "A" appeared in. Your code successfully returns the number of columns that an "A" appears in but I need to be able to figure out the column number that the first A appears in - to which I can add the number of columns returned by the countif statement. If I have A's in the third, fourth and fifth column, I need to be able to return something like startcol = 3 and last column =5. Or, startcol=3 and numcols = 3 something like that. Thanks! "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Jim, I looked a little closer at your formulas and ignored your words (the two don't agree) : do you have the A's in a row (row 1 A1:L1) or in a column (Column A - A1:A15)? =OFFSET(Sheet1!$1:$1,,,,COUNTIF(Sheet1!$A:$A,"A")) might be better if you have the second case..... HTH, Bernie MS Excel MVP "Jim" <. wrote in message ... I had some help yesterday from Bernie and Bob getting the first and last ROW of a dynamic range and that worked great. Thanks again guys! I've attempted to apply the same logic to try and return the first and last COLUMN of a dynamic range and can't seem to get my hands around it for some reason. Here's what I have . An excel spreadsheet with the letter "A" in cells A3 through A15. I have this dynamic range formula: =OFFSET(Sheet1!$1:$1,,,,MATCH("A",Sheet1!$1:$1,0)) All three of the following lines seems to return the first column the letter "A" is found in the dynamic range: = 3 sTemp1 = Range("testcol2").Cells.Count sTemp2 = Cells(Range("testcol2").Cells.Count).Column sTemp3 = Range("testcol2").Cells(Range("testcol2").Cells.Co unt).Column For the life of me I can't seem to get anything to return the LAST column the letter "A" is found in. Can anyone steer me in the right direction? Bob, or Bernie, you guys out there? Thanks Again!! Jim |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return last column of dynamic range
Jim,
Define your name as =OFFSET(Sheet1!$1:$1,0,MATCH("A",Sheet1!$1:$1,FALS E)-1,1,COUNTIF(Sheet1!$1:$1,"A")) BUT - you can't have any spaces in your group of "A" values. -- HTH, Bernie MS Excel MVP "Jim" <. wrote in message ... Bernie, you are absolutely right. My fingers and my brain had a disconnect. The A's would all be in row 1 - something like C1:Q1. Stupid mistake!! I used your formula below and it now correctly gives the number of columns that contain an "A" but I've now lost the ability to determine the first column that the first A appears in. What I was trying to do was to return the first column and last column that an "A" appeared in. Your code successfully returns the number of columns that an "A" appears in but I need to be able to figure out the column number that the first A appears in - to which I can add the number of columns returned by the countif statement. If I have A's in the third, fourth and fifth column, I need to be able to return something like startcol = 3 and last column =5. Or, startcol=3 and numcols = 3 something like that. Thanks! "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Jim, I looked a little closer at your formulas and ignored your words (the two don't agree) : do you have the A's in a row (row 1 A1:L1) or in a column (Column A - A1:A15)? =OFFSET(Sheet1!$1:$1,,,,COUNTIF(Sheet1!$A:$A,"A")) might be better if you have the second case..... HTH, Bernie MS Excel MVP "Jim" <. wrote in message ... I had some help yesterday from Bernie and Bob getting the first and last ROW of a dynamic range and that worked great. Thanks again guys! I've attempted to apply the same logic to try and return the first and last COLUMN of a dynamic range and can't seem to get my hands around it for some reason. Here's what I have . An excel spreadsheet with the letter "A" in cells A3 through A15. I have this dynamic range formula: =OFFSET(Sheet1!$1:$1,,,,MATCH("A",Sheet1!$1:$1,0)) All three of the following lines seems to return the first column the letter "A" is found in the dynamic range: = 3 sTemp1 = Range("testcol2").Cells.Count sTemp2 = Cells(Range("testcol2").Cells.Count).Column sTemp3 = Range("testcol2").Cells(Range("testcol2").Cells.Co unt).Column For the life of me I can't seem to get anything to return the LAST column the letter "A" is found in. Can anyone steer me in the right direction? Bob, or Bernie, you guys out there? Thanks Again!! Jim |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return last column of dynamic range
Bernie, that did it!
You ARE the man!! Thanks a bunch! Jim "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Jim, Define your name as =OFFSET(Sheet1!$1:$1,0,MATCH("A",Sheet1!$1:$1,FALS E)-1,1,COUNTIF(Sheet1!$1:$1,"A")) BUT - you can't have any spaces in your group of "A" values. -- HTH, Bernie MS Excel MVP "Jim" <. wrote in message ... Bernie, you are absolutely right. My fingers and my brain had a disconnect. The A's would all be in row 1 - something like C1:Q1. Stupid mistake!! I used your formula below and it now correctly gives the number of columns that contain an "A" but I've now lost the ability to determine the first column that the first A appears in. What I was trying to do was to return the first column and last column that an "A" appeared in. Your code successfully returns the number of columns that an "A" appears in but I need to be able to figure out the column number that the first A appears in - to which I can add the number of columns returned by the countif statement. If I have A's in the third, fourth and fifth column, I need to be able to return something like startcol = 3 and last column =5. Or, startcol=3 and numcols = 3 something like that. Thanks! "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Jim, I looked a little closer at your formulas and ignored your words (the two don't agree) : do you have the A's in a row (row 1 A1:L1) or in a column (Column A - A1:A15)? =OFFSET(Sheet1!$1:$1,,,,COUNTIF(Sheet1!$A:$A,"A")) might be better if you have the second case..... HTH, Bernie MS Excel MVP "Jim" <. wrote in message ... I had some help yesterday from Bernie and Bob getting the first and last ROW of a dynamic range and that worked great. Thanks again guys! I've attempted to apply the same logic to try and return the first and last COLUMN of a dynamic range and can't seem to get my hands around it for some reason. Here's what I have . An excel spreadsheet with the letter "A" in cells A3 through A15. I have this dynamic range formula: =OFFSET(Sheet1!$1:$1,,,,MATCH("A",Sheet1!$1:$1,0)) All three of the following lines seems to return the first column the letter "A" is found in the dynamic range: = 3 sTemp1 = Range("testcol2").Cells.Count sTemp2 = Cells(Range("testcol2").Cells.Count).Column sTemp3 = Range("testcol2").Cells(Range("testcol2").Cells.Co unt).Column For the life of me I can't seem to get anything to return the LAST column the letter "A" is found in. Can anyone steer me in the right direction? Bob, or Bernie, you guys out there? Thanks Again!! Jim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic range based on another column | Setting up and Configuration of Excel | |||
multiple column dynamic range | Excel Discussion (Misc queries) | |||
Formula to count every other column (dynamic range) | New Users to Excel | |||
Dynamic range using partial column? | Excel Discussion (Misc queries) | |||
Return the row numbers or a dynamic range in VBA | Excel Programming |