Convert Text to Number

[daren.beaney]

Hi, I've spent nearly a day on trying to find a solution to my problem. I've created a data entry form which goes into an excel sheet, one of the columns is in £ and obviously the value that is entered ends up being text instead of the required format, Accounting.

I've tried code relating to NumberFormat and many other variations including recording the Text to Column process which works fine on a single cell when the code runs but not a range.

This is what I've recently been trying with limited success on multiple cells within the column

Original code

Sub Macro2()


    Range("N3").Select
    Selection.TextToColumns Destination:=Range("N3"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True
End Sub

Modified Code:

Range("MaritimeFiscal").Select
 
        Selection.TextToColumns Destination:=Range("MaritimeFiscalStart"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True

MaritimeFiscalStart = N2 - the first cell in the column.
MaritimeFiscal = dynamic range in the column

Any support on this issue will be well received.

Thanks in advance,
Daren

 

[Joe4]

Are there things in column N that would get messed up by doing a "Text to Columns" on them?
If not, just use:

Sub Macro2()
    Columns("N:N").TextToColumns Destination:=Range("N1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(0, 1), TrailingMinusNumbers:=True
End Sub

 

[daren.beaney]

Thanks, but it didn't work.

 

[daren.beaney]

Found a solution, I removed the currency format on the textbox in the userform. Changed it from "£#,###.00" to "#,###.00".

 

[Joe4]

Can you please post a small sample of the values you are trying to convert so I can try to recreate it and test it here?
Be sure to include different scenarios, if they exist (i.e. if some have symbols and others do not, etc).

EDIT: Just saw your reply, so it appears that you are all set.

 

[daren.beaney]

Can you please post a small sample of the values you are trying to convert so I can try to recreate it and test it here?
Be sure to include different scenarios, if they exist (i.e. if some have symbols and others do not, etc).

EDIT: Just saw your reply, so it appears that you are all set.

Hi, yes it seems easier modifying the textbox format. The value that was being entered for example is £350.00, which kept on being up the convert icon.

 

[Joe4]

OK. A simple Search & Replace (replacing £ with nothing) would probably work too (either manually or via VBA code).
But I agree, it is probably better to handle it at the source!

 

[daren.beaney]

OK. A simple Search & Replace (replacing £ with nothing) would probably work too (either manually or via VBA code).
But I agree, it is probably better to handle it at the source!

Yes that would make sense, Thanks for the suggestion.