Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formating multiple cols from text to No
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. -- Jim |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formating multiple cols from text to No
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. Jim G wrote: 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. -- Jim -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formating multiple cols from text to No
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. -- Jim "Dave Peterson" wrote: 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. Jim G wrote: 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. -- Jim -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formating multiple cols from text to No
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 G wrote: 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. -- Jim "Dave Peterson" wrote: 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. Jim G wrote: 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. -- Jim -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formating multiple cols from text to No
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? -- Jim "Dave Peterson" wrote: 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 G wrote: 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. -- Jim "Dave Peterson" wrote: 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. Jim G wrote: 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. -- Jim -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formating multiple cols from text to No
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 G wrote: 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? -- Jim "Dave Peterson" wrote: 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 G wrote: 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. -- Jim "Dave Peterson" wrote: 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. Jim G wrote: 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. -- Jim -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formating multiple cols from text to No
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 -- Jim "Dave Peterson" wrote: 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 G wrote: 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? -- Jim "Dave Peterson" wrote: 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 G wrote: 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. -- Jim "Dave Peterson" wrote: 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. Jim G wrote: 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. -- Jim -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 Cols To 2 Cols VLookup Comparison | Excel Discussion (Misc queries) | |||
How do I perform multiple formating within a single line of text? | Excel Worksheet Functions | |||
conditional formating on rows & cols depending on one cell | Excel Discussion (Misc queries) | |||
Macro for single to multiple cols. Help! | New Users to Excel | |||
Cond Format:re color 2 cols, skip 2 cols | Excel Worksheet Functions |