Right now this does everything I need however... I need it to post values in the next column over the next time it goes through the loop so essentially k +1.
It will currently find values from each consecutive file and then paste them over each other in same column
Sub test()
Dim s1 As Worksheet
Dim r1 As Integer
Dim c1 As Integer
Dim BRarr
Set s1 = Workbooks("Filename1.xlsm").Worksheets("sheet1")
r1 = s1.UsedRange.Rows.Count
MsgBox r1
c1 = s1.UsedRange.Columns.Count
BRarr = Array("01", "02", "03", "04", "05", "07", "08", "09", "10", "11",
"12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23",
"24", "25", "26", "28", "29", "30", "31", "32", "33", "34", "35", "52",
"60", "62", "64", "70", "71", "74", "75", "78", "88")
For i = LBound(BRarr) To Ubound(BRarr)
Application.Workbooks.Open
("Filepath....Filename2 " & BRarr(i) & ".xlsx")
Workbooks("filename2 " & BRarr(i) & ".xlsx").Activate
Dim j As Integer
Dim q As Integer
Dim k As Integer
k = 7
For j = 2 To r1
q=Application.WorksheetFunction.CountIf(ActiveWorkbook.Worksheets("Sheet1").Range("M:M"), Workbooks("filename1.xlsm").Worksheets("Sheet1").Cells(j, 3))
Workbooks("filename1.xlsm").Worksheets("Sheet1").Cells(j, k).Value = q
Next j
Workbooks("Filename2 " & BRarr(i) & ".xlsx").Close
Next i
End Sub
Aucun commentaire:
Enregistrer un commentaire