Sum formula in VBA, need help to rewrite this one line of code

[Badnames]

This line is all part of a larger macro, but these are the two important parts. I am trying to do a sum function for a range that changes. I have 'i' dimmed as an integer, 'i' is to change the row as the loop runs. I want to sum a range on the current row the loop is on, but right now I do not know how to write the range so that I can use a syntax similar something like ("A" & i). Can someone see below and let me know how I can rewrite this?


Dim i as integer

Sheets("input").sum(range(("A" & i) : ("D" & i))

 

[Richard Schollar]

Do you want to write a formula to the worksheet or simply calculate the value (for use in your macro)?

If the latter, then you could use something like this (note I've dimmed i as a Long so that you don't get an overflow error if your row is beyond 32768:

Dim i As Long
Dim myValue As DOuble
'more code to assign value to i
'...
With Sheets("Input")
  myValue = Application.WorksheetFunction.Sum(.Range("A" & i & ":D" & i))
  MsgBox myValue
ENd With

 

[Badnames]

I have two worksheets, one is "product" the other is "inputs". If the loop is on row 21 in "inputs", I want to set J13 on "products" to the sum of p21:t21.

Nevermind got the add-in! here is my code:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> mrexcel()<br><br><br> Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>  <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>  <SPAN style="color:#00007F">Dim</SPAN> ProdCount <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>  <SPAN style="color:#00007F">Dim</SPAN> Prod2Count <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><br> i = 21<br> ProdCount = 0<br> Prod2Count = 0<br><br> <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> i <= 120<br>  <SPAN style="color:#00007F">If</SPAN> Range("B" & i) = "Prod" <SPAN style="color:#00007F">Then</SPAN><br>    Worksheets("Product").Activate<br>    Sheets("Product").Range("A" & (14 + ProdCount)) = Sheets("Input").Range("B" & i)<br>    Sheets("Product").Range("C" & (14 + ProdCount)) = Sheets("Input").Range("AA" & i)<br>    Sheets("Product").Range("E" & (14 + ProdCount)) = Sheets("Input").Range("C" & i) & " Mbps"<br>    Sheets("Product").Range("F" & (14 + ProdCount)) = Sheets("Input").Range("AA" & i)<br>    Sheets("Product").Range("J" & (14 + ProdCount)) = Sheets("Input").Application.WorksheetFunction.Sum(Range("p" & i & ":t" & i))<br>    ProdCount = ProdCount + 1<br>    <br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    <SPAN style="color:#00007F">If</SPAN> Range("B" & i) = "Prod2" <SPAN style="color:#00007F">Then</SPAN><br>    Worksheets("Product").Activate<br>    Sheets("Product").Range("G" & (14 + Prod2Count)) = Sheets("Input").Range("C" & i)<br>    Sheets("Product").Range("H" & (14 + Prod2Count)) = Sheets("Input").Range("U" & i)<br>    Prod2Count = Prod2Count + 1<br>    <br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>      i = i + 1<br>    <SPAN style="color:#00007F">Loop</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Badnames]

This is where the code is stopping... it's my problem child

Sheets("Product").Range("J" & (14 + ProdCount)) = Sheets("Input").Application.WorksheetFunction.Sum(Range("p" & i & ":t" & i))

 

[shg]

Try

    Worksheets("Product").Cells(ProdCount + 14, "J").Value2 = _
    WorksheetFunction.Sum(Worksheets("Input").Rows(i).Range("P1:T1").Value2)

 

[Badnames]

Awesome SHG! It worked!

In the code below, in the "P1:T1" why do you need the 1's. Also, what is the .value2 used for? Still a little new to vba and trying to learn.

WorksheetFunction.Sum(Worksheets("Input").Rows(i).Range("P1:T1").Value2)

 

[shg]

... in the "P1:T1" portion, can you rewrite it "P:T"?

You tried it, and ...?

... what is the .value2 used for?

Unlike Value, the Value2 property doesn't convert cells formatted as currency to Currency and cells formatted as dates to Date.

 

[Badnames]

Ahh good to know on the value part. I tried the "P:T" and the macro broke. Looks like I edited my question a little late, I mainly was wondering why you needed the 1's.