| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert column value to alpha
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 -- |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert column value to alpha
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)).cu t 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" wrote: 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 -- |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert column value to alpha
Joel wrote:
cells(1,ActColumn2).Paste I receive and error when the code gets to this point: Object doesn't support his property or method -- |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert column value to alpha
Joel wrote:
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)).cu t 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" wrote: 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 -- 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(. . .. . -- |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert column value to alpha
Joel wrote:
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)).cu t 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" wrote: 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 -- 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 -- |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Convert column value to alpha
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| convert numbers to alpha code | Excel Discussion (Misc queries) | |||
| Can you convert Excel column indicators from Alpha to Numberic? | Excel Discussion (Misc queries) | |||
| Can you convert Excel column indicators from Alpha to Numberic? | Excel Discussion (Misc queries) | |||
| Convert Alpha to Numeric | Excel Discussion (Misc queries) | |||
| convert cells containing alpha to numeric | Excel Worksheet Functions | |||
Linear Mode
