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

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

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

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

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

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