Convert cells(1,1) into "A1" and vice versa

excel vba cell
user366121 picture user366121 · Jun 7, 2011 · Viewed 197.5k times · Source

I am working on an worksheet generator in Excel 2007. I have a certain layout I have to follow and I often have to format cells based on input. Since the generator is dynamic I have to calculate all kinds of ranges, merge cells, etc.

How can I convert values like this?

Cells(1,1) into A1 and vice versa

Answer

Anders Lindahl picture Anders Lindahl · Jun 7, 2011

The Address property of a cell can get this for you:

MsgBox Cells(1, 1).Address(RowAbsolute:=False, ColumnAbsolute:=False)

returns A1.

The other way around can be done with the Row and Column property of Range:

MsgBox Range("A1").Row & ", " & Range("A1").Column

returns 1,1.

Related questions

VBA setting the formula for a cell

I'm trying to set the formula for a cell using a (dynamically created) sheet name and a fixed cell address. I'm using the following line but can't seem to get it working: "=" & strProjectName & "!" & Cells(2, 7).Address Any advice …

Read More
Get value of a merged cell of an excel from its cell address in vba

How to get the value of a merged cell of an excel having range address like "$B$4:$B$11" in vba

Read More
Excel doesn't update value unless I hit Enter

I have a very annoying problem in one of my worksheets in a workbook. I am using EXCEL 2007. Any cell's value will not be updated unless I hit ENTER. Either if the formula in the cell includes an if condition, …

Read More