title: VBA Concatenate multiple ranges from data dump
body: <p>My goal is to concatenate two ranges of cells with each other and add conditionally one more piece of string, based on files added as sheets to the workbook.</p>
  <p>First range starts at cell C2 and ends at the final row of column AF.
  The cells to concatenate are always located on the same row and 31 cells to the right.</p>
  <p>In case the first two numbers of the original cell &gt;= 22 or &lt;= 04 AND if the cell on the right contains either &quot;DA&quot;, &quot;DR&quot;, &quot;LA&quot;, &quot;LR&quot; or &quot;EG&quot; add &quot; ND&quot; at the end of the new string.
  Otherwise if &lt;= 05 AND the right cells contains either &quot;DA&quot;, &quot;DR&quot;, &quot;LA&quot;, &quot;LR&quot; or &quot;EG&quot; add &quot; SV&quot; to the end of it, else add nothing.</p>
  <p>What complicates the situation is that the above mentioned For-loops are added on the back of a For-loop to find files to add as data dump into the Workbook. Somehow the For-loop for the concatenate procedure is being skipped completely and I can't figure out why.</p>
  <pre><code>Sub Get_Files()
  'turn off automatic calculations
  Application.Calculation = xlManual
  
  Dim objFSO As Object
  Dim objFolder As Object
  Dim objFile As Object
  Dim worksheetName As String
  Dim i As Integer, j As Integer, k As Integer, l As Integer
  Dim LastRw As Long
  Dim x As Workbook, y As Workbook
  Dim ws1 As Worksheet, ws2 As Worksheet
  Dim newString As String
  
  
  
  'Create an instance of the FileSystemObject
  Set objFSO = CreateObject(&quot;Scripting.FileSystemObject&quot;)
  'Get the folder object
  Set objFolder = objFSO.GetFolder(Cells(1, 2).Value)
  i = 1
  'loops through each file in the directory and prints their names and path
  For Each objFile In objFolder.files
  'print file name
  Cells(i + 10, 1) = objFile.Name
  'print file last updated
  Cells(i + 10, 2) = objFile.DateLastModified
  
  
  'print file path without xls
  If objFile.Name Like &quot;*.xls&quot; Then
  
      worksheetName = Replace(objFile.Name, &quot;.xls&quot;, &quot;&quot;)
     
      Set x = Workbooks.Open(objFile.path)
      Set y = ThisWorkbook
      
      'Check if worksheet exists
      
          For j = 1 To y.Worksheets.Count
          If y.Worksheets(j).Name = worksheetName Then
              exists = True
          End If
          Next j
          
          If Not exists Then
              y.Worksheets.Add.Name = worksheetName
          End If
          
          
   
      Set ws1 = x.Sheets(worksheetName)
      Set ws2 = y.Sheets(worksheetName)
      
      
      ws1.Cells.Copy ws2.Cells
      x.Close True
      
      LastRw = ws2.Range(&quot;C2&quot;).Cells.End(xlDown).Row
      
              For k = 3 To k = LastRw
                  For l = 2 To l = 33
                      
                      If Len(ws2.Cells(k + 31, l)) &lt;&gt; 0 Then
                          
                          If Trim(ws2.Cells(k, 1 + 31)) = &quot;DA&quot; _
                          Or Trim(ws2.Cells(k, 1 + 31)) = &quot;DR&quot; _
                          Or Trim(ws2.Cells(k, 1 + 31)) = &quot;LA&quot; _
                          Or Trim(ws2.Cells(k, 1 + 31)) = &quot;LR&quot; _
                          Or Trim(ws2.Cells(k, 1 + 31)) = &quot;EG&quot; Then
                             
                             If CInt(Trim(Left(ws2.Cells(k, l), 2))) &gt;= 22 _
                             Or CInt(Trim(Left(ws2.Cells(k, l), 2))) &lt;= 4 Then
                             newString = Trim(ws2.Cells(k, 1 + 31)) &amp; Trim(ws2.Cells(k, l)) &amp; &quot; ND&quot;
                             
                             Else
                                 If CInt(Trim(Left(ws2.Cells(k, l), 2))) &lt;= 5 Then
                                 newString = Trim(ws2.Cells(k, 1 + 31)) &amp; Trim(ws2.Cells(k, l)) &amp; &quot; SV&quot;
                                 Else
                                     newString = Trim(ws2.Cells(k, 1 + 31)) &amp; Trim(ws2.Cells(k, l))
                                 End If
                             End If
                              
                          End If
                      Else
                      
                      newString = Trim(ws2.Cells(k, l))
                      
                      End If
                      
                      ws2.Cells(k, l).Value = newString
                      
                      l = l + 1
                      
                  Next l
                  
                  k = k + 1
                  
              Next k
      
      ws2.Visible = xlSheetHidden
      
      exists = False
     
  End If
  
  i = i + 1
  Next objFile
  
  
  MsgBox &quot;Update complete. Check last update timestamps of files.&quot;
  
  ThisWorkbook.Sheets(&quot;Access Control&quot;).Activate
  
  'turn on automatic calculations
  Application.Calculation = xlAutomatic
  
  End Sub
  </code></pre>