vba help to apply formula down till end of row data and where ever is blank in range.

Dear experts


I have excel formulas in excel sheet with in a range of B2:E2 i have to pull down formula with out pasting on already existing data in that range.
data in A:A column get blank and get the row end some where in A:A !!
if i run the code apply formulas from A:A column end of row data till there where ever blank in that range B2:E2 that only make apply formulas down.
once data end row in A:A column make the running code stop..

msg box should be "task completed"

find the attachment you may have understand !!!

Hi.
Try this:

Sub test()
Dim lastcell

With Worksheets("Sheet1")
    Set lastcell = .Cells(.Rows.Count, "a").End(xlUp)
    lastrow = Application.WorksheetFunction.Max(lastcell.Row)
End With

For Each cell In Range("b1:b" & lastrow)
    If cell.Value = "" Then
        cell.Formula = "=IFERROR(VLOOKUP(" & cell.Offset(0, -1).Address & ",Sheet2!A:E,2,FALSE)," & ")"
    Else
    End If
Next cell

For Each cell In Range("c1:c" & lastrow)
    If cell.Value = "" Then
        cell.Formula = "=IFERROR(VLOOKUP(" & cell.Offset(0, -2).Address & ",Sheet2!A:E,3,FALSE)," & ")"
    Else
    End If
Next cell

For Each cell In Range("d1:d" & lastrow)
    If cell.Value = "" Then
        cell.Formula = "=IFERROR(VLOOKUP(" & cell.Offset(0, -3).Address & ",Sheet2!A:E,4,FALSE)," & ")"
    Else
    End If
Next cell

For Each cell In Range("e1:e" & lastrow)
    If cell.Value = "" Then
        cell.Formula = "=IFERROR(VLOOKUP(" & cell.Offset(0, -4).Address & ",Sheet2!A:E,5,FALSE)," & ")"
    Else
    End If
Next cell

'=IFERROR(VLOOKUP(Sheet1!A30;Sheet2!A:E;2;FALSE);" ")

End Sub

Clear the cells in your table first because you have some formulas with #REF in them. The macro only checks for blanks so it hops over these leaving you with some (seemingly) blank cells. If you just clear everything in columns B:E before running the macro it should populate your entire table.

can you apply same format code to this sheet where ever the formula i tried but i couldnt complete it
find the attachment

any help on this????