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.

Solved

Posted on 2009-12-21

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)

'Debug.Print rng2.Address

' 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(0, 2).FormulaR1C1 = =IF(COUNTIF(RC[-10]:R[3]C[-4],'Link Data'!R6C1) + COUNTIF(R7C2:R9C8,'Link Data'!R6C1)=0,"""",COUNTIF(RC[-10]:R[3]C[-4],'Link Data'!R6C1) + COUNTIF(R7C2:R9C8,'Link Data'!R6C1))

rng1.Offset(1, 2).Value = ""

rng1.Offset(2, 2).Value = ""

rng1.Offset(0, 3).FormulaR1C1 = "=IF(SUMPRODUCT(NOT(ISERROR(SEARCH('Link Data'!R4C1,RC[-11]:R[2]C[-5])))*1)+SUMPRODUCT(NOT(ISERROR(SEARCH('Link Data'!R4C1,R7C2:R9C8)))*1)=0,"""",SUMPRODUCT(NOT(ISERROR(SEARCH('Link Data'!R4C1,RC[-11]:R[2]C[-5])))*1)+SUMPRODUCT(NOT(ISERROR(SEARCH('Link Data'!R4C1,R7C2:R9C8)))*1))"

rng1.Offset(1, 3).Value = ""

rng1.Offset(2, 3).Value = ""

rng1.Offset(0, 4).FormulaR1C1 = "=IF(SUMPRODUCT(NOT(ISERROR(SEARCH(LEFT('Link Data'!R8C1,2),RC[-12]:R[2]C[-6])))*1)+SUMPRODUCT(NOT(ISERROR(SEARCH(LEFT('Link Data'!R8C1,2),R7C2:R9C8)))*1)=0,"""",SUMPRODUCT(NOT(ISERROR(SEARCH(LEFT('Link Data'!R8C1,2),RC[-12]:R[2]C[-6])))*1)+SUMPRODUCT(NOT(ISERROR(SEARCH(LEFT('Link Data'!R8C1,2),R7C2:R9C8)))*1))"

rng1.Offset(1, 4).Value = ""

rng1.Offset(2, 4).Value = ""

rng1.Offset(0, 5).FormulaR1C1 = "=IF(SUMPRODUCT(NOT(ISERROR(SEARCH('Link Data'!R10C1,RC[-13]:R[2]C[-7])))*1)+SUMPRODUCT(NOT(ISERROR(SEARCH('Link Data'!R10C1,R7C2:R9C8)))*1)=0,"""",SUMPRODUCT(NOT(ISERROR(SEARCH('Link Data'!R10C1,RC[-13]:R[2]C[-7])))*1)+SUMPRODUCT(NOT(ISERROR(SEARCH('Link Data'!R10C1,R7C2:R9C8)))*1))"

rng1.Offset(1, 5).Value = ""

rng1.Offset(2, 5).Value = ""

The attached code will run and insert the formula but when it reached it will then insert the below formulas

sht.Range("a37").FormulaR1

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).En

'Debug.Print rng2.Address

' Apply Formula

rng1.Offset(0, 0).FormulaR1C1 = "=IF(SUM(R[1]C[-1]+R8C9)=0

rng1.Offset(1, 0).Value = ""

rng1.Offset(2, 0).Value = ""

rng1.Offset(0, 1).FormulaR1C1 = =IF(SUM(R[2]C[-2]+R9C9)=0,

rng1.Offset(1, 1).Value = ""

rng1.Offset(2, 1).Value = ""

rng1.Offset(0, 2).FormulaR1C1 = =IF(COUNTIF(RC[-10]:R[3]C[

rng1.Offset(1, 2).Value = ""

rng1.Offset(2, 2).Value = ""

rng1.Offset(0, 3).FormulaR1C1 = "=IF(SUMPRODUCT(NOT(ISERRO

rng1.Offset(1, 3).Value = ""

rng1.Offset(2, 3).Value = ""

rng1.Offset(0, 4).FormulaR1C1 = "=IF(SUMPRODUCT(NOT(ISERRO

rng1.Offset(1, 4).Value = ""

rng1.Offset(2, 4).Value = ""

rng1.Offset(0, 5).FormulaR1C1 = "=IF(SUMPRODUCT(NOT(ISERRO

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)
'Debug.Print rng2.Address
' 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(0, 2).FormulaR1C1 = "=IF(COUNTIF(RC[-10]:R[5]C[-4],'Link Data'!R6C1)=0,"""",COUNTIF(RC[-10]:R[5]C[-4],'Link Data'!R6C1))"
rng1.Offset(1, 2).Value = ""
rng1.Offset(2, 2).Value = ""
rng1.Offset(0, 3).FormulaR1C1 = "=IF(SUMPRODUCT(NOT(ISERROR(SEARCH('Link Data'!R4C1,RC[-11]:R[5]C[-5])))*1)=0,"""",SUMPRODUCT(NOT(ISERROR(SEARCH('Link Data'!R4C1,RC[-11]:R[5]C[-5])))*1))"
rng1.Offset(1, 3).Value = ""
rng1.Offset(2, 3).Value = ""
rng1.Offset(0, 4).FormulaR1C1 = "=IF(SUMPRODUCT(NOT(ISERROR(SEARCH(LEFT('Link Data'!R8C1,2),RC[-12]:R[5]C[-6])))*1)=0,"""",SUMPRODUCT(NOT(ISERROR(SEARCH(LEFT('Link Data'!R8C1,2),RC[-12]:R[5]C[-6])))*1))"
rng1.Offset(1, 4).Value = ""
rng1.Offset(2, 4).Value = ""
rng1.Offset(0, 5).FormulaR1C1 = "=IF(SUMPRODUCT(NOT(ISERROR(SEARCH('Link Data'!R10C1,RC[-13]:R[5]C[-7])))*1)=0,"""",SUMPRODUCT(NOT(ISERROR(SEARCH('Link Data'!R10C1,RC[-13]:R[5]C[-7])))*1))"
rng1.Offset(1, 5).Value = ""
rng1.Offset(2, 5).Value = ""
```

6 Comments

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.

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

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

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 c.Address <> rng1(rng1.Count).Address Then
'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]))"
.Offset(0, 2).FormulaR1C1 = "=IF(COUNTIF(RC[-10]:R[5]C[-4],'Link Data'!R6C1)=0,"""",COUNTIF(RC[-10]:R[5]C[-4],'Link Data'!R6C1))"
.Offset(0, 3).FormulaR1C1 = "=IF(SUMPRODUCT(NOT(ISERROR(SEARCH('Link Data'!R4C1,RC[-11]:R[5]C[-5])))*1)=0,"""",SUMPRODUCT(NOT(ISERROR(SEARCH('Link Data'!R4C1,RC[-11]:R[5]C[-5])))*1))"
.Offset(0, 4).FormulaR1C1 = "=IF(SUMPRODUCT(NOT(ISERROR(SEARCH(LEFT('Link Data'!R8C1,2),RC[-12]:R[5]C[-6])))*1)=0,"""",SUMPRODUCT(NOT(ISERROR(SEARCH(LEFT('Link Data'!R8C1,2),RC[-12]:R[5]C[-6])))*1))"
.Offset(0, 5).FormulaR1C1 = "=IF(SUMPRODUCT(NOT(ISERROR(SEARCH('Link Data'!R10C1,RC[-13]:R[5]C[-7])))*1)=0,"""",SUMPRODUCT(NOT(ISERROR(SEARCH('Link Data'!R10C1,RC[-13]:R[5]C[-7])))*1))"
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))"
.Offset(0, 2).FormulaR1C1 = "=IF(COUNTIF(RC[-10]:R[3]C[-4],'Link Data'!R6C1) + COUNTIF(R7C2:R9C8,'Link Data'!R6C1)=0,"""",COUNTIF(RC[-10]:R[3]C[-4],'Link Data'!R6C1) + COUNTIF(R7C2:R9C8,'Link Data'!R6C1))"
.Offset(0, 3).FormulaR1C1 = "=IF(SUMPRODUCT(NOT(ISERROR(SEARCH('Link Data'!R4C1,RC[-11]:R[2]C[-5])))*1)+SUMPRODUCT(NOT(ISERROR(SEARCH('Link Data'!R4C1,R7C2:R9C8)))*1)=0,"""",SUMPRODUCT(NOT(ISERROR(SEARCH('Link Data'!R4C1,RC[-11]:R[2]C[-5])))*1)+SUMPRODUCT(NOT(ISERROR(SEARCH('Link Data'!R4C1,R7C2:R9C8)))*1))"
.Offset(0, 4).FormulaR1C1 = "=IF(SUMPRODUCT(NOT(ISERROR(SEARCH(LEFT('Link Data'!R8C1,2),RC[-12]:R[2]C[-6])))*1)+SUMPRODUCT(NOT(ISERROR(SEARCH(LEFT('Link Data'!R8C1,2),R7C2:R9C8)))*1)=0,"""",SUMPRODUCT(NOT(ISERROR(SEARCH(LEFT('Link Data'!R8C1,2),RC[-12]:R[2]C[-6])))*1)+SUMPRODUCT(NOT(ISERROR(SEARCH(LEFT('Link Data'!R8C1,2),R7C2:R9C8)))*1))"
.Offset(0, 5).FormulaR1C1 = "=IF(SUMPRODUCT(NOT(ISERROR(SEARCH('Link Data'!R10C1,RC[-13]:R[2]C[-7])))*1)+SUMPRODUCT(NOT(ISERROR(SEARCH('Link Data'!R10C1,R7C2:R9C8)))*1)=0,"""",SUMPRODUCT(NOT(ISERROR(SEARCH('Link Data'!R10C1,RC[-13]:R[2]C[-7])))*1)+SUMPRODUCT(NOT(ISERROR(SEARCH('Link Data'!R10C1,R7C2:R9C8)))*1))"
End If
End If
End With
Next c
```

Title | # Comments | Views | Activity |
---|---|---|---|

VBA to delete range of cells in row NOT entire row | 11 | 36 | |

Revise Excel macro to include missing information | 4 | 19 | |

Updating Pivot Table within VBA | 5 | 29 | |

Function or formula to identify the last repeated character in a cell in Excell | 9 | 23 |

This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

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

Connect with top rated Experts

**16** Experts available now in Live!