[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 244

# Insert formula to range

I have the attached code which work well but I would like to be able to now alter this  so that from a designated point in the insert the formula changes
The attached code will run and insert the formula but when it reached it will then insert the below formulas

sht.Range("a37").FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)+1"
Application.Calculation = xlCalculationManual
'   Set Range
Set rng1 = Range([j10], [o36])

'''Set rng1 = Range([q2], [q2].End(xlDown))
'''
'''Set rng2 = Range([p2], Cells([q1].Value * 7 + 1, 16))
'Set rng2 = Range("q2:q" & ActiveSheet.Range("q" & ActiveSheet.Rows.Count).End(xlUp).Row).Resize(, 7)
'   Apply Formula

rng1.Offset(0, 0).FormulaR1C1 = "=IF(SUM(R[1]C[-1]+R8C9)=0,"""",SUM(R[1]C[-1]+R8C9))"
rng1.Offset(1, 0).Value = ""
rng1.Offset(2, 0).Value = ""
rng1.Offset(0, 1).FormulaR1C1 = =IF(SUM(R[2]C[-2]+R9C9)=0,"""",SUM(R[2]C[-2]+R9C9))
rng1.Offset(1, 1).Value = ""
rng1.Offset(2, 1).Value = ""
rng1.Offset(1, 2).Value = ""
rng1.Offset(2, 2).Value = ""
rng1.Offset(1, 3).Value = ""
rng1.Offset(2, 3).Value = ""
rng1.Offset(1, 4).Value = ""
rng1.Offset(2, 4).Value = ""
rng1.Offset(1, 5).Value = ""
rng1.Offset(2, 5).Value = ""

``````sht.Range("a37").FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)+1"
Application.Calculation = xlCalculationManual
'   Set Range
Set rng1 = Range([j10], [o36])

'''Set rng1 = Range([q2], [q2].End(xlDown))
'''
'''Set rng2 = Range([p2], Cells([q1].Value * 7 + 1, 16))
'Set rng2 = Range("q2:q" & ActiveSheet.Range("q" & ActiveSheet.Rows.Count).End(xlUp).Row).Resize(, 7)
'   Apply Formula

rng1.Offset(0, 0).FormulaR1C1 = "=IF(SUM(R[1]C[-1]+R[4]C[-1])=0,"""",SUM(R[1]C[-1]+R[4]C[-1]))"
rng1.Offset(1, 0).Value = ""
rng1.Offset(2, 0).Value = ""
rng1.Offset(0, 1).FormulaR1C1 = "=IF(SUM(R[2]C[-2]+R[5]C[-2])=0,"""",SUM(R[2]C[-2]+R[5]C[-2]))"
rng1.Offset(1, 1).Value = ""
rng1.Offset(2, 1).Value = ""
rng1.Offset(1, 2).Value = ""
rng1.Offset(2, 2).Value = ""
rng1.Offset(1, 3).Value = ""
rng1.Offset(2, 3).Value = ""
rng1.Offset(1, 4).Value = ""
rng1.Offset(2, 4).Value = ""
rng1.Offset(1, 5).Value = ""
rng1.Offset(2, 5).Value = ""
``````
0
sjvenz
• 3
• 3
1 Solution

Commented:
You need to be a bit clearer.
What were you wanting to insert below the Formula?

and if you could provide the whole code.
you have referenced to "sht" but its not in your Code.
0

Author Commented:
Sorry
I've attached the workbook

But what I'm trying to do is apply a formula to the range required but from a certain point apply a different set of formulas.

What I've done in the code is apply it to the whole range and then run it again with the separate formula, it is a long way round but seems to work OK in applying the formauls but it applies it to all the rows in the range what I need it to do is not to apply to any hidden rows.

I've rem out the bulk of the procedure while I'm trying to get the hidden rows worked out.

When opening the macros select Admin_Fromula (should be Admin_Formula - typeo error). The sub LINKS-DELETE is theone I'm working on
RO-Diagram-Template-Tool.xls
0

Commented:
There is a Password on the Module.

It would be possible with a Select Case
something like the Attached
``````Select Case i
Case 1
'Formula Type 1
Case 2
'Formula Type 2
End Select

OR

Select Case Cell.Row
Case is > 15
'Formula Type 1
Case Else
'Formula Type 2
End Select
``````
0

Author Commented:
pc0402b7
0

Commented:
Ive got the Attached.

it looks through rng1, if its Hidden, it skips that Cell,if its the last row in rng1, it will put in the other formula.
Test it out.

``````For Each c In rng1

With c
If .EntireRow.Hidden Or .EntireColumn.Hidden Then
Else
'If NOT Last Row
.Offset(0, 0).FormulaR1C1 = "=IF(SUM(R[1]C[-1]+R[4]C[-1])=0,"""",SUM(R[1]C[-1]+R[4]C[-1]))"
.Offset(0, 1).FormulaR1C1 = "=IF(SUM(R[2]C[-2]+R[5]C[-2])=0,"""",SUM(R[2]C[-2]+R[5]C[-2]))"
Else
'If IS Last Row
.Offset(0, 0).FormulaR1C1 = "=IF(SUM(R[1]C[-1]+R8C9)=0,"""",SUM(R[1]C[-1]+R8C9))"
.Offset(0, 1).FormulaR1C1 = "=IF(SUM(R[2]C[-2]+R9C9)=0,"""",SUM(R[2]C[-2]+R9C9))"
End If
End If
End With
Next c
``````
0

Author Commented:
thanks
0

## Featured Post

• 3
• 3
Tackle projects and never again get stuck behind a technical roadblock.