Solved

transform getformula in its value

Posted on 2012-03-09
3
171 Views
Last Modified: 2012-03-09
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
Comment
Question by:fitaliano
  • 2
3 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 37703900
for each blank row (in its entirety) or blank cells in each row?


Dave
0
 

Author Comment

by:fitaliano
ID: 37703922
Blank Cell.

F
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37703932
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SUMPRODUCT with criteriya 5 25
Excel 2016 - Black cell borders 11 25
ADD New Entries 7 15
Add a range in an Excel graph 5 24
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

1 Experts available now in Live!

Get 1:1 Help Now