CODEWITHSUNDEEP
excelvba
Pyroz
Pyroz

Reputation: 240

Using a variable as a column

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

Upvotes: 0

Views: 48

Answers (2)

Tim Williams
Tim Williams

Reputation: 166835

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

Upvotes: 2

MatthewD
MatthewD

Reputation: 6801

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

Upvotes: 2

Related Questions