PDA

View Full Version : Row and Column Numbers in VBA



roseberykeba
07-16-2007, 04:50 PM
Hi,

In excel you can enter the formulas "=row()" and "=column()" to return the current row and column indices. I was wondering if there was a way to program a formula in VBA which as part access the current row and column which that formula is in. Now, I know that ActiveCell gives me the active cell however I would like to be able to type my formula into excel once and then fill it down and right and for each cell always access the row and column of that cell. Thank you very much.

Andy

mikerickson
07-16-2007, 05:11 PM
Here are a couple of UDF's that use Application.Caller.

Note that when a routine (function) is called from the spreadsheet, .Caller returns a range. When called from VB, .Caller returns an Error. When called from a button,.Caller returns the button's caption.


Function myColumn() As Double
myColumn = Application.Caller.Column
End Function

Function myRow()
myRow = Application.Caller.Row
End Function

daniel_d_n_r
07-17-2007, 02:30 AM
address will give the cell address

mdmackillop
07-17-2007, 06:38 AM
Hi Andy
Welcome to VBAX
It sounds like you just need to use Absolute references in your formula
as in
=$A$1*$C1

david000
07-17-2007, 09:01 AM
Sub ActCol()
c = Left(ActiveCell.Columns.Address(0, 0), 1)
r = Right(ActiveCell.Rows.Address(0, 0), 1)
MsgBox c & r
End Sub


I've had to do this before....

mdmackillop
07-17-2007, 09:41 AM
Hi David,
Try your code with AF150
Regards
MD

david000
07-17-2007, 10:22 AM
It?s a non-dynamic example.


Hey, let us n00bs be?

mdmackillop
07-17-2007, 10:36 AM
It?s a non-dynamic example.


Hey, let us n00bs be?
The intention here is to answer the OP's question. You gave a "solution" which will work in 234 cells of the spreadsheet. If this was adopted, it would give misleading results outwith this range. While we appreciate all members answering questions, you cannot expect us to ignore misleading answers and "let you be".
A better response from yourself would be a correction of your posting. I would suggest you look at the Split function to achieve a neat solution.
Regards
MD

david000
07-20-2007, 11:26 AM
'via Chip Pearson
Function CLetter(CNumber As Integer) As String
CLetter = Left(Cells(1, CNumber).Address _
(False, False), 1 - (CNumber > 26) - (CNumber > 702))
End Function


Use it like this =CLetter(Coulmn())

Other worksheet Functions...
=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")
=LEFT(ADDRESS(1,COLUMN(),4),LEN(ADDRESS(1,COLUMN(),4))-1)