CODEWITHSUNDEEP
excelvba
Sumoshark
Sumoshark

Reputation: 1

Excel VBA If rngcell= and next cell= then this cell= and next cell=

I am looking to compare two ranges of cells, First objective is to see if there is any differences i.e. if this cell doesnt appear in this range. Second if the cell does appear has the next cell got this value.

Currently i have been able to output any cell in the first range that doesnt appear in the second:

For Each rngCell In Range("F7:F500")
    If WorksheetFunction.CountIf(Range("H7:H500"), rngCell) = 0 Then
        Range("O" & Rows.Count).End(xlUp).Offset(1) = rngCell
    End If
Next

I am very lost on how to approach the second part though so far I have tried the following:

For Each rngCell In Range("B6:B500")
    If WorksheetFunction.CountIf(Range("H7:H500"), rngCell) = 0 Then
    Set r2 = Range("E7:E500")
    Else If (Range("H7:H500")) and WorksheetFunction.CountIf(r2("vault")) Then
    Range("O" & Rows.Count).End(xlUp).Offset(1) = rngCell

Essentially, I am looking for an output with all the cells which are in the first range but not the second and all the cells that are in the first range and second but the next cell contains a specific value.

Upvotes: 0

Views: 671

Answers (1)

donPablo
donPablo

Reputation: 1959

Here is something to try...

I like the Range/xlUp/Offset(1) to continuously add to the bottom of the list.

(needs preceeding Columns("O:O").ClearContents)

Correct me if I am wrong.

Lets clarify 2nd Objective -- all the cells that are in the first range and second but the next cell contains a specific value.

  If  rngCell("B6:B500") IS in Range("H7:H500") 
  AND rngCell("B6:B500") IS in Range("E7:E500") MATCH (not CountIf) to get the row number
  AND if MatchedRowNum > 0 then
  AND "vault"            IS in Range("E7:E500").Offset(MatchedRowNum + 1)

Upvotes: 0

Related Questions