Formating multiple cols from text to No

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a data sheet that ocassionally has the lower half values remain as
text when I copy new data. I have produced the following macro to convert
the selected cols to either date or number format. There are many more
columns to convert to number format than shown. I've trimmed some of the
default code entered by the macro recorder to what I think is the minimum
required. Is there a more compact version of the code I can use to get the
same result. I also wan this to run on cahnges to the sheet (ie; when new
data is copied in).


Sub ColToNum()

Columns("K:K").Select 'number format
Selection.TextToColumns , DataType:=xlDelimited, FieldInfo _
:=Array(1, 1)
Columns("M:M").Select 'number format
Selection.TextToColumns , DataType:=xlDelimited, FieldInfo _
:=Array(1, 1)
Columns("Q:Q").Select 'number format
Selection.TextToColumns , DataType:=xlDelimited, FieldInfo _
:=Array(1, 1)
Columns("R:R").Select 'number format
Selection.TextToColumns , DataType:=xlDelimited, FieldInfo _
:=Array(1, 1)
Columns("E:E").Select 'date format
Selection.TextToColumns , DataType:=xlDelimited, FieldInfo _
:=Array(1, 4)
Columns("G:G").Select 'date format
Selection.TextToColumns , DataType:=xlDelimited, FieldInfo _
:=Array(1, 4)
End Sub

Any suggestions appreciated.
 
You could set up a couple of arrays that define what columns need to be fixed
and what type for each column (you vary between General and dates (DMY) in your
sample).

Option Explicit
Sub ColToNum2()

Dim ColsToFix As Variant
Dim TypeOfCols As Variant
Dim iCol As Long

ColsToFix = Array("K", "M", "Q", "R", "E", "G")
TypeOfCols = Array(1, 1, 1, 1, 4, 4)

If UBound(TypeOfCols) <> UBound(ColsToFix) Then
MsgBox "design error!"
Exit Sub
End If

With ActiveSheet
For iCol = LBound(ColsToFix) To UBound(ColsToFix)
.Cells(1, ColsToFix(iCol)).EntireColumn.TextToColumns _
DataType:=xlDelimited, _
FieldInfo:=Array(1, TypeOfCols(iCol))
Next iCol
End With

End Sub

You could run it each time something changed using a worksheet_Change event, but
personally, I'd just run it when I needed it. I think it would be overkill to
run this each time I changed anything on that worksheet.
 
Thanks Dave, that was a sensational solution.

I made it run on worksheet_change event because the users are required to
only copy a new data sheet produced by our project software. The new data
can run to several thousand lines. The template will summarise the data for
review. I didn't want them to have to be alert to errors etc.

The formatting problem has only occured on one data sheet so far and is as
yet inexplicable to me.

I had another problem due to a few lines in column E being a dolar value. A
value of $18.60 was converted to 18th June 2007 whcih resulted in $39,251. I
solved it by rounding to no decimal places.

However, I noticed a few dates converting to US style M/D/Y. Is there
something I can add to always ensure DD/MM/YYYY.

Thanks again for your help.
 
First, be careful. If those values that you're pasting look like dates, then
your data may not be what you expect.

If you have a column that is supposed to be dates, format them in an unambiguous
format -- mmm dd, yyyy (say).

Then check to see if your import and routine worked ok. Just because you ended
up with a date doesn't mean that it's the date that the original data used.



Jim said:
Thanks Dave, that was a sensational solution.

I made it run on worksheet_change event because the users are required to
only copy a new data sheet produced by our project software. The new data
can run to several thousand lines. The template will summarise the data for
review. I didn't want them to have to be alert to errors etc.

The formatting problem has only occured on one data sheet so far and is as
yet inexplicable to me.

I had another problem due to a few lines in column E being a dolar value. A
value of $18.60 was converted to 18th June 2007 whcih resulted in $39,251. I
solved it by rounding to no decimal places.

However, I noticed a few dates converting to US style M/D/Y. Is there
something I can add to always ensure DD/MM/YYYY.

Thanks again for your help.
Click to expand...
 
As you predicted, I've noticed a few dates formating into US format, but
dates well in advance of what they could have entered (the dates are
restricted to the current open month).

It's only happened in the one troublesome data set (that I've been made
aware of) and not others that are several times larger but from the same
software. Problem is, if I can't predict it, I'll need to run a fix on every
summary, and could run to hundreds per month.

Is there any way of correcting for or avoiding this?
 
I don't know how you're importing the other data, but maybe you could save that
data as a text file and then use file|open to open that text file.

You'll see the text import wizard where you can specify how each field should be
treated.

If your data fluctuates from mdy to dmy within the same field within the same
import, I don't have a way to automate this. 01/02/03 could mean lots of
different dates.

If your data fluctuates from mdy to dmy between imports, you can use two
different macros (or some sort of branching within one procedure to process the
data in the correct way--maybe just ask the user???).

You may want to look at it from the other end--maybe the process that "sends"
the dates can use an unambiguous format????



Jim said:
As you predicted, I've noticed a few dates formating into US format, but
dates well in advance of what they could have entered (the dates are
restricted to the current open month).

It's only happened in the one troublesome data set (that I've been made
aware of) and not others that are several times larger but from the same
software. Problem is, if I can't predict it, I'll need to run a fix on every
summary, and could run to hundreds per month.

Is there any way of correcting for or avoiding this?
Click to expand...
 
You were right on all counts. I changed the report format to 11.07.2007 and
this seems to have fixed the problem. Strangely, when I used 11-07-2007 it
reverted to US dates.

Thanks for your help on this. I'll be able to use the same technique for
several other projects I have. I'll also be mindful fo the traps thanks to
your advice.

Cheers
 
Back
Top