Sum Formula Macro - just some minor tweaks

boldcode · Nov 4, 2011

Hi All,

I have the following VBA code which works fine, but I want to modify certain parts of the code. This VBA code is basically a formula that when executed it populates the calculated results into Column N of my spreadsheet. Below you will find the VBA code and the parts of the code I need assistance with:

Code:

Sub Sum_Cal()
  Range("M2", Range("M" & Rows.Count).End(xlUp)).Offset(, 1).Formula = _
      "=IF(K2=0,""n/a"",IF(K2=2,""other"",IF(K2=1,L2+((L2/M2)*SUMPRODUCT(($A$2:$A$65356=A2)*($L$2:$L$65356)*($K$2:$K$65356=0))))))"
  End Sub

First Change I would Like:
From This: (($A$2:$A$65356=A2)
To something like this: (($A$2: to last cell in Column A with data=A2)

Second Change I would Like:
From This: ($L$2:$L$65356)
To something like this: ($L$2:to last cell in Column L with data)

Third Change I would Like:
From This: ($K$2:$K$65356=0))))))
To something like this: ($K$2:to last cell in Column K with data=0))))))

Let me know if you need additional explanation,

-BC

Shope · Nov 4, 2011

what version of excel are you using? in VBA using the code

Application.CountA(Range("A:A"))

will give you the total number of points with data in a specific column (column A here). My suggestion would be to use this number as a variable to determine the last cell in a column with data. HTH.

Shope.

boldcode · Nov 4, 2011

Shope,

I am using Excel 2010. I would use what you suggested except Row 1 is reserved for Column titles. I started the formula in Row 2 because that is where the data that's to be calculated begins. Based on your suggestion what would the code look like.

e.g. $A$2
$L$2
$K$2

Code:

Sub Sum_Cal()
  Range("M2", Range("M" & Rows.Count).End(xlUp)).Offset(, 1).Formula = _
      "=IF(K2=0,""n/a"",IF(K2=2,""other"",IF(K2=1,L2+((L2/M2)*SUMPRODUCT(($A$2:$A$65356=A2)*($L$2:$L$65356)*($K$2:$K$65356=0))))))"
  End Sub

Shope · Nov 4, 2011

as long as the data is in numbers the counta function should not return the row data in the count. If it does simply dim a variable as the counta (integer) and add 1 to it

boldcode · Nov 4, 2011

Shope,

I think I get what you are saying can you give an example of what the code would look like just to avoid confusion.

Here is my code:

Code:

Sub Sum_Cal()
  Range("M2", Range("M" & Rows.Count).End(xlUp)).Offset(, 1).Formula = _
      "=IF(K2=0,""n/a"",IF(K2=2,""other"",IF(K2=1,L2+((L2/M2)*SUMPRODUCT(($A$2:$A$65356=A2)*($L$2:$L$65356)*($K$2:$K$65356=0))))))"
  End Sub

Shope · Nov 4, 2011

something along the lines of this

Code:

Sub Sum_Cal()


Dim intA, intL, intK As Integer

intA = Application.CountA(Range("A:A"))
intL = Application.CountA(Range("L:L"))
intK = Application.CountA(Range("K:K"))




  Range("M2", Range("M" & Rows.Count).End(xlUp)).Offset(, 1).Formula = _
      "=IF(K2=0,""n/a"",IF(K2=2,""other"",IF(K2=1,L2+((L2/M2)*SUMPRODUCT(($A$2:$A$" & intA & " =A2)*($L$2:$L$" & intL & ")*($K$2:$K$" & intK & "=0))))))"
  End Sub

boldcode · Nov 4, 2011

I will try it out and let you know.

Thanks for your help.

Shope · Nov 4, 2011

No Problem, Please let me know how it works.

boldcode · Nov 4, 2011

Shope,

It seems like it's working so far, I will let you know if I get any errors.

Thanks for your help.

Shope · Nov 4, 2011

No Problem, glad I could help.
If you get an error, Please PM me. Otherwise, take care.

Sum Formula Macro - just some minor tweaks

boldcode

Active Member

Excel Facts

Shope

New Member

boldcode

Active Member

Shope

New Member

boldcode

Active Member

Shope

New Member

boldcode

Active Member

Shope

New Member

boldcode

Active Member

Shope

New Member

Similar threads

Forum statistics

Share this page

Sum Formula Macro - just some minor tweaks

Active Member

Excel Facts

New Member

Active Member

New Member

Active Member

New Member

Active Member

New Member

Active Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock