+ Reply to Thread
Results 1 to 7 of 7

Loop function SUMIFS

Hybrid View

  1. 09-05-2013, 08:53 AM #1
    jnh0
    jnh0 is offline
    Registered User
    Join Date
    08-07-2013
    Location
    Saudi Arabia
    MS-Off Ver
    Excel 2007 - 2010
    Posts
    73

    Loop function SUMIFS

    Hello everyone,

    Can you help me build this loop for the next function? Thanks!!

    'SUMIFS FOR PT
    Range("CX2").Select
    ActiveCell.FormulaR1C1 = "=SUMIFS(C[-101],C[-99],RC[-4],C[-100],"""")"
    Range("CX3").Select
    ActiveCell.FormulaR1C1 = "=SUMIFS(C[-101],C[-98],RC[-4],C[-100],"""")"
    Range("CX4").Select
    ActiveCell.FormulaR1C1 = "=SUMIFS(C[-101],C[-97],RC[-4],C[-100],"""")"
    Range("CX5").Select
    ActiveCell.FormulaR1C1 = "=SUMIFS(C[-101],C[-96],RC[-4],C[-100],"""")"
    Range("CX6").Select
    ActiveCell.FormulaR1C1 = "=SUMIFS(C[-101],C[-95],RC[-4],C[-100],"""")"
    Range("CX7").Select
    ActiveCell.FormulaR1C1 = "=SUMIFS(C[-101],C[-94],RC[-4],C[-100],"""")"
    Range("CX8").Select
    ActiveCell.FormulaR1C1 = "=SUMIFS(C[-101],C[-93],RC[-4],C[-100],"""")"
    Range("CX9").Select
    ActiveCell.FormulaR1C1 = "=SUMIFS(C[-101],C[-92],RC[-4],C[-100],"""")"
    Range("CX10").Select
    ActiveCell.FormulaR1C1 = "=SUMIFS(C[-101],C[-91],RC[-4],C[-100],"""")"
    Range("CX11").Select
    ActiveCell.FormulaR1C1 = "=SUMIFS(C[-101],C[-90],RC[-4],C[-100],"""")"
    Range("CX12").Select
    It goes the same until:
    ActiveCell.FormulaR1C1 = "=SUMIFS(C[-101],C[-45],RC[-4],C[-100],"""")"
    Reply With QuoteRegister To Reply
  2. 09-05-2013, 09:01 AM #2
    amotto11
    amotto11 is offline
    Forum Contributor
    Join Date
    08-22-2011
    Location
    Texas, United States
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    516

    Re: Loop function SUMIFS

    Use something like this
    dim counter as long
dim count as long
count = 99
     for counter = 2 to 12 'just change the 12 as needed
          range("CX"&counter).formulaR1C1 = "=SUMIFS(C[-101],C[" & count & "],RC[-4],C[-100],"""")"
          count = count - 1
     next
    Regards,
    amotto

    If I helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Reply With QuoteRegister To Reply
  3. 09-05-2013, 09:59 AM #3
    jnh0
    jnh0 is offline
    Registered User
    Join Date
    08-07-2013
    Location
    Saudi Arabia
    MS-Off Ver
    Excel 2007 - 2010
    Posts
    73

    Re: Loop function SUMIFS

    Thank you very much!! It works.

    And when it change rows and columns? how do you do it? lets say in here: 

        ActiveCell.Offset(1, 2).Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=IF(((COUNTIF(RC60:RC69,R2C94))>0),R2C98,"""")"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=IF(((COUNTIF(RC60:RC69,R3C94))>0),R3C98,"""")"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=IF(((COUNTIF(RC60:RC69,R4C94))>0),R4C98,"""")"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=IF(((COUNTIF(RC60:RC69,R5C94))>0),R5C98,"""")"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=IF(((COUNTIF(RC60:RC69,R6C94))>0),R6C98,"""")"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=IF(((COUNTIF(RC60:RC69,R7C94))>0),R7C98,"""")"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=IF(((COUNTIF(RC60:RC69,R8C94))>0),R8C98,"""")"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=IF(((COUNTIF(RC60:RC69,R9C94))>0),R9C98,"""")"
    ActiveCell.Offset(0, 1).Range("A1").Select
    Reply With QuoteRegister To Reply
  4. 09-05-2013, 12:12 PM #4
    amotto11
    amotto11 is offline
    Forum Contributor
    Join Date
    08-22-2011
    Location
    Texas, United States
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    516

    Re: Loop function SUMIFS

    try this:

    dim count as long
Application.cutcopymode = False
activecell.offset(1,2).formulaR1C1 = "=IF(((COUNTIF(RC60:RC69,R2C94))>0),R2C98,"""")" 'i had to place this out of the loop because your offset is different from the rest
for count = 2 to 9
activecell.offset(0,1).formulaR1C1 = "=IF(((COUNTIF(RC60:RC69,R3C94))>0),R" & count & "C98,"""")"
    Reply With QuoteRegister To Reply
  5. 09-05-2013, 12:12 PM #5
    amotto11
    amotto11 is offline
    Forum Contributor
    Join Date
    08-22-2011
    Location
    Texas, United States
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    516

    Re: Loop function SUMIFS

    try this:

    dim count as long
Application.cutcopymode = False
activecell.offset(1,2).formulaR1C1 = "=IF(((COUNTIF(RC60:RC69,R2C94))>0),R2C98,"""")" 'i had to place this out of the loop because your offset is different from the rest
for count = 2 to 9
     activecell.offset(0,1).formulaR1C1 = "=IF(((COUNTIF(RC60:RC69,R3C94))>0),R" & count & "C98,"""")"
next
    Reply With QuoteRegister To Reply
  6. 09-05-2013, 12:28 PM #6
    jnh0
    jnh0 is offline
    Registered User
    Join Date
    08-07-2013
    Location
    Saudi Arabia
    MS-Off Ver
    Excel 2007 - 2010
    Posts
    73

    Re: Loop function SUMIFS

    Thank you, I really appreciate it!!, I had to change some parts cuz it wasnt working that way but you gave me the idea!! thanks a lot.

    at the end was like this: 

    ActiveCell.Offset(1, 2).Range("A1").Select

For count = 3 To 10
    ActiveCell.FormulaR1C1 = "=IF(((COUNTIF(RC60:RC69,R" & count & "C95))>0),R" & count & "C99,"""")"
    ActiveCell.Offset(0, 1).Range("A1").Select
Next
    Reply With QuoteRegister To Reply
  7. 09-05-2013, 12:35 PM #7
    amotto11
    amotto11 is offline
    Forum Contributor
    Join Date
    08-22-2011
    Location
    Texas, United States
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    516

    Re: Loop function SUMIFS

    oh yes, hah. mine probably never moved cells, so you do need that select line in the for loop. I am glad you got it fixed. Unlike in your case, most of the time you do not need to use select lines. Your other code was pretty messy, it looked like excel created it from a macro builder, which is fine, but will run slower in the long run.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] NEED HELP! SUMIFS function
    By thedefense in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-25-2012, 08:47 PM
  2. [SOLVED] SUMIFS function?
    By jed38 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2012, 04:33 PM
  3. OR function in SUMIFS
    By skysurfer in forum Excel General
    Replies: 6
    Last Post: 05-09-2012, 02:41 AM
  4. Excel 2007 : SUMIFS function & #VALUE error
    By arangoa79 in forum Excel General
    Replies: 7
    Last Post: 11-19-2009, 06:00 PM
  5. Need help with SUMIFS function please
    By mTriniDee in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-04-2008, 11:50 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1