Entering Specific Text Until Last Row

[reberryjr]

I'm trying to update the contents of column E with "BKI Hold", starting with cell E2, as long as the cells in column A are not null. I want to continue entering that value, until the last row. I've tried a couple of routes. The first snippet only entered "BKI Hold" in E2, but not subsequent cells. The second snippet hasn't entered any information.

'mws.Range("E2") = "BKI Hold"
'mws.Range("E2").Select
'Selection.AutoFill Destination:=mws.Range("E2" & ":E" & mLR)

For i = 1 To mLR
    If mws.Range("A" & i).Value <> 0 Then Range("E" & i).Value = "BKI Hold"
Next I

 

[Fluff]

How about

Sub FillE()
   With Range("E2", Range("A" & Rows.Count).End(xlUp).Offset(, 4))
      .Value = Evaluate(Replace(Replace("if(@a<>"""",""BKI Hold"",@)", "@a", .Offset(, -4).Address), "@", .Address))
   End With
End Sub

 

[reberryjr]

@Fluff -
Thanks as always for the help. I copy and pasted your snippet in, and no updates were made to the sheet. I realized that I actually can't start the code at E2 every time, as I'll be adding additional records to this sheet, that will get a different entry. So I tried to use the snippet below yours, to no avail. I have to admit that I've never used the Evaluate or Replace functions before, and the @ in the code is new to me as well.

Sub CopyBKIHolds()
Application.ScreenUpdating = False
Dim m, s As Workbook
Dim mws, sws As Worksheet
Dim i, mLR As Long
Set m = ThisWorkbook
Set mws = ThisWorkbook.Sheets("BKI")
mLR = mws.Range("A" & Rows.Count).End(xlUp).Row
mQueueLR = mws.Range("E" & Rows.Count).End(xlUp).Row
Set s = Workbooks.Open("Hyperlink")
Set sws = s.Worksheets("BKFS Hold")
sLR = sws.Range("A" & Rows.Count).End(xlUp).Row
If sws.Range("A2").Value = "" Then
    Exit Sub
Else
sws.Range(Cells(2, 2), Cells(sLR, 2)).Copy
    mws.Range("A2").PasteSpecial Paste:=xlPasteValues
sws.Range(Cells(2, 7), Cells(sLR, 7)).Copy
    mws.Range("B2").PasteSpecial Paste:=xlPasteValues
sws.Range(Cells(2, 8), Cells(sLR, 8)).Copy
    mws.Range("C2").PasteSpecial Paste:=xlPasteValues
sws.Range(Cells(2, 11), Cells(sLR, 11)).Copy
    mws.Range("D2").PasteSpecial Paste:=xlPasteValues
    
'mws.Range("E2") = "BKI Hold"
'mws.Range("E2").Select
'Selection.AutoFill Destination:=mws.Range("E2" & ":E" & mLR)
'For i = 1 To mLR
'    If mws.Range("A" & i).Value <> 0 Then Range("E" & i).Value = "BKI Hold"
'Next i
   With Range("E2", Range("A" & Rows.Count).End(xlUp).Offset(, 4))
      .Value = Evaluate(Replace(Replace("if(@a<>"""",""BKI Hold"",@)", "@a", .Offset(, -4).Address), "@", .Address))
   End With
'With Range("E" & mQueueLR, Range("A" & Rows.Count).End(xlUp).Offset(, 4))
'    .Value = Evaluate(Replace(Replace("if(@a<>"""",""BKI Hold"",@)", "@a", .Offset(, -4).Address), "@", .Address))
'End With
End If
s.Close SaveChanges = False
Call Formatting
Application.ScreenUpdating = True
End Sub

 

[Fluff]

Assuming that you want this to work on the mws sheet try

   With Mws.Range("E" & mQueueLR, Mws.Range("A" & Rows.Count).End(xlUp).Offset(, 4))
      .Value = Evaluate(Replace(Replace("if(@a<>0,""BKI Hold"",@)", "@a", .Offset(, -4).Address), "@", .Address))
   End With

 

[reberryjr]

@Fluff imagine that...I forgot to reference the actual worksheet. SMH! Thank you so much....YET AGAIN!

 

[Fluff]

Glad to help & thanks for the feedback.