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
|