vba - AutoFill Formula from ActiveCell till the last cells of other columns having data

Question

My Name is Prathik. I'd like to apologize if this question has already been answered. I didn't find anything about this anywhere on the internet. So posting it here. Please assist.

So I'm writing this code wherein my macro writes a formula in E2 (Range A To D already has data). Then the code automatically Autofill the formula in E column till the last row where the Column D has Data. Below is the code:

Range("E2").Select ActiveCell.FormulaR1C1 = 'My formula Range("E2").Select Selection.AutoFill Destination:=Range("E2:E" & Range("D" & Rows.Count).End(xlUp).Row)

Then it it filters out the column E wherein the cells end up with "#Value" as the formula doesn't have other criteria to it.

Selection.AutoFilter ActiveSheet.Range("A:E").AutoFilter Field:=5, Criteria1:="#VALUE!"

Again the code writes the formula with different criteria to reduce the "#Value" counts. Now obviously there will be hidden cells because the Autofilter has filtered out the data as per the formula.

Now I need a code that help me to select the ActiveCell after E1, possibly E3,E7 or so on...and then write my formula and again autofill the data to the last row where the data is present in D column. And this continues till "#Value" is completely gone. Don't worry about the formula. I have it already covered. I'm only looking for referencing active cells and apply formulas every time data is filtered till "#Value" counts gets reduced to 0

Any help will be very much appreciated. Thank you in advance.

Please not that I don't have programming knowledge, this is just a basic project and you explaining me in leman language will be very helpful for me to learn.

question from:https://stackoverflow.com/questions/65541231/autofill-formula-from-activecell-till-the-last-cells-of-other-columns-having-dat

Answer 1

Waitting for answers