Removing the Last Two...

[tourless]

Hi Folks,

I know this has been asked a thousand times and I'm trying to implement some of the solutions I've found but I just can't seem to get this to work. I have values in column I that four decimal places deep and I simply want to remove the last two characters. The problem I'm noticing is some of the values only contain two digits even though the cells are formatted to four decimal places. So a value of 56.34 is formatted as 56.3400. Other cells have actual values that are three of four decimal places deep, such as a value of 5.7324 is formatted as 5.7324, and a value of 4.242 is formatted as 4.2420. So I guess I need to evaluate each cell in my range and add a few if's to account for the variations? This is what I'm working with.

For i = 2 To LastRow
    .Cells(i, "I") = Left(.Cells(i, "I").Value, Len(.Cells(i, "I").Value) - 2)
Next i

 

[Fluff]

How about

Sub tourless2()
   With Range("I2", Range("I" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(@="""","""",round(@,2))", "@", .Address))
   End With
End Sub

 

[VBE313]

why not use Round and just go two decimal places?

 

[tourless]

I was trying to avoid rounding but I can't really think of a good reason why I can't so I'm going to give this a try and test out some totals. If I total each section before and after the rounding I might have a variance which I would love to add to the last line of a given section but let's see how this works out. I'll keep you posted...

 

[tourless]

No good. Rounding skews my totals. I'm trying to work with TRUNC. Validating now.

 

[jim may]

How about

Sub tourless2()
   With Range("I2", Range("I" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(@="""","""",round(@,2))", "@", .Address))
   End With
End Sub

How is the "@" used or interpreted in the below partial code line.

Replace("if(@="""","""",round(@,2))", "@", .Address)

 

[Fluff]

The replace function replaces the @ in the formula with .Address

 

[Peter_SSs]

Does this variation do what you want?

Sub TwoDP()
  With Range("I2", Range("I" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(#="""","""",int(#*100)/100)", "#", .Address))
  End With
End Sub

 

[tourless]

Thanks for all the replies everyone... I'm using this for now although it's not perfect it leaves me a lot closer to my goal and eliminates a lot of time manually adjusting dollar values.

    Range("J2:J" & lastRow).Formula = "=TRUNC(I2,2)"
    Range("J2:J" & lastRow).Copy
    Range("I2").PasteSpecial xlPasteValues
    Range("J2:J" & lastRow).ClearContents

Thanks all.

 

[Fluff]

As this is a totally different question, it needs a new thread.
Thanks