| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Identify the column of a range by letter instead of number
Good afternoon, all!
I'm trying to reference a cell based on: Whatever the column is that contains the range "ILHID" (currently column B) and target.row At the moment, I can use range("B"&target.row), but I want to make this dynamic, so that if the position of ILHID changes, the code will still work. Can I either reference ILHID's column by its letter, or is there some clever combination using CELLS that I could use (that I'm currently working on, but not having a great deal of luck with)? Thanks in advance Pete |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Identify the column of a range by letter instead of number
Why not do a Find on ILHID and capture the column of the found cell? Help
has details on the Find method. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Peter Rooney" wrote in message ... Good afternoon, all! I'm trying to reference a cell based on: Whatever the column is that contains the range "ILHID" (currently column B) and target.row At the moment, I can use range("B"&target.row), but I want to make this dynamic, so that if the position of ILHID changes, the code will still work. Can I either reference ILHID's column by its letter, or is there some clever combination using CELLS that I could use (that I'm currently working on, but not having a great deal of luck with)? Thanks in advance Pete |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Identify the column of a range by letter instead of number
cells(Target.row,Range("ILHID").Column)
-- Regards, Tom Ogilvy "Peter Rooney" wrote in message ... Good afternoon, all! I'm trying to reference a cell based on: Whatever the column is that contains the range "ILHID" (currently column B) and target.row At the moment, I can use range("B"&target.row), but I want to make this dynamic, so that if the position of ILHID changes, the code will still work. Can I either reference ILHID's column by its letter, or is there some clever combination using CELLS that I could use (that I'm currently working on, but not having a great deal of luck with)? Thanks in advance Pete |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Identify the column of a range by letter instead of number
Hi, Tom,
This solved part of the problem, but here's what I', REALLY trying to achieve: MsgBox ("Entries in row " & Target.Row & " = " & Application.WorksheetFunction _ .CountA(Cells(Target.Row, Range("ILHID").Column)) _ .Offset(0, 0).Resize(1, Range("IssuesDatabaseStart").CurrentRegion.Columns .Count)) I want to check how many entries there are within the TARGET row of my database. Unfortunately, the above (incorporating your solution) give me an "invalid qualifier" message. I think it's something to do with usin CELLS with application.worksheetfunction.counta. What i want to do is, regardless of the column I'm in, say "from column B (or wherever "ILHID" (which is the column header) may be, to the last column in my database, tell me how many cells have entries in them" Hope this clarifies things. Cheers Pete "Tom Ogilvy" wrote: cells(Target.row,Range("ILHID").Column) -- Regards, Tom Ogilvy "Peter Rooney" wrote in message ... Good afternoon, all! I'm trying to reference a cell based on: Whatever the column is that contains the range "ILHID" (currently column B) and target.row At the moment, I can use range("B"&target.row), but I want to make this dynamic, so that if the position of ILHID changes, the code will still work. Can I either reference ILHID's column by its letter, or is there some clever combination using CELLS that I could use (that I'm currently working on, but not having a great deal of luck with)? Thanks in advance Pete |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Identify the column of a range by letter instead of number
Hi, Bob!
Thanks for this, but here's what I'm, REALLY trying to achieve: MsgBox ("Entries in row " & Target.Row & " = " & Application.WorksheetFunction _ .CountA(Cells(Target.Row, Range("ILHID").Column)) _ .Offset(0, 0).Resize(1, Range("IssuesDatabaseStart").CurrentRegion.Columns .Count)) I want to check how many entries there are within the TARGET row of my database. Unfortunately, the above (incorporating Tom's solution) give me an "invalid qualifier" message. I think it's something to do with usin CELLS with application.worksheetfunction.counta. What i want to do is, regardless of the column I'm in, say "from column B (or wherever "ILHID" ,which is the column header may be), to the last column in my database, tell me how many cells have entries in them" Hope this clarifies things (should have done this first, I guess! Cheers Pete "Bob Phillips" wrote: Why not do a Find on ILHID and capture the column of the found cell? Help has details on the Find method. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Peter Rooney" wrote in message ... Good afternoon, all! I'm trying to reference a cell based on: Whatever the column is that contains the range "ILHID" (currently column B) and target.row At the moment, I can use range("B"&target.row), but I want to make this dynamic, so that if the position of ILHID changes, the code will still work. Can I either reference ILHID's column by its letter, or is there some clever combination using CELLS that I could use (that I'm currently working on, but not having a great deal of luck with)? Thanks in advance Pete |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Identify the column of a range by letter instead of number
Sub ABC()
Set Target = Range("B9") res = Application.WorksheetFunction _ .CountA(Cells(Target.Row, _ Range("ILHID").Column) _ .Resize(1, Range("IssuesDatabaseStart") _ .CurrentRegion.Columns.Count)) MsgBox res End Sub worked fine for me. -- Regards, Tom Ogilvy "Peter Rooney" wrote in message ... Hi, Tom, This solved part of the problem, but here's what I', REALLY trying to achieve: MsgBox ("Entries in row " & Target.Row & " = " & Application.WorksheetFunction _ .CountA(Cells(Target.Row, Range("ILHID").Column)) _ .Offset(0, 0).Resize(1, Range("IssuesDatabaseStart").CurrentRegion.Columns .Count)) I want to check how many entries there are within the TARGET row of my database. Unfortunately, the above (incorporating your solution) give me an "invalid qualifier" message. I think it's something to do with usin CELLS with application.worksheetfunction.counta. What i want to do is, regardless of the column I'm in, say "from column B (or wherever "ILHID" (which is the column header) may be, to the last column in my database, tell me how many cells have entries in them" Hope this clarifies things. Cheers Pete "Tom Ogilvy" wrote: cells(Target.row,Range("ILHID").Column) -- Regards, Tom Ogilvy "Peter Rooney" wrote in message ... Good afternoon, all! I'm trying to reference a cell based on: Whatever the column is that contains the range "ILHID" (currently column B) and target.row At the moment, I can use range("B"&target.row), but I want to make this dynamic, so that if the position of ILHID changes, the code will still work. Can I either reference ILHID's column by its letter, or is there some clever combination using CELLS that I could use (that I'm currently working on, but not having a great deal of luck with)? Thanks in advance Pete |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Identify the column of a range by letter instead of number
Tom,
Still haven't got it, I'm afraid... MsgBox ("Entries in row " & Target.Row & " = " & Application.WorksheetFunction _ .CountA(Cells(Target.Row, Range("ILHID"), Column) _ .Resize(1, Range("IssuesDatabaseStart").CurrentRegion.Columns .Count))) gives me: "Wrong Number or arguments or invalid property assignment" I'm testing it as a MSGBOX before I actually do my logic testing. Sorry, but it's late and my brain's tired! Thanks Pete "Tom Ogilvy" wrote: Sub ABC() Set Target = Range("B9") res = Application.WorksheetFunction _ .CountA(Cells(Target.Row, _ Range("ILHID").Column) _ .Resize(1, Range("IssuesDatabaseStart") _ .CurrentRegion.Columns.Count)) MsgBox res End Sub worked fine for me. -- Regards, Tom Ogilvy "Peter Rooney" wrote in message ... Hi, Tom, This solved part of the problem, but here's what I', REALLY trying to achieve: MsgBox ("Entries in row " & Target.Row & " = " & Application.WorksheetFunction _ .CountA(Cells(Target.Row, Range("ILHID").Column)) _ .Offset(0, 0).Resize(1, Range("IssuesDatabaseStart").CurrentRegion.Columns .Count)) I want to check how many entries there are within the TARGET row of my database. Unfortunately, the above (incorporating your solution) give me an "invalid qualifier" message. I think it's something to do with usin CELLS with application.worksheetfunction.counta. What i want to do is, regardless of the column I'm in, say "from column B (or wherever "ILHID" (which is the column header) may be, to the last column in my database, tell me how many cells have entries in them" Hope this clarifies things. Cheers Pete "Tom Ogilvy" wrote: cells(Target.row,Range("ILHID").Column) -- Regards, Tom Ogilvy "Peter Rooney" wrote in message ... Good afternoon, all! I'm trying to reference a cell based on: Whatever the column is that contains the range "ILHID" (currently column B) and target.row At the moment, I can use range("B"&target.row), but I want to make this dynamic, so that if the position of ILHID changes, the code will still work. Can I either reference ILHID's column by its letter, or is there some clever combination using CELLS that I could use (that I'm currently working on, but not having a great deal of luck with)? Thanks in advance Pete |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Identify the column of a range by letter instead of number
Tom,
My apologies - I had a typo - should have been Range("ILHID").Column) NOT Range("ILHID"), Column) This works fine - thank you VERY much. Just time to fire it back before you think I didn't figure it out for myself (assuming you bother to deign my stupidity with another posting!) regards Pete "Peter Rooney" wrote: Tom, Still haven't got it, I'm afraid... MsgBox ("Entries in row " & Target.Row & " = " & Application.WorksheetFunction _ .CountA(Cells(Target.Row, Range("ILHID"), Column) _ .Resize(1, Range("IssuesDatabaseStart").CurrentRegion.Columns .Count))) gives me: "Wrong Number or arguments or invalid property assignment" I'm testing it as a MSGBOX before I actually do my logic testing. Sorry, but it's late and my brain's tired! Thanks Pete "Tom Ogilvy" wrote: Sub ABC() Set Target = Range("B9") res = Application.WorksheetFunction _ .CountA(Cells(Target.Row, _ Range("ILHID").Column) _ .Resize(1, Range("IssuesDatabaseStart") _ .CurrentRegion.Columns.Count)) MsgBox res End Sub worked fine for me. -- Regards, Tom Ogilvy "Peter Rooney" wrote in message ... Hi, Tom, This solved part of the problem, but here's what I', REALLY trying to achieve: MsgBox ("Entries in row " & Target.Row & " = " & Application.WorksheetFunction _ .CountA(Cells(Target.Row, Range("ILHID").Column)) _ .Offset(0, 0).Resize(1, Range("IssuesDatabaseStart").CurrentRegion.Columns .Count)) I want to check how many entries there are within the TARGET row of my database. Unfortunately, the above (incorporating your solution) give me an "invalid qualifier" message. I think it's something to do with usin CELLS with application.worksheetfunction.counta. What i want to do is, regardless of the column I'm in, say "from column B (or wherever "ILHID" (which is the column header) may be, to the last column in my database, tell me how many cells have entries in them" Hope this clarifies things. Cheers Pete "Tom Ogilvy" wrote: cells(Target.row,Range("ILHID").Column) -- Regards, Tom Ogilvy "Peter Rooney" wrote in message ... Good afternoon, all! I'm trying to reference a cell based on: Whatever the column is that contains the range "ILHID" (currently column B) and target.row At the moment, I can use range("B"&target.row), but I want to make this dynamic, so that if the position of ILHID changes, the code will still work. Can I either reference ILHID's column by its letter, or is there some clever combination using CELLS that I could use (that I'm currently working on, but not having a great deal of luck with)? Thanks in advance Pete |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Identify the column of a range by letter instead of number
Your original post didn't have a comma in it at that location. It was
Range("ILHID").Column -- Regards, Tom Ogilvy "Peter Rooney" wrote in message ... Tom, My apologies - I had a typo - should have been Range("ILHID").Column) NOT Range("ILHID"), Column) This works fine - thank you VERY much. Just time to fire it back before you think I didn't figure it out for myself (assuming you bother to deign my stupidity with another posting!) regards Pete "Peter Rooney" wrote: Tom, Still haven't got it, I'm afraid... MsgBox ("Entries in row " & Target.Row & " = " & Application.WorksheetFunction _ .CountA(Cells(Target.Row, Range("ILHID"), Column) _ .Resize(1, Range("IssuesDatabaseStart").CurrentRegion.Columns .Count))) gives me: "Wrong Number or arguments or invalid property assignment" I'm testing it as a MSGBOX before I actually do my logic testing. Sorry, but it's late and my brain's tired! Thanks Pete "Tom Ogilvy" wrote: Sub ABC() Set Target = Range("B9") res = Application.WorksheetFunction _ .CountA(Cells(Target.Row, _ Range("ILHID").Column) _ .Resize(1, Range("IssuesDatabaseStart") _ .CurrentRegion.Columns.Count)) MsgBox res End Sub worked fine for me. -- Regards, Tom Ogilvy "Peter Rooney" wrote in message ... Hi, Tom, This solved part of the problem, but here's what I', REALLY trying to achieve: MsgBox ("Entries in row " & Target.Row & " = " & Application.WorksheetFunction _ .CountA(Cells(Target.Row, Range("ILHID").Column)) _ .Offset(0, 0).Resize(1, Range("IssuesDatabaseStart").CurrentRegion.Columns .Count)) I want to check how many entries there are within the TARGET row of my database. Unfortunately, the above (incorporating your solution) give me an "invalid qualifier" message. I think it's something to do with usin CELLS with application.worksheetfunction.counta. What i want to do is, regardless of the column I'm in, say "from column B (or wherever "ILHID" (which is the column header) may be, to the last column in my database, tell me how many cells have entries in them" Hope this clarifies things. Cheers Pete "Tom Ogilvy" wrote: cells(Target.row,Range("ILHID").Column) -- Regards, Tom Ogilvy "Peter Rooney" wrote in message ... Good afternoon, all! I'm trying to reference a cell based on: Whatever the column is that contains the range "ILHID" (currently column B) and target.row At the moment, I can use range("B"&target.row), but I want to make this dynamic, so that if the position of ILHID changes, the code will still work. Can I either reference ILHID's column by its letter, or is there some clever combination using CELLS that I could use (that I'm currently working on, but not having a great deal of luck with)? Thanks in advance Pete |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Identify the column of a range by letter instead of number
Tom,
I typed your solution incorrectly, then copied it back to you... Just having a bad day, I guess! Thanks for your patience :-) Pete "Tom Ogilvy" wrote: Your original post didn't have a comma in it at that location. It was Range("ILHID").Column -- Regards, Tom Ogilvy "Peter Rooney" wrote in message ... Tom, My apologies - I had a typo - should have been Range("ILHID").Column) NOT Range("ILHID"), Column) This works fine - thank you VERY much. Just time to fire it back before you think I didn't figure it out for myself (assuming you bother to deign my stupidity with another posting!) regards Pete "Peter Rooney" wrote: Tom, Still haven't got it, I'm afraid... MsgBox ("Entries in row " & Target.Row & " = " & Application.WorksheetFunction _ .CountA(Cells(Target.Row, Range("ILHID"), Column) _ .Resize(1, Range("IssuesDatabaseStart").CurrentRegion.Columns .Count))) gives me: "Wrong Number or arguments or invalid property assignment" I'm testing it as a MSGBOX before I actually do my logic testing. Sorry, but it's late and my brain's tired! Thanks Pete "Tom Ogilvy" wrote: Sub ABC() Set Target = Range("B9") res = Application.WorksheetFunction _ .CountA(Cells(Target.Row, _ Range("ILHID").Column) _ .Resize(1, Range("IssuesDatabaseStart") _ .CurrentRegion.Columns.Count)) MsgBox res End Sub worked fine for me. -- Regards, Tom Ogilvy "Peter Rooney" wrote in message ... Hi, Tom, This solved part of the problem, but here's what I', REALLY trying to achieve: MsgBox ("Entries in row " & Target.Row & " = " & Application.WorksheetFunction _ .CountA(Cells(Target.Row, Range("ILHID").Column)) _ .Offset(0, 0).Resize(1, Range("IssuesDatabaseStart").CurrentRegion.Columns .Count)) I want to check how many entries there are within the TARGET row of my database. Unfortunately, the above (incorporating your solution) give me an "invalid qualifier" message. I think it's something to do with usin CELLS with application.worksheetfunction.counta. What i want to do is, regardless of the column I'm in, say "from column B (or wherever "ILHID" (which is the column header) may be, to the last column in my database, tell me how many cells have entries in them" Hope this clarifies things. Cheers Pete "Tom Ogilvy" wrote: cells(Target.row,Range("ILHID").Column) -- Regards, Tom Ogilvy "Peter Rooney" wrote in message ... Good afternoon, all! I'm trying to reference a cell based on: Whatever the column is that contains the range "ILHID" (currently column B) and target.row At the moment, I can use range("B"&target.row), but I want to make this dynamic, so that if the position of ILHID changes, the code will still work. Can I either reference ILHID's column by its letter, or is there some clever combination using CELLS that I could use (that I'm currently working on, but not having a great deal of luck with)? Thanks in advance Pete |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Identify the row in which a MAX number in a column resides | Excel Worksheet Functions | |||
| How do I turn excel columns from column number to column letter? | Setting up and Configuration of Excel | |||
| Formula to Identify Column Number | Excel Discussion (Misc queries) | |||
| column header changed from letter to number, how return to letter | Excel Discussion (Misc queries) | |||
| Auto number w/ different letter-number combos in same column | Excel Worksheet Functions | |||
Linear Mode
