Excel Cell Reference 1

[TomJinCA]

Hi all,

I've done alot with vb but just getting my feet wet with VBA in Excel. I see it can do alot of neat things, but I'm having a very basic (haha) problem. How do I get the reference to the current cell I'm in?? And how do I traverse cells?

For example, I'm in a cell and want to copy the current column and insert a copy right where I am. I know some code like this will do it:

Columns("N:N&quot.Select
Selection.Copy
Selection.Insert Shift:=xlToRight

But how do I get the "N:N" to refer to the current column I'm in?

In other words, how do I reference other cells/columns/rows from where I am?

Thanks much,

Tom


Tom Jacobson ...
MS Exchange admin, NT admin, Intranet admin, Virus protection, Frontpage, VB5&6, Access, Crystal Reports

 

[Store300]

If you're happy with R1C1 notation you can use:

ActiveCell.Row or ActiveCell.Column both of which give you numerical references (eg Row = 10, Column = 3 is C10).

If not you can try using ActiveCell.Address which gives you $C$10 and then manipulate it to split out the 'C' and the '10'

Sub FindColumn()

'Get current cells absolute address
a = ActiveCell.Address
'Search for the second '$'
'(start at the secon character as the first is always a '$'
b = InStr(2, a, "$&quot
'Split off the second '$' and the row number
MyColumn = Left(a, b - 1)
'Split off the leading '$'
MyColumn = Right(MyColumn, Len(MyColumn) - 1)
'Build a column reference for inserting
MyColumn = MyColumn & ":" & MyColumn

Columns(MyColumn).Select
Selection.Copy
Selection.Insert Shift:=xlToRight

End Sub

 

[TomJinCA]

Thanks very much, I had figured it out last night. That's what I was looking for - the activecell.row or column.

Thanks again for replying!

Tom J Tom Jacobson ...
MS Exchange admin, NT admin, Intranet admin, Virus protection, Frontpage, VB5&6, Access, Crystal Reports