Sum A Range Using VBA

[Chris101]

I have a PDF invoice that has been converted to excel. In the excel document I look for "Sales Tax" and I copy the sales tax "amounts" and paste them in column "Q" in the order they are found. My issue is that I would like to sum the range from where the data starts Q2 through the last row and place the sum at the top of column "Q2" replacing what is in the cell already.

In the example provided, the result of $16.35 should be placed in Q2. I can clear the values from Q3 down to last row leaving just the result in column Q2.

When I run my code, it seems to sum the range but with the wrong answer. It places $16.00 in Q2 rather than $16.35. I am assuming it is a rounding issue, and if so, i am not sure how to correct that. It could also be a coding issue or both.

I really appreciate any assistance that is provided. I thought this would be a simple task, but nothing seems to come easy for me when writing VBA.

I have placed my code below and a pic of the data for clarity.

Dim i As Long
Dim a As Long
Dim lastrow As Integer

lastrow = wsh2.Cells(Rows.Count, 17).End(xlUp).Row

i = 2
a = 0

For i = 2 To lastrow
a = a + wsh2.Cells(i, 17).Value
Next

wsh2.Range("Q2") = a

Again, any help is greatly appreciated!

Best Regards,
Chris

 

[rlv01]

You have defined variable 'a' as a long integer. Instead, define it as a double.

Dim a As Long ---> Dim a As Double


(Tip: when posting code, please try to use 'code tags' to format the code

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

 

[Chris101]

Riv01,

Your suggestion worked like a charm. Thank you very much for your time and knowldge in helping me complete this task. You have been a big help.

I will do some reading on how the "code tags" work to make it easier to read.

Thank you again!!!

Chris

 

[FormR]

Dim lastrow As Integer

FWIW, I'd also recommend that you declare this variable as a Long. The number of potential rows available in Excel is greater than the Integer types upper limit.

 

[Fluff]

I will do some reading on how the "code tags" work to make it easier to read.

Have a look here How to Post Your VBA Code