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

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

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

0
sjvenz
Asked:
sjvenz
  • 3
  • 3
1 Solution
 
bromy2004Commented:
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
 
sjvenzAuthor 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
 
bromy2004Commented:
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

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
sjvenzAuthor Commented:
Sorry password is
pc0402b7
0
 
bromy2004Commented:
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 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

Open in new window

0
 
sjvenzAuthor Commented:
thanks
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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