Using a variable as a column

Question

I'm making a macro to collect data from multiple Excel files and combine them into one where I can then Plot them all on the same Graph.

Having issues using variables to show which columns to paste to.

cq = ColumnLetter(n)
cp = ColumnLetter(n + 1)
Columns("cq:cp").Select

Where n is changing by 3 each time it loops. It has been pasting all the data in the columns CQ and CP.

I know this is a basic problem and the Code is messy as hell, I don't typically use Excel VBA.

Function ColumnLetter(ByVal intColumnNumber)
        Dim sResult
        intColumnNumber = intColumnNumber - 1
        If (intColumnNumber >= 0 And intColumnNumber < 26) Then
            sResult = Chr(65 + intColumnNumber)
        ElseIf (intColumnNumber >= 26) Then
            sResult = ColumnLetter(CLng(intColumnNumber \ 26)) _
                    & ColumnLetter(CLng(intColumnNumber Mod 26 + 1))
        Else
            Err.Raise 8, "Column()", "Invalid Column #" & CStr(intColumnNumber + 1)
        End If
        ColumnLetter = sResult
End Function

'Loop through each Excel file in folder
  Do While myFile <> ""
    'Set variable equal to opened workbook
      Set wb = Workbooks.Open(Filename:=myPath & myFile)
      cq = ColumnLetter(n)
      cp = ColumnLetter(n + 1)
      Columns("A:B").Select
      Selection.Copy
      Windows("Results.xlsx").Activate

      Columns("cq:cp").Select
      ActiveSheet.Paste

    'Change First Worksheet
      wb.Worksheets(1).Range("A1") = ColumnLetter(n)
      wb.Worksheets(1).Range("B1") = Left(myFile, 9)


    'Save and Close Workbook
      wb.Close SaveChanges:=True

    'Get next file name
      myFile = Dir
      n = n + 3
  Loop

Columns("cq:cp).Select you're missing a quote. Besides you would only have to paste to range("CP1") , not the entire columns. This is a Function() not a Sub() selects probably wouldn't work very well. — Davesexcel
– Davesexcel, Commented Nov 9, 2015 at 19:01
"...messy as hell..." -- I recommend you clean it up. (A) You are likely to find a problem that you can solve on your own. (B) You will better serve the friends who are going to explain how this should work. — Smandoli
– Smandoli, Commented Nov 9, 2015 at 19:03

MatthewD · Accepted Answer · 2015-11-09 19:01:25Z

2

Here is a function to get the column letter.

Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function

Change

Columns("cq:cp).Select

to

Columns(cq & ":" & cp).Select

edited Nov 9, 2015 at 19:01

answered Nov 9, 2015 at 18:54

MatthewD

6,8215 gold badges24 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Smandoli Over a year ago

Recommend also Option Explicit at top of module and run "Compile". From what I'm seeing, that may reveal some issues.

MatthewD Over a year ago

@Smandoli is right. Always use Option Explicit. It will force you to declare your variables.

Tim Williams · Accepted Answer · 2015-11-09 19:07:04Z

2

Function ColumnLetter(ByVal intColumnNumber)
    ColumnLetter = Replace(Cells(1, intColumnNumber).address(false,false),"1","")
End Function

for the rest:

Dim wsRes As Worksheet
Set wsRes = Workbooks("Results.xlsx").Worksheets("sheetNameHere")

Do While myFile <> ""

    Set wb = Workbooks.Open(Filename:=myPath & myFile)

    With wb.Worksheets(1)
        .Range("A:B").Copy wsRes.Cells(1, n)
        .Range("A1").Value = ColumnLetter(n)
        .Range("B1").Value = Left(myFile, 9)
    End With

    wb.Close SaveChanges:=True 'Save and Close Workbook

    'Get next file name
    myFile = Dir
    n = n + 3
Loop

edited Nov 9, 2015 at 19:07

answered Nov 9, 2015 at 19:00

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Collectives™ on Stack Overflow

Using a variable as a column

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related