# Sum Formula Macro - just some minor tweaks

#### boldcode

##### Active Member
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

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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.

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``````

Last edited:
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

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``````

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``````

I will try it out and let you know.

No Problem, Please let me know how it works.

Shope,

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

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

Replies
1
Views
808
Replies
9
Views
1K
Replies
1
Views
877
Replies
6
Views
1K
Replies
3
Views
1K

1,203,502
Messages
6,055,772
Members
444,822
Latest member
Hombre

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back