PDA

View Full Version : get letter of the active column



alienscript
09-17-2007, 08:59 AM
Hi VBA gurus,

Can someone help me please? I am stuck with this but temporarily use hardcode letter "F" of the column of the selected range. Any help is very very much appreciated. Thanks a million!



Dim finalRow As Long
finalRow = Sheets(2).Range("A65536").End(xlUp).Row
Rows("1:2").Select
Selection.Find(What:="PartNumber").Activate
Selection.Copy
Sheets(2).Range(ActiveCell.Address & ":F" & finalRow).Copy _
Sheets(1).Range("IV1").End(xlToLeft).Offset(0, 1)

Bob Phillips
09-17-2007, 09:10 AM
Sheets(2).Range(ActiveCell.Address, Sheets(2).Cells(finalRow, Activecell.Column)).Copy _
Sheets(1).Range("IV1").End(xlToLeft).Offset(0, 1)

Oorang
09-17-2007, 09:11 AM
Try This:
Public Sub Test()
VBA.MsgBox ColumnLetter(ActiveCell.Column)
End Sub
Public Function ColumnLetter(ColumnNumber As Long) As String
Const lngOffset_c As Long = 64
ColumnLetter = VBA.Chr$(ColumnNumber + lngOffset_c)
End Function
Or you could switch from "range" to the "cells" syntax which allows you to use numeric row/column reference. (My own preference.)
Public Sub Test()
Dim ws As Excel.Worksheet
Set ws = Excel.ActiveSheet
ws.Cells(1, 2).Value = "Foo"
End Sub

Bob Phillips
09-17-2007, 09:12 AM
Perhaps better



Dim finalRow As Long
With Sheets(2)
finalRow = .Range("A" & .Rows.Count).End(xlUp).Row
.Rows("1:2").Find(What:="PartNumber").Activate
Selection.Copy
.Range(ActiveCell.Address).Resize(finalRow - ActiveCell.Row + 1).Copy _
Sheets(1).Cells(1, .Columns.Count).End(xlToLeft).Offset(0, 1)
End With

Bob Phillips
09-17-2007, 09:16 AM
Try This:
Public Sub Test()
VBA.MsgBox ColumnLetter(ActiveCell.Column)
End Sub
Public Function ColumnLetter(ColumnNumber As Long) As String
Const lngOffset_c As Long = 64
ColumnLetter = VBA.Chr$(ColumnNumber + lngOffset_c)
End Function



Public Sub Test()
VBA.MsgBox ColumnLetter(Range("AA1").Column)
End Sub

alienscript
09-17-2007, 09:36 AM
wow! you guys'r gorgeous. Thanks so much.