VBA problem with decimals and concatenation

[DBrian]

I have a range of numbers in cells that have General formatting. When a number in a cell is less than 43560, for example 40000, then I want to add the words "square feet", so it displays as "40000 square feet".

I accomplish this with the following:
If Val(ActiveCell) < 43560 Then ActiveCell = ActiveCell & " square feet"
The result is "40000 square feet". So far, no problem.

The problem comes when the number is greater than or equal to 43560, in which case I want to divide the number by 43560, and I want the number to display rounded to two decimal places followed by the word "acre". For example, if the number is 46000 I want it to display as "1.06 acre".

I tried accomplishing this in two steps:
If Val(ActiveCell) >= 43560 Then ActiveCell.NumberFormat = "0.00"
If Val(ActiveCell) >= 43560 Then ActiveCell = Val(ActiveCell) / 43560 & " acre"

But instead of a number with two decimal places, the result is this: 1.05601469237833 acre

An additional problem arises when the number in the cell is exactly 43560. Then the result is this: 1 acre
The result I wanted was "1.00 acre".

Any help is greatly appreciated.

 

[Fluff]

try
If Val(ActiveCell) >= 43560 Then ActiveCell = Format(Val(ActiveCell) / 43560, "#0.00") & " acre"

 

[JustynaMK]

Hi Brian,

Maybe try using Format function:

    If ActiveCell >= 43560 Then
        ActiveCell = Format(ActiveCell / 43560, "0.00") & " acre"
    End If

Edit: sorry Fluff, just noticed your post!

 

[JLGWhiz]

Or

ActiveCell = Application.Round(ActiveCell.Value / 43560, 2) & " acre"

 

[JLGWhiz]

Or if you want to just truncated the value instead of rounding then

ActiveCell = Left(ActiveCell.Value / 43560, InStr(ActiveCell.Value / 43560, ".") + 2) & " acre"

But this could again leave the value at 1 if it is exactly divisible.

 

[DBrian]

Thank you. This also worked perfectly.

 

[DBrian]

JLGWhiz, Both of your solutions worked for > 43560 but not for = 43560. But thank you.

 

[DBrian]

Thank you. This worked perfectly.

 

[DBrian]

try
If Val(ActiveCell) >= 43560 Then ActiveCell = Format(Val(ActiveCell) / 43560, "#0.00") & " acre"

That does exactly what I want. Thank you for showing me a more efficient way.

 

[Fluff]

Glad we could help & thanks for the feedback