Help finding the last cell in a range.

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

Quote:

---

Originally Posted by wnazzaro

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.

Quote:

---

Originally Posted by wnazzaro

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.

---

Code:

---

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

---

Quote:

---

Originally Posted by wnazzaro

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

Code:

---

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

---

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

Quote:

---

Originally Posted by xld

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

Quote:

---

Originally Posted by wnazzaro

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

Code:

---

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

---

as I showed earlier.

Quote:

---

Originally Posted by xld

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.

Code:

---

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

---

Quote:

---

Originally Posted by xld

Code:

---

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.

Quote:

---

Originally Posted by wnazzaro

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 :)

Code:

---

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

---

Quote:

---

Originally Posted by xld

You're trying me out :)

Code:

---

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.