Macro wont add decimals only integers?

D

distraughtintern

New Member
Joined
Jun 8, 2011
Messages
5
I am writing a macro to generate daily sales reports. The macro works fully except for a nested loop consisting of if statements. The only thing wrong with the loop or if statements is that it is not adding the decimal values.

Here is ALL the CODE. What I think the problem area is between these guys ":warning:"

Sub Daily_tix_rep()

Dim ToDay
Dim iBottomRow As Integer
Dim iRowCounter As Integer
Dim iReportCount As Integer
Dim iVisa As Integer
Dim iAmex As Integer
Dim iMc As Integer
Dim iCreditCount As Integer


iBottomRow = Sheets("Requests and Comps").Range("D12").End(xlDown).Row
ReportCount = 7


Sheets("Daily Ticket Inc. Report").Range("A7:N118").delete Shift:=xlToLeft


ToDay = Sheets("Daily Ticket Inc. Report").Range("J1").Value

For iRowCounter = 12 To iBottomRow
If Range("AC" & iRowCounter) = ToDay Then
ReportCount = ReportCount + 1

Sheets("Daily Ticket Inc. Report").Range("A" & ReportCount) = "A&B"
Range("A5").Copy
Sheets("Daily Ticket Inc. Report").Range("B" & ReportCount).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A3").Copy
Sheets("Daily Ticket Inc. Report").Range("C" & ReportCount).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("W" & iRowCounter).Copy
Sheets("Daily Ticket Inc. Report").Range("D" & ReportCount).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("S" & iRowCounter).Copy
Sheets("Daily Ticket Inc. Report").Range("E" & ReportCount).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("U" & iRowCounter).Copy
Sheets("Daily Ticket Inc. Report").Range("F" & ReportCount).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("Z" & iRowCounter).Copy
Sheets("Daily Ticket Inc. Report").Range("G" & ReportCount).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("Y" & iRowCounter).Copy
Sheets("Daily Ticket Inc. Report").Range("H" & ReportCount).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AD" & iRowCounter).Copy
Sheets("Daily Ticket Inc. Report").Range("K" & ReportCount).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("V" & iRowCounter).Copy
Sheets("Daily Ticket Inc. Report").Range("I" & ReportCount).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Daily Ticket Inc. Report").Range("J" & ReportCount) = WorksheetFunction.Sum(Sheets("Daily Ticket Inc. Report").Range("D" & ReportCount, "I" & ReportCount))

Sheets("Daily Ticket Inc. Report").Range("B" & ReportCount).NumberFormat = "m/d/yyyy"
Sheets("Daily Ticket Inc. Report").Range("D" & ReportCount).NumberFormat = "$#,##0.00"
Sheets("Daily Ticket Inc. Report").Range("E" & ReportCount).NumberFormat = "$#,##0.00"
Sheets("Daily Ticket Inc. Report").Range("F" & ReportCount).NumberFormat = "$#,##0.00"
Sheets("Daily Ticket Inc. Report").Range("G" & ReportCount).NumberFormat = "$#,##0.00"
Sheets("Daily Ticket Inc. Report").Range("H" & ReportCount).NumberFormat = "$#,##0.00"
Sheets("Daily Ticket Inc. Report").Range("I" & ReportCount).NumberFormat = "$#,##0.00"
Sheets("Daily Ticket Inc. Report").Range("J" & ReportCount).NumberFormat = "$#,##0.00"
Sheets("Daily Ticket Inc. Report").Range("J" & ReportCount).Font.Bold = True
End If
Next

Dim iReportBotRow As Integer
Dim iReportCount2 As Integer

iReportBotRow = Sheets("Daily Ticket Inc. Report").Range("D8").End(xlDown).Row


ReportCount = ReportCount + 2
iReportCount2 = ReportCount
Sheets("Daily Ticket Inc. Report").Range("C" & ReportCount) = "Amount to apply as ticket income:"
Sheets("Daily Ticket Inc. Report").Range("D" & ReportCount) = WorksheetFunction.Sum(Sheets("Daily Ticket Inc. Report").Range("D8", "D" & iReportBotRow))
Sheets("Daily Ticket Inc. Report").Range("C" & ReportCount).Font.Bold = True
Sheets("Daily Ticket Inc. Report").Range("D" & ReportCount).Font.Bold = True
Sheets("Daily Ticket Inc. Report").Range("D" & ReportCount).NumberFormat = "$#,##0.00"
ReportCount = ReportCount + 1
Sheets("Daily Ticket Inc. Report").Range("C" & ReportCount) = "Amount to apply as service charges:"
Sheets("Daily Ticket Inc. Report").Range("D" & ReportCount) = WorksheetFunction.Sum(Sheets("Daily Ticket Inc. Report").Range("E8", "E" & iReportBotRow))
Sheets("Daily Ticket Inc. Report").Range("C" & ReportCount).Font.Bold = True
Sheets("Daily Ticket Inc. Report").Range("D" & ReportCount).Font.Bold = True
Sheets("Daily Ticket Inc. Report").Range("D" & ReportCount).NumberFormat = "$#,##0.00"
ReportCount = ReportCount + 1
Sheets("Daily Ticket Inc. Report").Range("C" & ReportCount) = "Amount to apply as 3% CC charges:"
Sheets("Daily Ticket Inc. Report").Range("D" & ReportCount) = WorksheetFunction.Sum(Sheets("Daily Ticket Inc. Report").Range("F8", "F" & iReportBotRow))
Sheets("Daily Ticket Inc. Report").Range("C" & ReportCount).Font.Bold = True
Sheets("Daily Ticket Inc. Report").Range("D" & ReportCount).Font.Bold = True
Sheets("Daily Ticket Inc. Report").Range("D" & ReportCount).NumberFormat = "$#,##0.00"
ReportCount = ReportCount + 1
Sheets("Daily Ticket Inc. Report").Range("C" & ReportCount) = "Amount to apply as parking:"
Sheets("Daily Ticket Inc. Report").Range("D" & ReportCount) = WorksheetFunction.Sum(Sheets("Daily Ticket Inc. Report").Range("G8", "G" & iReportBotRow))
Sheets("Daily Ticket Inc. Report").Range("C" & ReportCount).Font.Bold = True
Sheets("Daily Ticket Inc. Report").Range("D" & ReportCount).Font.Bold = True
Sheets("Daily Ticket Inc. Report").Range("D" & ReportCount).NumberFormat = "$#,##0.00"
ReportCount = ReportCount + 1
Sheets("Daily Ticket Inc. Report").Range("C" & ReportCount) = "Amount to apply as tour fee:"
Sheets("Daily Ticket Inc. Report").Range("D" & ReportCount) = WorksheetFunction.Sum(Sheets("Daily Ticket Inc. Report").Range("H8", "H" & iReportBotRow))
Sheets("Daily Ticket Inc. Report").Range("C" & ReportCount).Font.Bold = True
Sheets("Daily Ticket Inc. Report").Range("D" & ReportCount).Font.Bold = True
Sheets("Daily Ticket Inc. Report").Range("D" & ReportCount).NumberFormat = "$#,##0.00"
ReportCount = ReportCount + 1
Sheets("Daily Ticket Inc. Report").Range("C" & ReportCount) = "Shipping fee:"
Sheets("Daily Ticket Inc. Report").Range("D" & ReportCount) = WorksheetFunction.Sum(Sheets("Daily Ticket Inc. Report").Range("I8", "I" & iReportBotRow))
Sheets("Daily Ticket Inc. Report").Range("C" & ReportCount).Font.Bold = True
Sheets("Daily Ticket Inc. Report").Range("D" & ReportCount).Font.Bold = True
Sheets("Daily Ticket Inc. Report").Range("D" & ReportCount).NumberFormat = "$#,##0.00"

ReportCount = ReportCount + 1


:warning:
iVisa = 0
iAmex = 0
iMc = 0

iCreditCount = 0

For iCreditCount = 8 To iReportBotRow
If Sheets("Daily Ticket Inc. Report").Range("K" & iCreditCount) = "VISA" Then
iVisa = iVisa + Sheets("Daily Ticket Inc. Report").Range("J" & iCreditCount).Value
End If
If Sheets("Daily Ticket Inc. Report").Range("K" & iCreditCount) = "MC" Then
iMc = iMc + Sheets("Daily Ticket Inc. Report").Range("J" & iCreditCount).Value
End If
If Sheets("Daily Ticket Inc. Report").Range("K" & iCreditCount) = "AMEX" Then
iAmex = iAmex + Sheets("Daily Ticket Inc. Report").Range("J" & iCreditCount).Value
End If
Debug.Print iVisa
Debug.Print iMc
Debug.Print iAmex


Next

:warning:

Dim iGrandTot As Integer



ReportCount = ReportCount + 1
Sheets("Daily Ticket Inc. Report").Range("I" & ReportCount) = "Grand Total:"
iGrandTot = iMc + iVisa + iAmex
Sheets("Daily Ticket Inc. Report").Range("J" & ReportCount) = iGrandTot
Sheets("Daily Ticket Inc. Report").Range("I" & ReportCount).Font.Bold = True
Sheets("Daily Ticket Inc. Report").Range("J" & ReportCount).Font.Bold = True
Sheets("Daily Ticket Inc. Report").Range("J" & ReportCount).NumberFormat = "$#,##0.00"
ReportCount = ReportCount + 1
Sheets("Daily Ticket Inc. Report").Range("I" & ReportCount) = "Visa:"
Sheets("Daily Ticket Inc. Report").Range("J" & ReportCount) = iVisa
Sheets("Daily Ticket Inc. Report").Range("J" & ReportCount).NumberFormat = "$#,##0.00"
ReportCount = ReportCount + 1
Sheets("Daily Ticket Inc. Report").Range("I" & ReportCount) = "Amex:"
Sheets("Daily Ticket Inc. Report").Range("J" & ReportCount) = iAmex
Sheets("Daily Ticket Inc. Report").Range("J" & ReportCount).NumberFormat = "$#,##0.00"
ReportCount = ReportCount + 1
Sheets("Daily Ticket Inc. Report").Range("I" & ReportCount) = "MC:"
Sheets("Daily Ticket Inc. Report").Range("J" & ReportCount) = iMc
Sheets("Daily Ticket Inc. Report").Range("J" & ReportCount).NumberFormat = "$#,##0.00"

Values the if statement is working with They alternate (MC,AMEX, MC, AMEX)

<table style="border-collapse: collapse; width: 69pt;" width="92" border="0" cellpadding="0" cellspacing="0"><col style="width: 69pt;" width="92"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt; width: 69pt;" width="92" align="right" height="17">$144.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" align="right" height="17">$114.50</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" align="right" height="17">$146.50</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" align="right" height="17">$171.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" align="right" height="17">$72.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" align="right" height="17">$229.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" align="right" height="17">$109.00</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl66" style="height: 12.75pt;" align="right" height="17">$217.50</td> </tr> </tbody></table>

Debug.print printout
0
144
0
0
144
114
0
290
114
0
290
285
0
362
285
0
362
514
0
471
514
0
471
732


Any ideas why it doesnt add the cents/how to fix it?

Will upload a clean document minus the test numbers shortly.
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
when you use "Integer" as a type, you restrict the variable to being a whole number

use 'single' for small decimal numbers or 'double' for big ones.
 
Upvote 0
diddi said:
when you use "Integer" as a type, you restrict the variable to being a whole number

use 'single' for small decimal numbers or 'double' for big ones.
Click to expand...

Ahhh of course!! Thank you so much I had forgotten I had dimensioned them that way. Thanks so much. Also thanks for the tab hint while coding.
 
Upvote 0
a bit more detail on variable types...

Byte data type
A data type used to hold positive integer numbers ranging from 0 to 255. Byte variables are stored as single, unsigned 8-bit (1-byte) numbers.

Boolean data type
A data type with only two possible values, True (-1) or False (0). Boolean variables are stored as 16-bit (2-byte) numbers.

Integer data type
A data type that holds integer variables stored as 2-byte whole numbers in the range -32,768 to 32,767. The Integer data type is also used to represent enumerated values. The percent sign (%) type-declaration character represents an Integer in Visual Basic.

Long data type
A 4-byte integer ranging in value from -2,147,483,648 to 2,147,483,647. The ampersand (&) type-declaration character represents a Long in Visual Basic.

Currency data type
A data type with a range of -922,337,203,685,477.5808 to 922,337,203,685,477.5807. Use this data type for calculations involving money and for fixed-point calculations where accuracy is particularly important. The at sign (@) type-declaration character represents Currency in Visual Basic.

Single data type
A data type that stores single-precision floating-point variables as 32-bit (2-byte) floating-point numbers, ranging in value from -3.402823E38 to -1.401298E-45 for negative values, and 1.401298E-45 to 3.402823E38 for positive values. The exclamation point (!) type-declaration character represents a Single in Visual Basic.

Double data type
A data type that holds double-precision floating-point numbers as 64-bit numbers in the range -1.79769313486232E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values. The number sign (#) type-declaration character represents the Double in Visual Basic.

(stolen from ozgrid)
 
Upvote 0
a hint to save you a bit of typing...
you can use 'with' blocks:

Code: 
ReportCount = ReportCount + 1
With Sheets("Daily Ticket Inc. Report")
    .Range("I" & ReportCount) = "Grand Total:"
    iGrandTot = iMc + iVisa + iAmex
    .Range("J" & ReportCount) = iGrandTot
    .Range("I" & ReportCount).Font.Bold = True
    .Range("J" & ReportCount).Font.Bold = True
    .Range("J" & ReportCount).NumberFormat = "$#,##0.00"


etc

End With
 
Upvote 0
Thanks! I have tried using "with" in the past but I have always end up with problems so I have avoided them. Ill give them another shot now that I have an example to work with.

diddi said:
a hint to save you a bit of typing...
you can use 'with' blocks:

Code: 
ReportCount = ReportCount + 1
With Sheets("Daily Ticket Inc. Report")
    .Range("I" & ReportCount) = "Grand Total:"
    iGrandTot = iMc + iVisa + iAmex
    .Range("J" & ReportCount) = iGrandTot
    .Range("I" & ReportCount).Font.Bold = True
    .Range("J" & ReportCount).Font.Bold = True
    .Range("J" & ReportCount).NumberFormat = "$#,##0.00"


etc

End With
Click to expand...
 
Upvote 0
no worries. have a bit of a go and if it doesnt work post it on this thread.

indenting can help a lot when trying to get blocks like with/end with to line up properly.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
VBA code clean up
Replies
9
Views
583
mumps
mumps
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
K
  • Question Question
Modifying Existing VBA Code to add 2 additional data columns
Replies
0
Views
329
keranali
K
Cliffork
  • Solved
Print to PDF Macro, or possibly more
2 3
Replies
21
Views
375
mumps
mumps
D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
366
dilshod_k
D

Forum statistics

Threads
1,224,602
Messages
6,179,848
Members
452,948
Latest member
UsmanAli786

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top