how to search two different ranges and input answer

Question

this code almost works but im getting a Error "Method 'Range' of object'_Worksheet' failed" when trying to perform the action. any ideas?

Dim k As Range
For Each k In Sheet2.Range("h6:zz6").Cells
    If k = Sheet4.Range("e1").Value Then
        Dim i As Long
        Dim j As Long
        Dim lrow As Long

        For i = 10 To 200
            If Sheet4.Range("B" & i).Value = "" Then
                Exit For
            End If

            For j = 7 To 10000
                If Sheet2.Range("c" & j).Value = "" Then
                    Exit For
                End If

                If Sheet4.Range("B" & i).Text = Sheet2.Range("c" & j).Text Then
                    ***Sheet2.Range(k & j).Value*** = Sheet4.Range("b" & i).Value   
                    Exit For
                End If
            Next j
        Next i

        Exit For
    End If
Next

`k` is a `Range` and `j` is a `Long`. What do you expect `Range(k & j)` to refer to? — BigBen, May 11 '21 at 14:26
j gives me he value "serial number" from sheet2 column "C" row 25, and K gives me what column "Data" is in, which happens to be column "K". so in Cell "K25" i need to place value. does this help? — Byron, May 11 '21 at 14:37

score 0 · Accepted Answer · answered May 12 '21 at 11:11

***Sheet2.Range(k & j).Value***

This part of your loop does not really refer to any cell addresses. It only refers to the number of the iteration itself. Here, for k = 11 and j = 11 it would just yield "(11 & 11)" (of course, it would present an error), not a particular range/cell.

I would try to replace it with:

***Sheet2.Cells(j,k.Column).Value**

In this code snippet, we use the .Cells to give a reference as to which specific cell in the loop we are going to look at.

Dim k As Range
For Each k In Sheet2.Range("h6:zz6").Cells
    If k = Sheet4.Range("e1").Value Then
        Dim i As Long
        Dim j As Long
        Dim lrow As Long

        For i = 10 To 200
            If Sheet4.Range("B" & i).Value = "" Then
                Exit For
            End If

            For j = 7 To 10000
                If Sheet2.Range("c" & j).Value = "" Then
                    Exit For
                End If

                If Sheet4.Range("B" & i).Text = Sheet2.Range("c" & j).Text Then
                    ***Sheet2.Cells(j,k.Column).Value*** = Sheet4.Range("b" & i).Value   
                    Exit For
                End If
            Next j
        Next i

        Exit For
    End If
Next

how to search two different ranges and input answer

1 Answers1