Data Formatting Problem in a Macro

[RedMonkey]

Been mining these forums for awhile with relish and even picked up a few items from the store but now I am leaving the nursery of planned exercises and reading.

I'm cutting my teeth by recording a macro of a small daily project I do and cutting out the fat and a few non-functional processes but I have hit a snag.

The data I use is imported from another source and always has the same columns, headers and type of data. Only the total amount of rows change. This makes it easy for me to use Range ("$N:$N").Select type commands to format columns before the next stage of making a pivottable and such. All other types of formatting (dates, removing spaces, etc)have gone smoothly.

So here is the rub: I have a column (N) that is nothing but simple numbers. But the import creates them as text AND the only way I can normally convert them to text is selecting the row and using the smart-tag to convert to number. Recording does not show ANY data from doing this. Yes I can create a new col and mult by 1 then remove a col but that's more steps for a simple result. Using the .NumberFormat = "0" also has no effect on my text.

So I start digging in the forums an find ideas:

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
<o></o>
Sub nfmt()<o></o>
With ActiveSheet.UsedRange<o></o>
.Value = Evaluate(.Address & "*1")<o></o>
End With<o></o>
End Sub<o></o>


But this and others were created so you can manually select a cell or range and then run the code, my beginner's block deals with integrating something into a larger macro. I know once I can think in object.method a bit clearer this will cease to be an issue.

Hate to be wordy but since I cannot upload examples yet (work security) I want to be as clear as possible.

 

[Weaver]

Here's a thought, try this:

    Columns("N:N").TextToColumns Destination:=Range("N1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True

 

[RedMonkey]

Well I have to admit that did what needed to be done and thank you for that solution!

I really do not want to get into the habit of emulating a longer process for a format issue though so any other ideas like the data x 1 item in my original post will be appreciated.

 

[Weaver]

Well I have to admit that did what needed to be done and thank you for that solution!

I really do not want to get into the habit of emulating a longer process for a format issue though so any other ideas like the data x 1 item in my original post will be appreciated.

I'm not really sure what you mean by 'longer process' in this instance. Have you got any specific examples that might help?

or maybe you're suggesting something like this:

Sub x()
    With Intersect(Columns("n"), ActiveSheet.UsedRange)
        .Value = Evaluate(.Address & "*1")
    End With
End Sub