lundi 9 avril 2018

Trying to compare a cell to a column in another workbook and count number of instances it appears in the column

Trying to compare a cell(column A in workbook 1) to a column (column C in workbook 2) and count number of instances it appears in the column C. and run it for each cell in the same column of column A in workbook 1. additionally pasting the count in the column B of workbook1.

This is what I have so far...

Sub valueassign()
'
' valueassign Macro
'
Dim s1 As Worksheet
Dim wb As Workbook
Dim s2 As Worksheet

Dim i As Integer
Dim j As Integer
Dim k As Integer

Set s1 = Worksheets("sheet1")
Set wb = Application.Workbooks.Open("StockAvailable.xlsx")
Set s2 = wb.Worksheets("FilteredData")

j = s1.UsedRange.Rows.Count
k = s2.UsedRange.Rows.Count

For i = 2 To k
If Application.WorksheetFunction.CountIf(s2.Range(Cells(2, 5), s2.Cells(k, 
1)), s1.Cells(i, 3).Value)
    s1.Cells(i, 5).Value =

Else
    s1.Cells(i, 5).Value =
End If
Next i

End Sub

Aucun commentaire:

Enregistrer un commentaire