CODEWITHSUNDEEP
vbaexcel
Kavit Kumar
Kavit Kumar

Reputation: 13

VBA SUMIF Function changing sum_ranges criteria

I am new to VBA. I am trying to use a loop to carry out a sumif function. I want the function to change its sum_range criteria as it goes through.

I tried using the following code first:

While cls <= num_cols
    While rws <= num_rows
        Cells(rws, cls).Value = "=SUMIF('BW YTD - Act&Plan'!$C:$C,A" & rws & ",'BW YTD - Act&Plan'!D:D)"
        rws = rws + 1
    Wend
    rws = 7
    cls = cls + 1
Wend

As you notice in the code, the sum_range is D:D. I want to be able to loop through and change it to E:E the next time and keep going until I have reached the last column I have.

I hard coded it as follows: 

While rws <= num_rows
    Cells(rws, 2).Value = "=SUMIF('BW YTD - Act&Plan'!$C:$C,A" & rws & ",'BW YTD - Act&Plan'!D:D)"
    rws = rws + 1
Wend
rws = 7
While rws <= num_rows
    Cells(rws, 3).Value = "=SUMIF('BW YTD - Act&Plan'!$C:$C,A" & rws & ",'BW YTD - Act&Plan'!E:E)"
    rws = rws + 1
Wend
rws = 7
While rws <= num_rows
    Cells(rws, 4).Value = "=SUMIF('BW YTD - Act&Plan'!$C:$C,A" & rws & ",'BW YTD - Act&Plan'!F:F)"
    rws = rws + 1
Wend
rws = 7
While rws <= num_rows
    Cells(rws, 5).Value = "=SUMIF('BW YTD - Act&Plan'!$C:$C,A" & rws & ",'BW YTD - Act&Plan'!G:G)"
    rws = rws + 1
Wend
rws = 7

Any advice would be greatly appreciated.

Upvotes: 1

Views: 181

Answers (1)

Cohan
Cohan

Reputation: 4544

You can use a for loop and the chr() function convert an integer to a letter. The (cls + 64 + 2) allows you to say cls = 2 for "B" and then adding 64 gets you 66 which is the ASCII character for "B". To offset so if cls = 2 will give you "D", just add the extra 2. (You can of course combine it so it's chr(cls + 66), but i wanted to demonstrate why it would be 66.)

While cls <= num_cols
    While rws <= num_rows
        Cells(rws, cls).value = "=SUMIF('BW YTD - Act&Plan'!$C:$C,A" & rws & _
              ",'BW YTD - Act&Plan'!" & Chr(cls + 64 + 2) & ":" & Chr(cls + 64 + 2) & ")"
        rws = rws + 1
    Wend
    rws = 7
Wend

Upvotes: 1

Related Questions