Convert column value to alpha

[Chris Premo]

I'm parsing through a Text file with excel and trying to minimize the
code. This is the code I've started with and would like to use a Do
While loop. I've defined my starting and ending points with the ActRow
and ActColumn statements. Unfortunately, the "ActiveCell.Column"
statement returns a numeric value (1 in this case) for Column "A".

How can I convert this to "A", then "B", then "C", etc until I reach
the "ActRow" value (in my case 30)?


Columns("A:B").Select
Columns("A:B").EntireColumn.AutoFit

Range("A1").Select
Selection.End(xlDown).Select
ActRow = ActiveCell.Row
Dim ActColumn
ActColumn = ActiveCell.Column

ActColumn2 = ActColumn + 2

Range(ActColumn & "1:" & ActColumn & ActRow).Select
Selection.Cut
Range(ActColumn2 & "1").Select
ActiveSheet.Paste

Do While Counter <= ActRow

Range(ActColumn2 & "2:" & ActColumn & ActRow).Select
Selection.Cut
Range(ActColumn3 & "1").Select
ActiveSheet.Paste
ActColumn2 = ActColumn2 + 1
ActColumn3 = ActColumn3 + 1
Counter = Counter + 1

Loop

--

 

[Joel]

Your problems are due to COLUNNS returning a number for the column and the
RANGE requires a letter. You need to use CELLS in this case

Dim ActColumn


Columns("A:B").Select
Columns("A:B").EntireColumn.AutoFit

LastRow = Range("A1").End(xlDown).Row

ActColumn = ActiveCell.Column

ActColumn2 = ActColumn + 2

Range(cells(1,ActColumn),cells(LastRow,ActRow)).cut

cells(1,ActColumn2).Paste

Counter = 1
Do While Counter <= LastRow

range(cells(2,ActColumn2),cells(LastRow,ActColumn)).Cut
cells(1,ActColumn3).Paste
ActColumn2 = ActColumn2 + 1
ActColumn3 = ActColumn3 + 1
Counter = Counter + 1

Loop

 

[Chris Premo]

cells(1,ActColumn2).Paste

I receive and error when the code gets to this point: Object doesn't
support his property or method



--

 

[Chris Premo]

Your problems are due to COLUNNS returning a number for the column
and the RANGE requires a letter. You need to use CELLS in this case

Dim ActColumn


Columns("A:B").Select
Columns("A:B").EntireColumn.AutoFit

LastRow = Range("A1").End(xlDown).Row

ActColumn = ActiveCell.Column

ActColumn2 = ActColumn + 2

Range(cells(1,ActColumn),cells(LastRow,ActRow)).cut

cells(1,ActColumn2).Paste

Counter = 1
Do While Counter <= LastRow

range(cells(2,ActColumn2),cells(LastRow,ActColumn)).Cut
cells(1,ActColumn3).Paste
ActColumn2 = ActColumn2 + 1
ActColumn3 = ActColumn3 + 1
Counter = Counter + 1

Loop

What I need is some type of code logic that will do the same as this,
but cleaner:

ActColumn3 = IIf(ActColumn2 = 3, "C", IIf(ActColumn2 = 4, "D", IIf(. .
.. .

--

 

[Chris Premo]

Your problems are due to COLUNNS returning a number for the column
and the RANGE requires a letter. You need to use CELLS in this case

Dim ActColumn


Columns("A:B").Select
Columns("A:B").EntireColumn.AutoFit

LastRow = Range("A1").End(xlDown).Row

ActColumn = ActiveCell.Column

ActColumn2 = ActColumn + 2

Range(cells(1,ActColumn),cells(LastRow,ActRow)).cut

cells(1,ActColumn2).Paste

Counter = 1
Do While Counter <= LastRow

range(cells(2,ActColumn2),cells(LastRow,ActColumn)).Cut
cells(1,ActColumn3).Paste
ActColumn2 = ActColumn2 + 1
ActColumn3 = ActColumn3 + 1
Counter = Counter + 1

Loop

This works, but I'd like to make the code simpler for ActColumn3 and go
out to a defined point (eg. column "AZ")

ActColumn3 = IIf(ActColumn2 = 3, "C", IIf(ActColumn2 = 4, "D", ... )
Range(Cells(1, ActColumn), Cells(LastRow, ActRow)).Cut
Range(ActColumn3 & 1).Select
ActiveSheet.Paste

--

 

[Chris]

This is how I did it and it works:
**********************************************************
'This Function changes the data in the file from a Column format
'to a row format. This supposes a "Two" column data file and only
'one record. This first column would contain the "Column Names" and
'the second column contains the actual data.

'This process sets the begining and ending column points that will
'be used to move the data.
Range("A1").Select
ActRow1 = ActiveCell.Row
ActColumn1 = ActiveCell.Column
LastRow = Range("A1").End(xlDown).Row
ActColumn2 = ActColumn1 + 2

'This process begins the move of the First Column information
Range(Cells(ActRow1, ActColumn1), Cells(LastRow, ActRow1)).Select
Range(Cells(1, ActColumn1), Cells(LastRow, ActRow1)).Cut
Cells(1, ActColumn2).Select
ActiveSheet.Paste

'This sets the new position set point for the rest of the First
'Column move. It loops through the data until the end point (LastRow)
'is reached.
ActColumn1 = ActiveCell.Column
Counter = 1
Do While Counter < LastRow
Range(Cells(2, ActColumn1), Cells(LastRow, ActColumn1)).Cut
ActColumn2 = ActColumn1 + 1
ActColumn1 = ActiveCell.Column + 1
Cells(1, ActColumn2).Select
ActiveSheet.Paste
Counter = Counter + 1
Loop

'This process sets the begining and ending column points that will
'be used to move the "Actual Data".
Range("B1").Select
ActRow1 = ActiveCell.Row + 1
ActColumn1 = ActiveCell.Column
LastRow = Range("B1").End(xlDown).Row
ActColumn2 = ActColumn1 + 1

'This process begins the move of the Second Column information
Range(Cells(ActRow1, ActColumn1), Cells(LastRow, ActRow1)).Select
Range(Cells(1, ActColumn1), Cells(LastRow, ActRow1)).Cut
Cells(2, ActColumn2).Select
ActiveSheet.Paste

'This sets the new position set point for the rest of the First
'Column move. It loops through the data until the end point (LastRow)
'is reached.
ActColumn1 = ActiveCell.Column
Counter = 1
Do While Counter < LastRow
Range(Cells(3, ActColumn1), Cells(LastRow + 1, ActColumn1)).Cut
ActColumn2 = ActColumn1 + 1
ActColumn1 = ActiveCell.Column + 1
Cells(2, ActColumn2).Select
ActiveSheet.Paste
Counter = Counter + 1
Loop

Columns("A:B").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select