Excel Cell value the same as above, then-CodePudding

Somehow I got myself in the situation that I need to work out a code in excel VBA. Unfortunately this is not my expertise and now I am stuck.

I am trying to check is the value in a cell in Colom B is the same as a cell in Colom B on another row, Y places lower/higher. Y being a Value in cell "A8". IF the values are the same then the value in Colom P of the same row has to get the same value as in Colom R of the same row. ELSE Colom Q of that row has to get the same value as in Colom R of the same row. (In the schematic below, Y=2, so colom B1 check 2 places lower B3 is the same so data in R1 should also be in P1. continuing the same idea B4 compared with B6 does not have the same value so the Q4 should have the same value as R4)

ColomA ColomB ColomP ColomQ ColomR
          1                    x         
          2                    x        
   2      1                    x        
          2                    x    
          30                   x      
          40                   x         
          50                   x
          60                   x

ColomA ColomB ColomP ColomQ ColomR
         1       x             x         
         2       x             x        
   2     1       x             x        
         2       x             x    
         30            x       x      
         40            x       x         
         50            x       x
         60            x       x

(representation of the input (top) and the desired result (bottom))

I tried multiple things but didn't get very far.. the last I tried seen below.

For iRow = Range("B3:B2502").Rows.Count To 1 Step -1
If Cells(iRow   2, 2).Value = Cells(iRow   Y, 2) Then
Cells(iRow   2, 15).Value = Cells(iRow   2, 17)

Else

Cells(iRow   2, 16).Value = Cells(iRow   2, 17)

End If
Next

End Sub

And some variations with ranges but this, fairly obviously, did not work ..

I would truly appreciate any advice.

Cheers,

CodePudding user response：

Paste this in cell P1 then copy the formula down the rows:

=IF(OR(IF(ISERROR(B1=OFFSET(B1,$A$8,0)),False,B1=OFFSET(B1,$A$8,0)),IF(ISERROR(B1=OFFSET(B1,-$A$8,0)),False,B1=OFFSET(B1,-$A$8,0))),R1,"")

Paste this in cell Q1 then copy the formula down the rows:

=IF(OR(IF(ISERROR(B1<>OFFSET(B1,$A$8,0)),False,B1<>OFFSET(B1,$A$8,0)),IF(ISERROR(B1<>OFFSET(B1,-$A$8,0)),False,B1<>OFFSET(B1,-$A$8,0))),R1,"")

CodePudding user response：

If I am understanding your code as a reference for what is being asked (the narrative language leaves me confused), I think you're trying to do something like this (mock-up untested):

With Sheets(1)
    Dim comparativeValue as Long
    comparativeValue = .cells(8,1).value
    Dim lastRow as Long
    lastRow = .cells(.rows.count,2).end(xlup).row
    Dim iterator as Long
    For iterator = 2 to lastRow step 1
        Dim pasteColumn as Long
        Select Case True
            Case comparativeValue <= .Cells(i,2).Value
                pasteColumn = 16
            Case comparativeValue > .Cells(i,2).Value
                pasteColumn = 17
        End Select
        .cells(i,pasteColumn).value = .cells(i,18).value
    Next iterator
End With

This compares your comparativeValue in A8 to each value in Column B, and based on ">=" or "<" the pasteColumnn will receive the value from Column R.