CODEWITHSUNDEEP
excelvba
xyz
xyz

Reputation: 2300

Assistance with comparing cells with If formula

I am Comparing cells in column D and if they match paste the value of the previous cell in column B into the next cell in column B if they do not match paste the value of the subseqent cell in column A into the cell in column B

e.g

IF(D2=D3,B2,A2+1)" but this is not working after running on the 1st sequence of cells in D I get #Valve!" for the rest of column B

I am sure this is the problem IF(D2=D3,B2,A2+1)" in-particular the A2+1 reference but not sure how to call it

(Sorry if this was unclear)

Thanks 

Sub TargetId()
Dim lRow As Long
Dim ws As Worksheet

Set ws = Sheets("UnPivot")
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

 Range("B1").FormulaR1C1 = "Source"
 Range("B2").FormulaR1C1 = [A2].Value

With ws
lRow = ws.Range("D" & .Rows.Count).End(xlUp).Row

   With .Range("B3:B" & lRow)
        .Formula = "=IF(D2=D3,B2,A2+1)"
        .Value = .Value
   End With
End With


End Sub

Upvotes: 0

Views: 30

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35863

As follow up from comments, this one works:

With .Range("B3:B" & lRow)
    .Formula = "=IF(D2=D3,B2,A3)"
    .Value = .Value
End With

Upvotes: 1

Related Questions