Copy formulas based on present value

[HVtech]

Hi,
I've been reading some posts now, but I can't find how I must handle my problem.
I recorded this as a macro, but I need it all formulas in this code to be copied down for every row(A column) where there is some text.
How should this be written?
This is the recorded macro

Sub VulOnkosten()

    Range("B7").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 10092543
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("C7").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]*Boodschappen!R11C7"
    Range("D7").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-3]<>"""",'Wie gaan mee'!R15C9,""-"")"
    Range("E7").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
    Range("F7").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 10092543
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("G7").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""ja"",RC[-2]-RC[-3],RC[-2])"
    Range("G8").Select
End Sub

 

[SkipVought]

hi,

Do you not have data in every row of your table? WHY NOT?

You really do not need VBA in order to copy formulas. You could use the ISBLANK function right on the sheet in an IF function.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[HVtech]

Hi Skip,
From another piece of code there are names copied to A7 and down.
What I want on this sheet, when I call the macro the sheet gets selected, and depending if there is a name in A, the formulas are being set.
Hope its makes sense ..

 

[HVtech]

Got it,
Added this code to the code above and now it doing what I want.
Thanks for support!
Cheers

    Dim i As Long
    Application.ScreenUpdating = False
    i = Cells(Rows.Count, "A").End(xlUp).Row
        
      Range("B7:E7").AutoFill Destination:=Range("B7:E" & i)
      Range("F7").AutoFill Destination:=Range("F7:F" & i)
      Range("G7").AutoFill Destination:=Range("G7:G" & i)
    
End Sub

 

[HVtech]

Hmm fallback..
On itslef the code is working but now I see, when I add names in later stadium (when there is already formulas written) again all is filled down and the values that were there are being rewritten.
This is not supposed to happen.
How do I prevent that?
So I need only the formula and cel properties but not the values.

Any ideas?

 

[SkipVought]

Are you saying that when the formulas recalculate, a different value returns?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[HVtech]

No, I'll try to explain.
Let's call the sheet we try to modify Costs
Fase 1, the sheet is empty
Fase 2, on another sheet I select names i a form and the code behind it put the selected names on the Costs sheet.
The code I have now writes the formulas in the cells I want, all going well. As you can see ther are a few columns I make yellow for writing manually some data.

Then I remember I forgot 1 or more names, and I go back to my form and select some more names.
Again they are written to sheet Costs in A column, as supposed.
BUT! Now it looks like the values from first row are autofilled down, rewriting the original values!
Even the columns that are supposed to be empty are filled down with values taken form the first row.

What I want, is in case I add names later on, just the formulas are written in the appropiate cells, leave the yellow ones blank (but mmake them yellow) and checks if the next row A column has someting written, if yes: write those formulas again.
Thanks for your patience..
Cheers, Hans

 

[SkipVought]

What version Excel?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[HVtech]

2010

 

[SkipVought]

Yes! Your FORMULAS & VALUES in columns B thru G, your FORMULAS & VALUES get filled. This is what YOU programmed!

      Range("B7:G7").AutoFill Destination:=Range("B7:G" & i)

Why don't you CHANGE your table to a Structured Table, and just see what happens when you add you names in column A.

Select any cell in your table and...

Insert > Tables > Table



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[HVtech]

Got it working..
Thanks for your time and support Skip.
This is the code for any other with similair problem:

    Dim i As Long
    i = Cells(Rows.Count, "A").End(xlUp).Row
    
    Application.ScreenUpdating = False
    
    With Range("B7:B" & i & ",F7:G" & i).Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 10092543
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    With Range("C7:C" & i)
        .FormulaR1C1 = "=RC[-1]*R2C11"
        .NumberFormat = _
        "_ [$€-413] * #,##0.00_ ;_ [$€-413] * -#,##0.00_ ;_ [$€-413] * ""-""??_ ;_ @_ "
    End With
    
    Range("D7:D" & i).FormulaR1C1 = "=IF(RC[-3]<>"""",'Wie gaan mee'!R17C10,""-"")"
    
    Range("E7:E" & i).FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
    
    With Range("H7:H" & i)
        .FormulaR1C1 = "=IF(RC[-2]=""ja"",RC[-3]-RC[-4]-RC[-1],RC[-3])"
        .NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
    End With
    
    Application.ScreenUpdating = True

Office 2010

 

[SkipVought]

Do you use Structured Tables?

This is a really great new feature as of Excel 2007.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[HVtech]

No, I'll have a look at that.
Thanks again

Office 2010

 

[SkipVought]

One of the benefits you will see, is that your formulas will propagate to new rows as data is added.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}