CODEWITHSUNDEEP
vbaexcelrange
Nick
Nick

Reputation: 39

How to find the row number of a cell value that matches any given value

In this situation, i am checking each cell in the range of "F". and looking to see if each cell matches any of the cells in a separate range "D". If it does I want to know the row number in which this cell exists and copy it (as well as information in other column of the same row) into another sheet. if it can't find a match it will print the cell it could not find and beside it "0". How can i find the value of the row in which this data exists?

Dim lastrow As Integer
lastrow = Range("F65336").End(xlUp).Row
Dim C As Integer
C = 2
For Each cell In Range("F2:F" & lastrow)
If cell.Value = Range("D2:" & lastrow) Then
    sheet2.Range("B" & C).Value = Sheet1.Range("D" & C).Value
    Sheet1.Range("D" & C).Value = Sheet1.Range("F" & VALUE_OF_ROW_OF_MATCHING_CELL).Value
    sheet2.Range("C" & C).Value = Sheet1.Range("H" & VALUE_OF_ROW_OF_MATCHING_CELL).Value
Else
    sheet2.Range("B" & C).Value = Sheet1.Range("D" & C).Value
    Sheet1.Range("C" & C).Value = 0
End If
C = C + 1
Next cell

Upvotes: 0

Views: 3058

Answers (1)

mr.Reband
mr.Reband

Reputation: 2430

How could this work?

If cell.Value = Range("D2:" & lastrow) Then

It is attempting to compare a single cell with a collection of cells, and that would never evaluate to true.

Since you mention you are looking to see if each cell matches any of the cells in a separate range "D", I think you need another for each in your code: 

Dim lastrow As Integer
lastrow = Range("F65336").End(xlUp).Row

Dim fCell, dCell
For Each fCell In Range("F2:F" & lastrow)
    Dim fRow As Integer
    fRow = fCell.Row

    'prepopulate with default, not-found values
    Sheet2.Range("B" & fRow).Value = Sheet1.Range("D" & fRow).Value
    Sheet1.Range("C" & fRow).Value = 0

    For Each dCell In Range("D2:D" & lastrow)
        If fCell.Value = dCell.Value Then
            Dim dRow As Integer
            dRow = dCell.Row
            Sheet2.Range("B" & fRow).Value = Sheet1.Range("D" & dRow).Value
            Sheet1.Range("D" & fRow).Value = Sheet1.Range("F" & dRow).Value
            Sheet2.Range("C" & fRow).Value = Sheet1.Range("H" & dRow).Value
        End If
    Next dCell

Next fCell

Note that the above assumes that the ranges span the same set of rows. If this is indeed the case, then I would suggest looking into using vlookups instead.

For example, you could use column G to perform a vlookup. Put this in G2 and fill down: 

 =IFERROR(VLOOKUP(F2,D:D,1,FALSE), "")

Then, in Sheet2, Cell B2, you could reference G2: 

 =IF(Sheet1!G2="","",Sheet1!D2)

...and so on.

Upvotes: 1

Related Questions