CODEWITHSUNDEEP
excelvba
Michi
Michi

Reputation: 5471

Getting cell reference via VBA

In my Excel spreadsheet I want to insert the cell reference from Cell A1 into Cell B1 via VBA.
In the end the spreadsheet should look like this:

     A       B
1           A1
2
3
4

Cell B1 is filled by running a VBA that gets the cell reference from Cell A1.
I tried the following VBA but could not make it work (runtime error 1001):

Sub test()
Sheet1.Range("B1").Value = Sheet1.Range(Sheet1.Range("A1").Value)
End Sub

What do I need to change in my VBA to solve my issue?

Upvotes: 1

Views: 7719

Answers (1)

jamheadart
jamheadart

Reputation: 5293

To get the full address of Cell A1 into Cell B1 you can use the function:

Sub AdressCell ()
Sheet1.Range("B1").Value = Sheet1.Range("A1").Address
End Sub

To get rid of the absolute references you can use:

Sub AdressCell ()
Sheet1.Range("B1").Value = Sheet1.Range("A1").Address(False, False)
End Sub

For only getting the Column you can use:

Sub AdressColumn ()
Sheet1.Range("B1").Value = Sheet1.Range("A1").Column
End Sub

For only getting the rows you can use:

Sub AdressRow ()
Sheet1.Range("B1").Value = Sheet1.Range("A1").Row
End Sub

Upvotes: 2

Related Questions