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").FormulaR1 C1 = "=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).En d(xlUp).Ro w).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]+R 9C9))
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(ISERRO R(SEARCH(' Link Data'!R4C1,RC[-11]:R[2]C[- 5])))*1)+S UMPRODUCT( NOT(ISERRO R(SEARCH(' Link Data'!R4C1,R7C2:R9C8)))*1) =0,"""",SU MPRODUCT(N OT(ISERROR (SEARCH('L ink Data'!R4C1,RC[-11]:R[2]C[- 5])))*1)+S UMPRODUCT( NOT(ISERRO R(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(ISERRO R(SEARCH(L EFT('Link Data'!R8C1,2),RC[-12]:R[2] C[-6])))*1 )+SUMPRODU CT(NOT(ISE RROR(SEARC H(LEFT('Li nk Data'!R8C1,2),R7C2:R9C8))) *1)=0,"""" ,SUMPRODUC T(NOT(ISER ROR(SEARCH (LEFT('Lin k Data'!R8C1,2),RC[-12]:R[2] C[-6])))*1 )+SUMPRODU CT(NOT(ISE RROR(SEARC H(LEFT('Li nk Data'!R8C1,2),R7C2:R9C8))) *1))"
rng1.Offset(1, 4).Value = ""
rng1.Offset(2, 4).Value = ""
rng1.Offset(0, 5).FormulaR1C1 = "=IF(SUMPRODUCT(NOT(ISERRO R(SEARCH(' Link Data'!R10C1,RC[-13]:R[2]C[ -7])))*1)+ SUMPRODUCT (NOT(ISERR OR(SEARCH( 'Link Data'!R10C1,R7C2:R9C8)))*1 )=0,"""",S UMPRODUCT( NOT(ISERRO R(SEARCH(' Link Data'!R10C1,RC[-13]:R[2]C[ -7])))*1)+ SUMPRODUCT (NOT(ISERR OR(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 = ""
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
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
There is a Password on the Module.
It would be possible with a Select Case
something like the Attached
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
ASKER
Sorry password is
pc0402b7
pc0402b7
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
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.