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