Link to home
Start Free TrialLog in
Avatar of sjvenz
sjvenz

asked on

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)
'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 = ""

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 = ""

Open in new window

Avatar of bromy2004
bromy2004
Flag of Australia image
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.
Avatar of sjvenz
sjvenz

ASKER

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
Avatar of bromy2004
bromy2004
Flag of Australia image
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

Open in new window

Avatar of sjvenz
sjvenz

ASKER

Sorry password is
pc0402b7
ASKER CERTIFIED SOLUTION
Avatar of bromy2004
bromy2004
Flag of Australia image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sjvenz
sjvenz

ASKER

thanks