1 line of VBA Code Needed: "IF" Formula

[DougStroud]

Here is my existing code- this was written by ACW and works fantastic. But I need an additional line- After Line:

Cells(i + 1, "AC").Value = 1998

I need to look at col. AA, IF a cell in AA is blank and col. Y on the same row begins w/ ~P then copy the values from AA and AB 1 cell below into the blank cells.
So AA7 is blank and Y7 begins w/ ~P, Copy the values from AA8 and AB8 to AA7 and AB7.


Full Code:

LRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row
  ws1.Range("y5:z" & LRow).Copy
  ws1.Range("y5:z" & LRow).PasteSpecial Paste:=xlPasteValues
  
  For i = Cells(Rows.Count, "D").End(xlUp).Row To 3 Step -1
    If Cells(i, "D") = "NEED PARENT" Then
      Cells(i, "D").EntireRow.Insert
      Cells(i + 1, "D").EntireRow.Copy Destination:=Cells(i, 1)
      Cells(i + 1, "D").Formula = "=IF(LEFT(W" & i + 1 & ",2)=""~P"","""",IF(AND(LEFT(W" & i + 1 & ",2)=""~C"",LEFT(U" & i + 1 & ",8)<>LEFT(U" & i & ",8)),""NEED PARENT"",""""))"
      Cells(i + 1, "D").Copy Destination:=Cells(i, "D")
      Cells(i + 1, "N").ClearContents
      Cells(i + 1, "Z").Value = 0
      Cells(i + 1, "AC").Value = 1998
    End If
  Next i

Thanks--

Doug

 

[Von Pookie]

I haven't tested this--so I'm not quite sure that I have the i and i+1 variables in the correct place, but this should give you an idea, at least:

If Cells(i, "AA") = "" And Left(Cells(i, "Y"), 2) = "~P" Then
       Range(Cells(i + 1, "AA"), Cells(i + 1, "AB")).Copy Range(Cells(i, "AA"), Cells(i, "AB"))
End If

Edit:
And, because I'm apparently not thinking clearly, how about possibily simplifying that bit of code I already posted

If Cells(i, "AA") = "" And Left(Cells(i, "Y"), 2) = "~P" Then
    Cells(i + 1, "AA").Value = Cells(i,"AA").Value
    Cells(i + 1, "AB").Value = Cells(i, "AB").Value
End If

Edited again to fix the extra parenthesis that I forgot to remove in the second example.

 

[DougStroud]

Thanks Kristy,
I will try it. Can we lose the If and End if by placing this lline directly below the last line that has 1998 in it?

 

[Von Pookie]

Not really. If you only want to copy those values IF the 2 columns match your arguments, you're going to have another IF statement. You can rewrite it to leave off the extra End If, though.

Here are both examples rewritten so that they should not require an End If:

This is simply the same code all on one line (which won't require an End If):

If Cells(i, "AA") = "" And Left(Cells(i, "Y"), 2) = "~P" Then Range(Cells(i + 1, "AA"), Cells(i + 1, "AB")).Copy Range(Cells(i, "AA"), Cells(i, "AB"))

This uses the colon (":") between the separate statements to continue and run both resulting statements (to 'copy' the values) on one line:

If Cells(i, "AA") = "" And Left(Cells(i, "Y"), 2) = "~P" Then Cells(i + 1, "AA").Value = Cells(i, "AA").Value: Cells(i + 1, "AB").Value = Cells(i, "AB").Value

 

[DougStroud]

Thanks Kristy,
That is super of you to write it up both ways!

Cheers, and have a great day today.


Doug