VBA find and replace 2.50 with 1.50 issue

[MichaelRSnow]

Hi

Hopefully a really simple solution to my problem, i'm trying to find and replace a rate of 1.50 with 2.50, the 'fnd' value is in a textbox formatted as 0.00, as is the 'rplc' value, but the sheet where its performing this change is formatted as 0.00 (number, 2 decimal) but if you select the cell it shows as 0.0 (don't see the ending 0). Therefore it can't find 1.50 as its technically 1.5

Is there a way to get this to work WITHOUT formatting all my values to text?

        'Perform the Find/Replace on each row for rates
          sht.Cells.SpecialCells(xlCellTypeVisible).Replace What:=fnd, Replacement:=rplc, _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
            SearchFormat:=False, ReplaceFormat:=False

 

[footoo]

Try using fnd*1 (or fnd+0, or --fnd) instead of fnd

 

[MichaelRSnow]

Try using fnd*1 (or fnd+0, or --fnd) instead of fnd

Would this invalidate other ones where the rate being found and replaced is 1.55 with 1.65 for example?

 

[footoo]

Would this invalidate other ones where the rate being found and replaced is 1.55 with 1.65 for example?

No. Why not test and see?

 

[MichaelRSnow]

No. Why not test and see?

Thought i would ask first, just in case

 

[footoo]

Thought i would ask first, just in case

All it does is convert the text to a number.

 

[MichaelRSnow]

Slight issue with that, the rate also appears in over columns along that row, sometimes within a string, this add's extra 0's to those so might need to break the sht.Cells.SpecialCells(xlCellTypeVisible) into multiple pieces of code focusing on columns not the whole visible sheet of data

 

[MichaelRSnow]

Having separate code for each of the rate columns and applying your solution to the rates (columns) not in the string has worked perfectly, thank you

 

[footoo]

Slight issue with that, the rate also appears in over columns along that row, sometimes within a string, this add's extra 0's to those so might need to break the sht.Cells.SpecialCells(xlCellTypeVisible) into multiple pieces of code focusing on columns not the whole visible sheet of data

Having separate code for each of the rate columns and applying your solution to the rates (columns) not in the string has worked perfectly, thank you

Instead of separate code, try :

Cells.SpecialCells(xlCellTypeVisible).Replace What:=fnd * 1, Replacement:=Format(rplc * 1, "0.00"), _

Edit : No, that doesn't work - will revert later.

 

[MichaelRSnow]

There are only 5 rate columns, 3 in strings, 2 as number values, just over a thousand rows. Running it 5 times focusing on each column hasn't delayed the code any, so no need to worry about finding a cleaner solution.

Thanks again