Cell Address

[poitbot]

Good day all,

Just wondering if there's a way to print cell addresses using a macro?

I have text that is being copied to a new worksheet from many work sheets into column A but I would like their cell locations to be printed into column B.

Possible?

Thanks,

Adam

 

[lenze]

Yes, a Range in VBA has an Address Property
What code are you currently using to copy the values into Column "A"?

lenze

 

[poitbot]

Hey Lenze,

The code I'm using is as follows:

Public Sub texttonewsheet()
Dim n As Long, i As Long
Dim rng As range
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
n = 1
For i = 2 To Sheets.Count
For Each rng In Sheets(i).range("A1:CI200")
If rng.Value <> "" Then
If Not Application.IsNumber(rng) Then
Sheets(1).range("A" & n).Value = rng.Value
n = n + 1
End If
End If
Next rng
Next i
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

 

[lenze]

Untested, but I thing it will work using your existing code

Sheets(1).range("A" & n).Value = rng.Value
Sheets(1).Range("B" & n ).Value = rng.Address
n = n + 1

You can also get the Sheet Name if needed

Sheets(1).Range("C" & n).Value = Sheets(i).Name

lenze

 

[poitbot]

That's beautiful!

Thank you very much!