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.
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.
In case the first two numbers of the original cell >= 22 or <= 04 AND if the cell on the right contains either "DA", "DR", "LA", "LR" or "EG" add " ND" at the end of the new string. Otherwise if <= 05 AND the right cells contains either "DA", "DR", "LA", "LR" or "EG" add " SV" to the end of it, else add nothing.
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.
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("Scripting.FileSystemObject")
'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 "*.xls" Then
worksheetName = Replace(objFile.Name, ".xls", "")
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("C2").Cells.End(xlDown).Row
For k = 3 To k = LastRw
For l = 2 To l = 33
If Len(ws2.Cells(k + 31, l)) <> 0 Then
If Trim(ws2.Cells(k, 1 + 31)) = "DA" _
Or Trim(ws2.Cells(k, 1 + 31)) = "DR" _
Or Trim(ws2.Cells(k, 1 + 31)) = "LA" _
Or Trim(ws2.Cells(k, 1 + 31)) = "LR" _
Or Trim(ws2.Cells(k, 1 + 31)) = "EG" Then
If CInt(Trim(Left(ws2.Cells(k, l), 2))) >= 22 _
Or CInt(Trim(Left(ws2.Cells(k, l), 2))) <= 4 Then
newString = Trim(ws2.Cells(k, 1 + 31)) & Trim(ws2.Cells(k, l)) & " ND"
Else
If CInt(Trim(Left(ws2.Cells(k, l), 2))) <= 5 Then
newString = Trim(ws2.Cells(k, 1 + 31)) & Trim(ws2.Cells(k, l)) & " SV"
Else
newString = Trim(ws2.Cells(k, 1 + 31)) & 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 "Update complete. Check last update timestamps of files."
ThisWorkbook.Sheets("Access Control").Activate
'turn on automatic calculations
Application.Calculation = xlAutomatic
End Sub
For k = 3 To k = LastRw
For l = 2 To l = 33
These two lines are incorrect syntax. When you write For k = 3 to k = LastRw, this evaluates to For k = 3 to False or For k = 3 to 0, so your loop will never run. This needs to be:
For k = 3 To LastRw
For l = 2 To 33