Comparing Cells in Columns and Writing Values to a New Column

November 17, 2021

I am writing a macro to compare the cells of two columns. If the strings contained in the cells being compared match, a value from an adjacent cell is written from a new cell. I tested my loops and they are working, but I cannot figure out the problem when it comes to writing the cell value to the new cell.

Sub Compare()
Dim i As Integer
Dim j As Integer
Dim RowNumberData As Long
Dim RowNumberConstant As Long
Dim DataRange1 As String
Dim ConstantRange1 As String
Dim DataRange2 As String
Dim ConstantRange2 As String
RowNumberData = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
RowNumberConstant = Sheet2.Cells(Rows.Count, 3).End(xlUp).Row
For i = 1 To RowNumberConstant
    Let ConstantRange1 = "C" & i
    Let ConstantRange2 = "D" & i
    For j = 1 To RowNumberData
        Let DataRange1 = "A" & j
        Let DataRange2 = "B" & j
        If StrComp(DataRange1, ConstantRange1, vbTextCompare) = 0 Then
            Sheet2.Range(ConstantRange2).Value = Sheet2.Range(DataRange2).Value
        End If
    Next j
Next i
End Sub

>Solution :

your ...Range# variables are not ranges but strings so you StrComp is comparing "C1" to "A1" Not the values in those cells

Sub Compare()
Dim i As Integer
Dim j As Integer
Dim RowNumberData As Long
Dim RowNumberConstant As Long
Dim DataRange1 As Range
Dim ConstantRange1 As Range
Dim DataRange2 As Range
Dim ConstantRange2 As Range
RowNumberData = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row
RowNumberConstant = Sheet2.Cells(Rows.Count, 3).End(xlUp).Row
For i = 1 To RowNumberConstant
    Set ConstantRange1 = Sheet2.Range("C" & i)
    Set ConstantRange2 = Sheet2.Range("D" & i)
    For j = 1 To RowNumberData
        Set DataRange1 = Sheet2.Range("A" & j)
        Set DataRange2 = Sheet2.Range("B" & j)
        If StrComp(DataRange1, ConstantRange1, vbTextCompare) = 0 Then
            ConstantRange2.Value = DataRange2.Value
            Exit For
        End If
    Next j
Next i
End Sub