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

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

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

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

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

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

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

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

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

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