How to look up for each cell of a range to each cell of another range?-CodePudding

The problem in the code is that I'm making equal a cell to a range of cells…

I want to compare each cell of range A in a Worksheet called “Ax” to each cell of range B in a Worksheet called “Bx”.

Sub NvxDetail()

Dim Ax As Worksheet: Set Ax = Workbooks("MODÈLE DE PROPOSITION DE CONTRAT DE SOUS-TRAITANCE.").Worksheets("Proposition de contrat")
Dim By As Worksheet: Set By = Workbooks("Suivi contrat fact").Worksheets("Détail")

Dim last_row As Integer: last_row = Ax.Cells(Ax.Rows.Count, 3).End(xlUp).Row
Dim arng As Range: Set arng = Ax.Range(Ax.Cells(13, 1), Ax.Cells(last_row, 1))
Dim newcttnbr As Range: Set newcttnbr = By.Cells(ByLastRow, 1)

Ax.Range("C12").Copy
    newcttnbr.PasteSpecial xlPasteValues
       Application.CutCopyMode = False
       ActiveCell.Select

For i = 4 To 104
If By.Cells(2, i).Value = arng.Value Then
   By.Cells(2, i).Value = arng.Offset(0, 5)
End If
Next

End Sub

Updated full code :

Sub NvxDetail()

Dim Ax As Worksheet: Set Ax = Workbooks("MODÈLE DE PROPOSITION DE CONTRAT DE SOUS-TRAITANCE.").Worksheets("Proposition de contrat")
Dim By As Worksheet: Set By = Workbooks("Suivi contrat fact").Worksheets("Détail")

Dim arng As Range: Set arng = Ax.Range(Ax.Cells(13, 1), Ax.Cells(last_row, 1))
Dim newcttnbr As Range: Set newcttnbr = By.Cells(ByLastRow, 1)

    Ax.Range("C12").Copy
    newcttnbr.PasteSpecial xlPasteValues
       Application.CutCopyMode = False
       ActiveCell.Select


For i = 4 To 104
    For Each c In arng
        If By.Cells(2, i).Value = c.Value Then
           By.Cells(2, i).Value = c.Offset(0, 5)   'update value
           Exit For
        End If
    Next
Next


End Sub

CodePudding user response：

You need sth like this - if you want to compare by.cells(2,i) against all values of arng. First value that is equal will jump into the update row and then exits the for-loop that compares the values

Dim c As Range
For i = 4 To 104
    For Each c In arng
        If By.Cells(2, i).value = c.value Then
           By.Cells(2, i).value = c.Offset(0, 5)   'update value
           Exit For
        End If
    Next
Next