range.formula changing format?

[ra_weiss]

I have looked everywhere within my workbook/sheet for what could be causing the following code to enter its result rounded to the nearest dollar.

******************
Sub Office_Pay_Totals()

Dim i As Long
Dim j As Long

i = 0
ActiveSheet.Unprotect Password:="1"
Range("E31").Formula = "=Sumif(N9:N28,""x"",E9:E28)"
Let i = Range("e31")

For j = 9 To 28

ActiveSheet.Cells(j, 14).Activate
If ActiveSheet.Cells(j, 14) = "X" Then
ActiveSheet.Unprotect Password:="1"
ActiveSheet.Cells(j, 5).Activate
ActiveCell.BorderAround Weight:=xlMedium
ActiveSheet.Unprotect Password:="1"

ActiveSheet.Cells(j, 14).Clear

End If

Next j

On Error Resume Next
Let Range("e31") = i
On Error Resume Next
ActiveSheet.Protect Password:="1"

End Sub

*******************
Cell E31 has $0.00 formatting, but when ever I run this script the result is changed to $0 formatting. As a kicker, another PC (also WIN7) does not do this. Does anyone have ideas?

(The script looks in column N for any instances of "X", and if found adds all the corresponding row figures in column E and enters the total in cell E31)

And, yes, I'm a beginner.

 

[ra_weiss]

Ver 14.0.51.28.5000 32bit

 

[Norie]

You've declared the variable i as a Long Integer, so you'll lose the decimal part of the result when you assign it to that variable.

You could declare it as Decimal, or you could just put the result straight into the cell without using a variable.

 

[ra_weiss]

Interesting. I wonder how it works on my other PC.

I'll try your decimal suggestion. Thanks.

 

[ra_weiss]

Got it:

Dim i As Single
Dim j As Single

Thanks for your prompt reply.

 

[Norie]

Actually I misread your formula and assumed that since you mentioned % you expected dollars and cents.

Looks like it's only dollars though, so an integer should be fine.

So I'm not 100% sure what the problem is, though that does seem to be quite a lot of code for what I think is quite simple.

For a start you should only need to unprotect the sheet once.

 

[ra_weiss]

The protect/unprotect routine is for usability.

K, go for it. I have "N" column that if an "X" is placed, the routine must find corresponding row data in "E" column, then total all instances into "E31". I'd like to see an alternative. Thanks.

 

[Norie]

Sorry I'm not quite following.

What has column K got to do with it?

Also if you want a total wouldn't you use SUMIF not COUNTIF?

 

[ra_weiss]

I don't believe either would work in this case. K = OK, as in, OK, go for it

 

[ra_weiss]

<style type="text/css">
table.tableizer-table {border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif; font-size: 12px;} .tableizer-table td {padding: 4px; margin: 3px; border: 1px solid #ccc;}
.tableizer-table th {background-color: #104E8B; color: #FFF; font-weight: bold;}
</style>

<table class="tableizer-table">
<tr class="tableizer-firstrow"><th></th><th> </th><th> </th><th> </th></tr> <tr><td> </td><td>$525.00</td><td> </td><td>X</td></tr> <tr><td> </td><td>$21.00</td><td> </td><td> </td></tr> <tr><td> </td><td>$908.00</td><td> </td><td>X</td></tr> <tr><td> </td><td>$27.00</td><td> </td><td>X</td></tr> <tr><td> </td><td>$49.00</td><td> </td><td> </td></tr> <tr><td> </td><td> </td><td> </td><td> </td></tr> <tr><td>SUBT</td><td>$70.00</td><td> </td><td> </td></tr> <tr><td>X</td><td>$1,460.00</td><td> </td><td> </td></tr> <tr><td>TOTAL</td><td>$1,530.00</td><td> </td><td></td></tr></table>

Any row with an "X" gets totaled in a seperate subtotal, and also has a border added to the cell. (the border is not shown in my example)

Edit: clean up