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

PresidentEvil

New Member
Joined
Jan 2, 2021
Messages
10
Office Version
  1. 2016
Platform
  1. 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:
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.
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
398
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi Prathik,

When the code finds #Value in any cell, do you need to change formula for those cells or for all cells in column E?

e.g., IF cells E5 and E6 has # value error, code should change formula only for E5 and E6 cells or for all cells in E column

Thanks,
Saurabh
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
You don't need to autofill or Autofilter.
You can apply a formula to a range of cells. Excel will automatically adjust relative cell references for each row.
Use the SpecialCells method to return the range of #VALUE! errors if any.

Maybe something like this.
VBA Code:
Sub MyFormula()
    Dim LastRow As Long, rngErrors As Range
    
    ActiveSheet.AutoFilterMode = False
    LastRow = Range("D" & Rows.Count).End(xlUp).Row
    
    With Range("E2:E" & LastRow)
    
        .FormulaR1C1 = "My formula 1"
        
        On Error Resume Next
        Set rngErrors = .SpecialCells(xlFormulas, xlErrors)
        On Error GoTo 0
        If Not rngErrors Is Nothing Then
            rngErrors.FormulaR1C1 = "My Formula 2"
        End If
        
        On Error Resume Next
        Set rngErrors = .SpecialCells(xlFormulas, xlErrors)
        On Error GoTo 0
        If Not rngErrors Is Nothing Then
            rngErrors.FormulaR1C1 = "My Formula 3"
        End If
            
    End With
    
End Sub
 

PresidentEvil

New Member
Joined
Jan 2, 2021
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
Hi Prathik,

When the code finds #Value in any cell, do you need to change formula for those cells or for all cells in column E?

e.g., IF cells E5 and E6 has # value error, code should change formula only for E5 and E6 cells or for all cells in E column

Thanks,
Saurabh
Only for the cells which has #Value error. :)
 

PresidentEvil

New Member
Joined
Jan 2, 2021
Messages
10
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

You don't need to autofill or Autofilter.
You can apply a formula to a range of cells. Excel will automatically adjust relative cell references for each row.
Use the SpecialCells method to return the range of #VALUE! errors if any.

Maybe something like this.
VBA Code:
Sub MyFormula()
    Dim LastRow As Long, rngErrors As Range
   
    ActiveSheet.AutoFilterMode = False
    LastRow = Range("D" & Rows.Count).End(xlUp).Row
   
    With Range("E2:E" & LastRow)
   
        .FormulaR1C1 = "My formula 1"
       
        On Error Resume Next
        Set rngErrors = .SpecialCells(xlFormulas, xlErrors)
        On Error GoTo 0
        If Not rngErrors Is Nothing Then
            rngErrors.FormulaR1C1 = "My Formula 2"
        End If
       
        On Error Resume Next
        Set rngErrors = .SpecialCells(xlFormulas, xlErrors)
        On Error GoTo 0
        If Not rngErrors Is Nothing Then
            rngErrors.FormulaR1C1 = "My Formula 3"
        End If
           
    End With
   
End Sub
Thank you for the code. Will try this and let you know the outcome.
 

PresidentEvil

New Member
Joined
Jan 2, 2021
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
You don't need to autofill or Autofilter.
You can apply a formula to a range of cells. Excel will automatically adjust relative cell references for each row.
Use the SpecialCells method to return the range of #VALUE! errors if any.

Maybe something like this.
VBA Code:
Sub MyFormula()
    Dim LastRow As Long, rngErrors As Range
   
    ActiveSheet.AutoFilterMode = False
    LastRow = Range("D" & Rows.Count).End(xlUp).Row
   
    With Range("E2:E" & LastRow)
   
        .FormulaR1C1 = "My formula 1"
       
        On Error Resume Next
        Set rngErrors = .SpecialCells(xlFormulas, xlErrors)
        On Error GoTo 0
        If Not rngErrors Is Nothing Then
            rngErrors.FormulaR1C1 = "My Formula 2"
        End If
       
        On Error Resume Next
        Set rngErrors = .SpecialCells(xlFormulas, xlErrors)
        On Error GoTo 0
        If Not rngErrors Is Nothing Then
            rngErrors.FormulaR1C1 = "My Formula 3"
        End If
           
    End With
   
End Sub
That code works fantastically. But one issue though, let say I have added 5 formulas in the code you have given. Daily data doesn't remain the same hence, if today #Value error gets reduced by using 5 formulas, on other day it might get reduced during application of 3rd formula itself.

So is there a way wherein I can stop apply the next formula if #value error stops before using all the formula? I'm now facing issue of others formulas (which are not required ) being applied to the last executed formula.

Ex: if #Value errors is reduced to zeros at formula 3, but still formula 4 & 5 is being applied to formula 3 results hence generating #Value error again.

Thanks a ton!
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384

ADVERTISEMENT

I'm not sure I follow what you had said.

If after Formula 3 is applied to the #VALUE! errors and then there are no more errors after that, then Formulas 4 and 5 are not applied to the sheet.
 

PresidentEvil

New Member
Joined
Jan 2, 2021
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
I'm not sure I follow what you had said.

If after Formula 3 is applied to the #VALUE! errors and then there are no more errors after that, then Formulas 4 and 5 are not applied to the sheet.
You got it correct. But unfortunately, formula 4 & 5 are being applied even after Value error is 0 at formula 3. Not only that, formula 4 & 5 are being applied 'again' to the results of formula 3. Hence it is generating more #Value! Errors
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
Add this line before each formula code block
Set rngErrors = Nothing

Rich (BB code):
Sub MyFormula()
    Dim LastRow As Long, rngErrors As Range
    
    ActiveSheet.AutoFilterMode = False
    LastRow = Range("D" & Rows.Count).End(xlUp).Row
    
    With Range("E2:E" & LastRow)
    
        .FormulaR1C1 = "My formula 1"
        
        On Error Resume Next
        Set rngErrors = .SpecialCells(xlFormulas, xlErrors)
        On Error GoTo 0
        If Not rngErrors Is Nothing Then
            rngErrors.FormulaR1C1 = "My Formula 2"
        End If
        
        Set rngErrors = Nothing
        On Error Resume Next
        Set rngErrors = .SpecialCells(xlFormulas, xlErrors)
        On Error GoTo 0
        If Not rngErrors Is Nothing Then
            rngErrors.FormulaR1C1 = "My Formula 3"
        End If
            
    End With
    
End Sub
 
Solution

PresidentEvil

New Member
Joined
Jan 2, 2021
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
Add this line before each formula code block
Set rngErrors = Nothing

Rich (BB code):
Sub MyFormula()
    Dim LastRow As Long, rngErrors As Range
   
    ActiveSheet.AutoFilterMode = False
    LastRow = Range("D" & Rows.Count).End(xlUp).Row
   
    With Range("E2:E" & LastRow)
   
        .FormulaR1C1 = "My formula 1"
       
        On Error Resume Next
        Set rngErrors = .SpecialCells(xlFormulas, xlErrors)
        On Error GoTo 0
        If Not rngErrors Is Nothing Then
            rngErrors.FormulaR1C1 = "My Formula 2"
        End If
       
        Set rngErrors = Nothing
        On Error Resume Next
        Set rngErrors = .SpecialCells(xlFormulas, xlErrors)
        On Error GoTo 0
        If Not rngErrors Is Nothing Then
            rngErrors.FormulaR1C1 = "My Formula 3"
        End If
           
    End With
   
End Sub
It's working perfectly fine now.

Thank you for all your help. Highly appreciated!!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,229
Messages
5,600,420
Members
414,383
Latest member
kevinlarey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top