Need a work around!!

[MsAlitaY]

Hi All,

The code below reviews each row and returns "ERROR" in column P if the sum of the MONTHS does not equal the hard number stated in YTD; if the numbers match the code just moves on to the next row for evaluation.

The code works perfectly EXCEPT when the sum total and the YTD ends with .50...urg!!! ANY IDEAS ON HOW TO FIX THIS???

[TABLE="width: 865"]
<tbody>[TR]
[TD="class: xl588, width: 64, bgcolor: transparent"]OFFSET
[/TD]
[TD="class: xl588, width: 90, bgcolor: transparent"]-15
[/TD]
[TD="class: xl588, width: 64, bgcolor: transparent"]-14
[/TD]
[TD="class: xl588, width: 64, bgcolor: transparent"]-13
[/TD]
[TD="class: xl588, width: 64, bgcolor: transparent"]-12
[/TD]
[TD="class: xl588, width: 64, bgcolor: transparent"]-11
[/TD]
[TD="class: xl588, width: 64, bgcolor: transparent"]-10
[/TD]
[TD="class: xl588, width: 68, bgcolor: transparent"]-9
[/TD]
[TD="class: xl588, width: 68, bgcolor: transparent"]-8
[/TD]
[TD="class: xl588, width: 65, bgcolor: transparent"]-7
[/TD]
[TD="class: xl588, width: 65, bgcolor: transparent"]-6
[/TD]
[TD="class: xl588, width: 65, bgcolor: transparent"]-5
[/TD]
[TD="class: xl588, width: 65, bgcolor: transparent"]-4
[/TD]
[TD="class: xl588, width: 65, bgcolor: transparent"]-3
[/TD]
[TD="class: xl588, width: 71, bgcolor: transparent"]-2
[/TD]
[TD="class: xl588, width: 68, bgcolor: transparent"]-1
[/TD]
[TD="class: xl588, width: 77, bgcolor: transparent"]ACTIVECELL
[/TD]
[/TR]
[TR]
[TD="class: xl588, bgcolor: transparent"]COLUMN
[/TD]
[TD="class: xl588, bgcolor: transparent"]A
[/TD]
[TD="class: xl588, bgcolor: transparent"]B
[/TD]
[TD="class: xl588, bgcolor: transparent"]C
[/TD]
[TD="class: xl588, bgcolor: transparent"]D
[/TD]
[TD="class: xl588, bgcolor: transparent"]E
[/TD]
[TD="class: xl588, bgcolor: transparent"]F
[/TD]
[TD="class: xl588, bgcolor: transparent"]G
[/TD]
[TD="class: xl588, bgcolor: transparent"]H
[/TD]
[TD="class: xl588, bgcolor: transparent"]I
[/TD]
[TD="class: xl588, bgcolor: transparent"]J
[/TD]
[TD="class: xl588, bgcolor: transparent"]K
[/TD]
[TD="class: xl588, bgcolor: transparent"]L
[/TD]
[TD="class: xl588, bgcolor: transparent"]M
[/TD]
[TD="class: xl588, bgcolor: transparent"]N
[/TD]
[TD="class: xl588, bgcolor: transparent"]O
[/TD]
[TD="class: xl588, bgcolor: transparent"]P
[/TD]
[/TR]
[TR]
[TD="class: xl588, bgcolor: transparent"][/TD]
[TD="class: xl589, bgcolor: silver"]Business Unit
[/TD]
[TD="class: xl589, bgcolor: silver"]Fund
[/TD]
[TD="class: xl589, bgcolor: silver"]Department
[/TD]
[TD="class: xl589, bgcolor: silver"]Major
[/TD]
[TD="class: xl589, bgcolor: silver"]Account
[/TD]
[TD="class: xl589, bgcolor: silver"]Title
[/TD]
[TD="class: xl589, bgcolor: silver"]FY16
[/TD]
[TD="class: xl589, bgcolor: silver"]FY17
[/TD]
[TD="class: xl589, bgcolor: silver"]JUL
[/TD]
[TD="class: xl589, bgcolor: silver"]AUG
[/TD]
[TD="class: xl589, bgcolor: silver"]SEP
[/TD]
[TD="class: xl589, bgcolor: silver"]OCT
[/TD]
[TD="class: xl589, bgcolor: silver"]NOV
[/TD]
[TD="class: xl589, bgcolor: silver"]YTD
[/TD]
[TD="class: xl589, bgcolor: silver"]BUDGET
[/TD]
[TD="class: xl589, bgcolor: silver"]Error Check
[/TD]
[/TR]
[TR]
[TD="class: xl588, bgcolor: transparent"][/TD]
[TD="class: xl593, width: 90, bgcolor: transparent"]95720
[/TD]
[TD="class: xl593, width: 64, bgcolor: transparent"]01000
[/TD]
[TD="class: xl593, width: 64, bgcolor: transparent"]857
[/TD]
[TD="class: xl593, width: 64, bgcolor: transparent"]11
[/TD]
[TD="class: xl593, width: 64, bgcolor: transparent"]5012489
[/TD]
[TD="class: xl591, width: 64, bgcolor: transparent"]Expense
[/TD]
[TD="class: xl592, width: 68, bgcolor: transparent"] 59,803.08
[/TD]
[TD="class: xl592, width: 68, bgcolor: transparent"] 62,734.25
[/TD]
[TD="class: xl592, width: 65, bgcolor: transparent"] 8,001.36
[/TD]
[TD="class: xl592, width: 65, bgcolor: transparent"] 5,456.20
[/TD]
[TD="class: xl592, width: 65, bgcolor: transparent"] 5,456.20
[/TD]
[TD="class: xl592, width: 65, bgcolor: transparent"] 5,459.90
[/TD]
[TD="class: xl592, width: 65, bgcolor: transparent"] 5,615.84
[/TD]
[TD="class: xl592, width: 71, bgcolor: transparent"] 29,989.50
[/TD]
[TD="class: xl592, width: 68, bgcolor: transparent"] 70,951.00
[/TD]
[TD="class: xl590, bgcolor: transparent"]ERROR
[/TD]
[/TR]
</tbody>[/TABLE]

'To review each row for data errors

Do While ActiveCell.Offset(0, -11).Value > 0
YTD = ActiveCell.Offset(0, -2)
Fx = Application.WorksheetFunction.Sum(ActiveCell.Offset(0, -7), ActiveCell.Offset(0, -6), ActiveCell.Offset(0, -5), ActiveCell.Offset(0, -4), ActiveCell.Offset(0, -3))

If Fx = YTD Then

ActiveCell.Offset(1, 0).Select

Else

Range("P1").FormulaR1C1 = "Error Check"
ActiveCell.FormulaR1C1 = "ERROR"
ActiveCell.Offset(1, 0).Select

End If

Loop

 

[Joe4]

I am guessing that you may have declared either your YTD or Fx variables in VBA to be Integers. Note that Integers do not allow for decimals!
You will need to change how they are declared (maybe Single or Double).

 

[MsAlitaY]

I had it declared as Long. Double didn't work, but Single did.

Thanks Joe4!!

 

[Joe4]

You are welcome.

Yep, "Long" is short for "Long Integer", another data type with no decimals.