Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

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
0
fitaliano
Asked:
fitaliano
  • 2
1 Solution
 
dlmilleCommented:
for each blank row (in its entirety) or blank cells in each row?


Dave
0
 
fitalianoAuthor Commented:
Blank Cell.

F
0
 
dlmilleCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now