PresidentEvil
New Member
 Joined
 Jan 2, 2021
 Messages
 10
 Office Version

 2016
 Platform

 Windows
Hi there,
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:
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.
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.
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.