transform getformula in its value

I have the following code

    Dim Ecell As Range
    
    For Each Ecell In Range("B4:B100")
            If IsEmpty(Ecell) Then
    
    Range("U8").Select
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-4]C:R[-1]C)+RC[-1]"
    Range("V8").Select
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-4]C:R[-1]C)+RC[-1]"
    Range("W8").Select
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-4]C:R[-1]C)+RC[-1]"
    Range("X8").Select
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-4]C:R[-1]C)+RC[-1]"
    Range("Y8").Select
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-4]C:R[-1]C)+RC[-1]"
    Range("Z8").Select
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-4]C:R[-1]C)+RC[-1]"
    Range("AA8").Select
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-4]C:R[-1]C)+RC[-1]"
    Range("AB8").Select
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-4]C:R[-1]C)+RC[-1]"
    Range("AC8").Select
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-4]C:R[-1]C)+RC[-1]"
    Range("AC8").Select
            
    Next Ecell

Open in new window


I would like to execute it for each blank row I find and not only for row 8
fitalianoAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
dlmilleConnect With a Mentor Commented:
Ok

Try this:

Sub test()
Dim Ecell As Range
Dim rng As Range
Dim r As Range

    For Each Ecell In Range("B4:B100")
        If Trim(Ecell.Value) = vbNullString Then
            For Each r In Range("U" & Ecell.Row, "AC" & Ecell.Row)
                If Trim(r.Value) = vbNullString Then
                    r.FormulaR1C1 = "=SUBTOTAL(9,R[-4]C:R[-1]C)+RC[-1]"
                End If
            Next r
        End If
    Next Ecell
    
            
End Sub

Open in new window


If you post a sample I could confirm, but looks like it worked with the simple sample I created.

Dave
0
 
dlmilleCommented:
for each blank row (in its entirety) or blank cells in each row?


Dave
0
 
fitalianoAuthor Commented:
Blank Cell.

F
0
All Courses

From novice to tech pro — start learning today.