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
|