Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
InputBox for column letter problem
The second InputBox is to get the column letter of the last column to include in the code as the range to transfer a long column/s into several shorter column/s to the next sheet.
I want to be able to change nCols in this line to the column of my choice. wks1.Range("A" & i & ":nCols" & i + nnCols).Copy wks2.Cells(1, j)... I can hard code it to C, D or whatever and it works fine. Thanks. Howard Option Explicit Sub AcolumToNcolumns() Dim wks1 As Worksheet, wks2 As Worksheet Dim iColumn As Integer Dim lLast As Long Dim i As Long, j As Integer, y As Integer Dim nRows As String Dim nnCols As String Dim nCols As String nRows = InputBox("Colum No. of Rows.", "Enter value") If nRows = vbNullString Then Exit Sub nCols = InputBox("Colum A to Column ?", "Enter Column Letter") If nCols = vbNullString Then Exit Sub nnCols = nRows - 1 Set wks1 = Worksheets("One Column") Set wks2 = Worksheets("N Columns") lLast = wks1.Cells(wks1.Rows.Count, 1).End(xlUp).Row j = wks2.Cells(1, wks2.Columns.Count).End(xlToLeft).Column For iColumn = 1 To nRows lLast = Application.Max(lLast, wks1.Cells(wks1.Rows.Count, _ iColumn).End(xlUp).Row) Next iColumn If lLast < nRows Then MsgBox "Less than nnCols rows", vbOKOnly Exit Sub End If For i = 1 To lLast Step nRows wks1.Range("A" & i & ":nCols" & i + nnCols).Copy wks2.Cells(1, j) j = wks2.Cells(1, wks2.Columns.Count).End(xlToLeft).Column + 1 ' To cut data to sheet 2 'wks1.Range("A" & i & ":nCols" & i + nnCols).Cut wks2.Cells(1, j) 'j = wks2.Cells(1, wks2.Columns.Count).End(xlToLeft).Column + 1 Next wks2.Activate Columns("A:Z").HorizontalAlignment = xlCenter Application.Columns("A:Z").AutoFit wks1.Activate Set wks1 = Nothing Set wks2 = Nothing End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
InputBox for column letter problem
Hi Howard,
Am Tue, 21 Jan 2014 19:32:52 -0800 (PST) schrieb L. Howard: wks1.Range("A" & i & ":nCols" & i + nnCols).Copy wks2.Cells(1, j) change the line above to: wks1.Range("A" & i & ":" & nCols & i + nnCols).Copy wks2.Cells(1, j) Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
InputBox for column letter problem
On Wednesday, January 22, 2014 12:19:46 AM UTC-8, Claus Busch wrote:
Hi Howard, Am Tue, 21 Jan 2014 19:32:52 -0800 (PST) schrieb L. Howard: wks1.Range("A" & i & ":nCols" & i + nnCols).Copy wks2.Cells(1, j) change the line above to: wks1.Range("A" & i & ":" & nCols & i + nnCols).Copy wks2.Cells(1, j) Regards Claus B. Right on!! I was thinking the problem was with the InputBox not the syntax of the code line. Works great! Thanks Claus. Regards, Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change InputBox Range Selection to Column Letter Selection | Excel Programming | |||
How do I turn excel columns from column number to column letter? | Setting up and Configuration of Excel | |||
How to replace column letter in refferences with a function using the old column letter? | Links and Linking in Excel | |||
How to replace column letter in refferences with a function using the old column letter? | Excel Worksheet Functions | |||
column header changed from letter to number, how return to letter | Excel Discussion (Misc queries) |