Macro not working on find/replace

Question

Good day,

Hoping you can assist please.

I am trying to record a macro which will find text and replace it with numbers.

I have copied figures from a CSV document onto my Excel sheet.

They are not reading as figures, so I need to change the format into something that will read as a number.

If I replace the full stop (.) with a comma (,) manually by using find/replace, it works.

Example: 54.34 changes to 54,34

But when I record a macro to do the same, it's not working in the same way.

While it replaces the full stop with a comma, the figures still do not read as numbers.

Any ideas?

Regards,

Lindi

Answer 1

Does this work?

Sub Convert2Number()
    Selection.NumberFormat = "General"
    Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart
End Sub

Answer 2

Oh dear... it's not working.

Thanks for your assistance though.

I must just be missing something.

Answer 3

Did you try the macro that I suggested? (Select the range you want to apply it to before running it)

If so, does it also fail the second time you run it?

Answer 4

Hi,

Please bear with me as my Excel knowledge is rather limited as a beginner.

I am not writing out the code but completing the manual actions and then referring to the code.

It always works on the 1st try after I record the macro, but when I try to test it again, it fails.

Not sure what changes I need to apply to the below to match it to your suggestion above.

Mine looks like this...

Sub NumberConverting()

'

' NumberConverting Macro

'

'

Range("C15:W137").Select 

Cells.Replace What:=".", Replacement:=",", LookAt:=xlPart, SearchOrder \_ 

    :=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:= \_ 

    False, FormulaVersion:=xlReplaceFormula2 

ActiveWindow.SmallScroll Down:=-1 

Range("A1").Select 

End Sub

Regards,

Lindi

Answer 5

Thanks for the response Hans.

Will give it a go.

Much appreciated.

Regards,

Lindi