We help IT Professionals succeed at work.

# transform getformula in its value

on
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
``````

I would like to execute it for each blank row I find and not only for row 8
Comment
Watch Question

## View Solution Only

Most Valuable Expert 2012
Top Expert 2012

Commented:
for each blank row (in its entirety) or blank cells in each row?

Dave

Commented:
Blank Cell.

F
Most Valuable Expert 2012
Top Expert 2012
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
``````

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

Dave