asked on

Excel Insert formula in column A & B for any row where column D is not null/blank

I have a formula in A2 and B2 and I am currently using the following to auto-fill the formula from A2 "=M2-7" into column A and "=L2" into column B of any row where column D is not blank/null. The problem is that it's not very consistent. sometimes it works and sometimes it doesn't.

Seems like it would be better to just write the formula into each cell where D is not blank/null?

This is how I am doing it now.

Sub Autofill ()

Sheets("Engines").Select
Range("A2").Select

Dim lastrow As Long
lastrow = Worksheets("Engines").Cells(Rows.Count, "D").End(xlUp).Row
Selection.AutoFill Destination:=Range("A2:A" & lastrow)
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B" & lastrow)

End Sub

Thanks,

swjtx99

Saqib Husain

In what way does it NOT work?

Norie

Does this work any better?

Sub MyAutofill ()
Dim lastrow As Long

    With Worksheets("Engines")
           lastrow = .Cells(Rows.Count, "D").End(xlUp).Row
           .Range("A2:B2").Copy .Range("A2:B" & lastrow)
    End With
End Sub

Open in new window

swjtx99

ASKER

Hi ssaqibh,

Very strange sometimes it just deletes everything in columns a and b. I've messed with it for hours and can't figure out why. Thought maybe just doing something like formula.r1c1? but I've been googling for examples how to do that and haven't found anything close enough yet.

Hi imnorie,

I think the problem is that the formula I have put into A2 and B2 gets deleted for some unknown reason so not sure your solution would work either. Funny it doesn't happen every time.

Saqib Husain

Can you send us a small sample workbook?

swjtx99

ASKER

Hi,

Ok, I just figured out why the formulas in A2 and B2 are getting deleted.

In a section above, I'm deleting all rows that match a certain criteria in column G. The reason why sometimes it worked and sometimes it didn't is because sometimes the value in G2 matched (and thus got deleted) and sometimes it didn't. I suppose I could have that search/delete happen AFTER I populate columns A and B. Might be simpler than trying to insert the formula and copy it down?

swjtx99

swjtx99

ASKER

Hi,

Ok, I moved the code and all is well.

Thanks for taking a look at my question.

swjtx99

ASKER CERTIFIED SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

swjtx99

ASKER

I think moving the code as I did solved the problem but your solution also works and I can archive and save for later. Populating a formula into a Cell is a good solution to have.

Thanks for the help.

swjtx99

Excel Insert formula in column A &amp; B for any row where column D is not null/blank

Excel Insert formula in column A & B for any row where column D is not null/blank