Help finding the last cell in a range.

[wnazzaro]

Same code as yesterday, new problem.

In Excel: I have a group of columns. Row 2 has Q1, Q2, Q3, Q4 repeated for each year that is in row 1, the four cells in row 1 are merged and hold the year. I can find the beginning of this range using the MATCH function (thanks again xld), but not the end of the range, unless I know the last cell of the range.

This is assuming you don't know the last year of the range in row 1 (that would be too easy).

Right now I am setting the range in my MATCH to A2:IT2 to find the last Q4, which is my fix for now, but I would love to know a way to do this (actually, a way to find the end of a row or column instead of a Do Until...Loop = "" or a SendKeys "^{Down}" would really be great).

Also, why does setting my range to A2:IT2 work, but setting it to A2:IU2 doesn't? Setting it to A2:IV2 also doesn't return the last Q4, but that resets my formula in Excel while testing to the range 2:2, which also doesn't work.

So, I figured out to set my range to A2:IT2 while I was writing the post, but knowledge sharing would be helpful. I spent waaaaay too long this morning trying to figure out how to center across a Merge (HorizontalAlignment) and I appreciate the knowledge that can be found here.

Bill

[Bob Phillips]

In Excel: I have a group of columns. Row 2 has Q1, Q2, Q3, Q4 repeated for each year that is in row 1, the four cells in row 1 are merged and hold the year.

Don't use merge cells, they are more trouble than they warrant.

Use Format>Cells>Alignmnet Centre Across Selection instead.


I can find the beginning of this range using the MATCH function (thanks again xld), but not the end of the range, unless I know the last cell of the range.


iLastCol = Cells(2, Columns.Count).End(xlToLeft).Column


Right now I am setting the range in my MATCH to A2:IT2 to find the last Q4, which is my fix for now, but I would love to know a way to do this (actually, a way to find the end of a row or column instead of a Do Until...Loop = "" or a SendKeys "^{Down}" would really be great).

I would just use


Application.MATCH(value,Range("2:2"),0)

that is test the whole row. It is only 256 cells so there is no great problem.

[wnazzaro]

I would just use

Application.MATCH(value,Range("2:2"),0)

that is test the whole row. It is only 256 cells so there is no great problem.

Thanks for the help.

Match("Q4", Range("2:2"), 0) returns the first instance of Q4, not the last. Since Q4 repeats for each year I used Match("Q4", Range("A2:IT2"), 1) which will return the last instance. I still wonder why that works but if you set the range to "A2:IU2" it doesn't work.

Bill

[Bob Phillips]

Thanks for the help.

Match("Q4", Range("2:2"), 0) returns the first instance of Q4, not the last. Since Q4 repeats for each year I used Match("Q4", Range("A2:IT2"), 1) which will return the last instance. I still wonder why that works but if you set the range to "A2:IU2" it doesn't work.

Bill

I don't think I am getting this.

If I have say Q1 in H2, L2, P2, etc., if I do a Application.MATCH("Q1"),Range("2:2"),1) I get the last one fine. So where is the porblem that you have?

Other than that you can use


Application.MATCH("Q1",Range("A2",Cells(2,iLastCol)),1)

as I showed earlier.

[wnazzaro]

I don't think I am getting this.

If I have say Q1 in H2, L2, P2, etc., if I do a Application.MATCH("Q1"),Range("2:2"),1) I get the last one fine. So where is the porblem that you have?

Other than that you can use

Application.MATCH("Q1",Range("A2",Cells(2,iLastCol)),1)

as I showed earlier.

Try this: put Q1 in H2, L2, P2, and T2 and do the Match. I get 16, not 20 like you would expect.

[Bob Phillips]

Dim iYearCol As Long
Dim iQtrOff As Long
Dim iQtrCol As Long
iYearCol = Application.Match(2007, Range("1:1"), 0)
iQtrOff = Application.Match("Q3", Range(Cells(2, iYearCol), Cells(2, "IV")), 0)
iQtrCol = iYearCol + iQtrOff - 1
MsgBox "Cell required is colum " & iQtrCol

[wnazzaro]

Dim iYearCol As Long
Dim iQtrOff As Long
Dim iQtrCol As Long
iYearCol = Application.Match(2007, Range("1:1"), 0)
iQtrOff = Application.Match("Q3", Range(Cells(2, iYearCol), Cells(2, "IV")), 0)
iQtrCol = iYearCol + iQtrOff - 1
MsgBox "Cell required is colum " & iQtrCol



Like I said, it's easy if you know which year is the last year in the range. Gets harder if you don't have that information.

[Bob Phillips]

Like I said, it's easy if you know which year is the last year in the range. Gets harder if you don't have that information.

You're trying me out :)



Dim iYearCol As Long
Dim iQtrOff As Long
Dim iQtrCol As Long
iYearCol = Cells(1, Columns.Count).End(xlToLeft).Column
iQtrOff = Application.Match("Q3", Range(Cells(2, iYearCol), Cells(2, "IV")), 0)
iQtrCol = iYearCol + iQtrOff - 1

[wnazzaro]

You're trying me out :)



Dim iYearCol As Long
Dim iQtrOff As Long
Dim iQtrCol As Long

iYearCol = Cells(1, Columns.Count).End(xlToLeft).Column
iQtrOff = Application.Match("Q3", Range(Cells(2, iYearCol), Cells(2, "IV")), 0)
iQtrCol = iYearCol + iQtrOff - 1


Like I said, it gets harder for me. Apparently not for you. : )
I have so much to learn.