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

[PresidentEvil]

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.

 

[Saurabhj]

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]

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.

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]

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]

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.

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]

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.

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]

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]

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]

Add this line before each formula code block
Set rngErrors = Nothing

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

 

[PresidentEvil]

Add this line before each formula code block
Set rngErrors = Nothing

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!!