mercredi 11 avril 2018

Trying to Print Values in a the next column

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