Row and Column Numbers in VBA

[roseberykeba]

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]

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]

address will give the cell address

[mdmackillop]

Hi Andy
Welcome to VBAX
It sounds like you just need to use Absolute references in your formula
as in
=$A$1*$C1

[david000]

[VBA]
Sub ActCol()
c = Left(ActiveCell.Columns.Address(0, 0), 1)
r = Right(ActiveCell.Rows.Address(0, 0), 1)
MsgBox c & r
End Sub
[/VBA]

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

[mdmackillop]

Hi David,
Try your code with AF150
Regards
MD

[david000]

It?s a non-dynamic example.


Hey, let us n00bs be?

[mdmackillop]

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]

[VBA]
'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
[/VBA]

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)