Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
automaticaly moving information
I am importing CSV records from a sales management application and the
information, when imported, hits the sheet in the columns and each range needs to be moved into the correct columns. (This imported information is of a different size each time it is imported. ) Also, there will be several ranges of records that will have to be moved under the same identified column. Example: move I2 thru M2 to D2 move N2 thru R2 to D3 move S2 thru W2 to D4 (and so on until it finds the last record with the same import code) I have identified the columns that this information needs to be moved into, however I do not know the formula or code to tell it to do this? Can anyone help me? Thanks, J. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
automaticaly moving information
I think the problem is with your input data. the data may have been created
on a unix system that uses a different Line feed. Your problem may be all the data is ending up on one line because it is not recognizing the carriage return. If this is the case I have a better solution which is to read the data into the worksheet using a macro that will recognized the UNIX carriage return. "J" wrote: I am importing CSV records from a sales management application and the information, when imported, hits the sheet in the columns and each range needs to be moved into the correct columns. (This imported information is of a different size each time it is imported. ) Also, there will be several ranges of records that will have to be moved under the same identified column. Example: move I2 thru M2 to D2 move N2 thru R2 to D3 move S2 thru W2 to D4 (and so on until it finds the last record with the same import code) I have identified the columns that this information needs to be moved into, however I do not know the formula or code to tell it to do this? Can anyone help me? Thanks, J. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
automaticaly moving information
If Joel's guess if wrong, then for the example you posted, this macro will
move the row of data to the column you specified... Sub MoveRow2Data() Dim X As Long, Z As Long Dim LastColumn As Long Const StartCol As Long = 9 Const GroupCount As Long = 5 Const MoveToColumn As Long = 4 Const DataRow As Long = 2 With Worksheets("Sheet1") LastColumn = .Cells(DataRow, .Columns.Count).End(xlToLeft).Column For X = StartCol To LastColumn Step 5 For Z = 0 To GroupCount - 1 .Cells(DataRow, X + Z).Copy _ Destination:=.Cells(X - 9 + DataRow + Z, MoveToColumn) Next Next .Cells(DataRow, StartCol).Resize(1, LastColumn - _ StartCol + 1).ClearContents End With End Sub Rick "J" wrote in message ... I am importing CSV records from a sales management application and the information, when imported, hits the sheet in the columns and each range needs to be moved into the correct columns. (This imported information is of a different size each time it is imported. ) Also, there will be several ranges of records that will have to be moved under the same identified column. Example: move I2 thru M2 to D2 move N2 thru R2 to D3 move S2 thru W2 to D4 (and so on until it finds the last record with the same import code) I have identified the columns that this information needs to be moved into, however I do not know the formula or code to tell it to do this? Can anyone help me? Thanks, J. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
automaticaly moving information
Rick: I think you have to insert rows otherwise you are going to overwrite
data in next row. I posted this solution in a newer posting. "Rick Rothstein (MVP - VB)" wrote: If Joel's guess if wrong, then for the example you posted, this macro will move the row of data to the column you specified... Sub MoveRow2Data() Dim X As Long, Z As Long Dim LastColumn As Long Const StartCol As Long = 9 Const GroupCount As Long = 5 Const MoveToColumn As Long = 4 Const DataRow As Long = 2 With Worksheets("Sheet1") LastColumn = .Cells(DataRow, .Columns.Count).End(xlToLeft).Column For X = StartCol To LastColumn Step 5 For Z = 0 To GroupCount - 1 .Cells(DataRow, X + Z).Copy _ Destination:=.Cells(X - 9 + DataRow + Z, MoveToColumn) Next Next .Cells(DataRow, StartCol).Resize(1, LastColumn - _ StartCol + 1).ClearContents End With End Sub Rick "J" wrote in message ... I am importing CSV records from a sales management application and the information, when imported, hits the sheet in the columns and each range needs to be moved into the correct columns. (This imported information is of a different size each time it is imported. ) Also, there will be several ranges of records that will have to be moved under the same identified column. Example: move I2 thru M2 to D2 move N2 thru R2 to D3 move S2 thru W2 to D4 (and so on until it finds the last record with the same import code) I have identified the columns that this information needs to be moved into, however I do not know the formula or code to tell it to do this? Can anyone help me? Thanks, J. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
automaticaly moving information
I didn't get the impression this was being done in an already populated
column (mainly because the example showed the starting row as 2); but, of course, given the example nature of the message, you could very well be right. Rick "Joel" wrote in message ... Rick: I think you have to insert rows otherwise you are going to overwrite data in next row. I posted this solution in a newer posting. "Rick Rothstein (MVP - VB)" wrote: If Joel's guess if wrong, then for the example you posted, this macro will move the row of data to the column you specified... Sub MoveRow2Data() Dim X As Long, Z As Long Dim LastColumn As Long Const StartCol As Long = 9 Const GroupCount As Long = 5 Const MoveToColumn As Long = 4 Const DataRow As Long = 2 With Worksheets("Sheet1") LastColumn = .Cells(DataRow, .Columns.Count).End(xlToLeft).Column For X = StartCol To LastColumn Step 5 For Z = 0 To GroupCount - 1 .Cells(DataRow, X + Z).Copy _ Destination:=.Cells(X - 9 + DataRow + Z, MoveToColumn) Next Next .Cells(DataRow, StartCol).Resize(1, LastColumn - _ StartCol + 1).ClearContents End With End Sub Rick "J" wrote in message ... I am importing CSV records from a sales management application and the information, when imported, hits the sheet in the columns and each range needs to be moved into the correct columns. (This imported information is of a different size each time it is imported. ) Also, there will be several ranges of records that will have to be moved under the same identified column. Example: move I2 thru M2 to D2 move N2 thru R2 to D3 move S2 thru W2 to D4 (and so on until it finds the last record with the same import code) I have identified the columns that this information needs to be moved into, however I do not know the formula or code to tell it to do this? Can anyone help me? Thanks, J. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
automaticaly moving information
Hi Rick and Joel,
I appreciate your help and I guess my explanation wasn't completely clear, so I will try it again and hopefully I won't make it worse. The Imported CSV information will be going onto and overwriting any existing information already on the sheet. And this is expected and it's ok because when the initial search query is done within the sales-management application, the search is always based on a new "date", so the information on the spreadsheet needs to be replaced. I am going to try the macro that you gave me, but it's been a very long time since I programmed code, so if you can give me any pointers that would be appreciated. Thanks J. "Rick Rothstein (MVP - VB)" wrote: I didn't get the impression this was being done in an already populated column (mainly because the example showed the starting row as 2); but, of course, given the example nature of the message, you could very well be right. Rick "Joel" wrote in message ... Rick: I think you have to insert rows otherwise you are going to overwrite data in next row. I posted this solution in a newer posting. "Rick Rothstein (MVP - VB)" wrote: If Joel's guess if wrong, then for the example you posted, this macro will move the row of data to the column you specified... Sub MoveRow2Data() Dim X As Long, Z As Long Dim LastColumn As Long Const StartCol As Long = 9 Const GroupCount As Long = 5 Const MoveToColumn As Long = 4 Const DataRow As Long = 2 With Worksheets("Sheet1") LastColumn = .Cells(DataRow, .Columns.Count).End(xlToLeft).Column For X = StartCol To LastColumn Step 5 For Z = 0 To GroupCount - 1 .Cells(DataRow, X + Z).Copy _ Destination:=.Cells(X - 9 + DataRow + Z, MoveToColumn) Next Next .Cells(DataRow, StartCol).Resize(1, LastColumn - _ StartCol + 1).ClearContents End With End Sub Rick "J" wrote in message ... I am importing CSV records from a sales management application and the information, when imported, hits the sheet in the columns and each range needs to be moved into the correct columns. (This imported information is of a different size each time it is imported. ) Also, there will be several ranges of records that will have to be moved under the same identified column. Example: move I2 thru M2 to D2 move N2 thru R2 to D3 move S2 thru W2 to D4 (and so on until it finds the last record with the same import code) I have identified the columns that this information needs to be moved into, however I do not know the formula or code to tell it to do this? Can anyone help me? Thanks, J. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
automaticaly moving information
I am going to try the macro that you gave me, but it's been a very long
time since I programmed code, so if you can give me any pointers that would be appreciated. Right click the worksheet tab that you want this functionality on, select View Code from the menu that pops up and Copy/Paste my macro into the window that opened up when you did that. Next, back on the worksheet, after your CSV file is imported into cell I2 through whatever column it end at, press Alt+F8, select MoveRow2Data from the list and click Run. That should be it (assuming I didn't screw up the code in my macro or that I didn't misinterpret your question). Rick |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
automaticaly moving information
HI Rick
I copy and pasted the macro but when I ran it I got a syntex error. Do my my ranges have to be noted on in the macro? My ranges a I2 thru M2 inserted unter D3 N2 thru R2 inserted under D4 S2 thru W2 inserted under D5 And so on .... I will be putting a math formula at the bottom of the D column when all of the info has be moved into place. I hope this makes more sense? If so, would you tell me how to modify the macro? Thanks, J. "Rick Rothstein (MVP - VB)" wrote: I am going to try the macro that you gave me, but it's been a very long time since I programmed code, so if you can give me any pointers that would be appreciated. Right click the worksheet tab that you want this functionality on, select View Code from the menu that pops up and Copy/Paste my macro into the window that opened up when you did that. Next, back on the worksheet, after your CSV file is imported into cell I2 through whatever column it end at, press Alt+F8, select MoveRow2Data from the list and click Run. That should be it (assuming I didn't screw up the code in my macro or that I didn't misinterpret your question). Rick |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
automaticaly moving information
What line did you get the "syntax error" on? By the way, I just re-ran the
code here on my system without any problem at all. I am using XL2003... what version are you using? Out of curiosity, did you remember to change the Worksheets("Sheet1") reference in the With statement to the name of your actual sheet? Rick "J" wrote in message ... HI Rick I copy and pasted the macro but when I ran it I got a syntex error. Do my my ranges have to be noted on in the macro? My ranges a I2 thru M2 inserted unter D3 N2 thru R2 inserted under D4 S2 thru W2 inserted under D5 And so on .... I will be putting a math formula at the bottom of the D column when all of the info has be moved into place. I hope this makes more sense? If so, would you tell me how to modify the macro? Thanks, J. "Rick Rothstein (MVP - VB)" wrote: I am going to try the macro that you gave me, but it's been a very long time since I programmed code, so if you can give me any pointers that would be appreciated. Right click the worksheet tab that you want this functionality on, select View Code from the menu that pops up and Copy/Paste my macro into the window that opened up when you did that. Next, back on the worksheet, after your CSV file is imported into cell I2 through whatever column it end at, press Alt+F8, select MoveRow2Data from the list and click Run. That should be it (assuming I didn't screw up the code in my macro or that I didn't misinterpret your question). Rick |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
automaticaly moving information
Rick,
I'm using Excel 2000 and, yes, I changed the sheet name to the one that I have renamed it too. Also, here's the error: Compile error Syntax Error And the first line is highlighted in Yellow with the second line highlighted in Blue, Thanks, "Rick Rothstein (MVP - VB)" wrote: What line did you get the "syntax error" on? By the way, I just re-ran the code here on my system without any problem at all. I am using XL2003... what version are you using? Out of curiosity, did you remember to change the Worksheets("Sheet1") reference in the With statement to the name of your actual sheet? Rick "J" wrote in message ... HI Rick I copy and pasted the macro but when I ran it I got a syntex error. Do my my ranges have to be noted on in the macro? My ranges a I2 thru M2 inserted unter D3 N2 thru R2 inserted under D4 S2 thru W2 inserted under D5 And so on .... I will be putting a math formula at the bottom of the D column when all of the info has be moved into place. I hope this makes more sense? If so, would you tell me how to modify the macro? Thanks, J. "Rick Rothstein (MVP - VB)" wrote: I am going to try the macro that you gave me, but it's been a very long time since I programmed code, so if you can give me any pointers that would be appreciated. Right click the worksheet tab that you want this functionality on, select View Code from the menu that pops up and Copy/Paste my macro into the window that opened up when you did that. Next, back on the worksheet, after your CSV file is imported into cell I2 through whatever column it end at, press Alt+F8, select MoveRow2Data from the list and click Run. That should be it (assuming I didn't screw up the code in my macro or that I didn't misinterpret your question). Rick |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
automaticaly moving information
Rick,
I just tried to save my file as a xls extention and I got the following errors: VBA 3821b - Internal Error 2709 and when asked to repair it, I got the next error message: Windows Installer Error 1603 So, it seems that it's my system that is most likely the problem. Would you agree? J. "J" wrote: Rick, I'm using Excel 2000 and, yes, I changed the sheet name to the one that I have renamed it too. Also, here's the error: Compile error Syntax Error And the first line is highlighted in Yellow with the second line highlighted in Blue, Thanks, "Rick Rothstein (MVP - VB)" wrote: What line did you get the "syntax error" on? By the way, I just re-ran the code here on my system without any problem at all. I am using XL2003... what version are you using? Out of curiosity, did you remember to change the Worksheets("Sheet1") reference in the With statement to the name of your actual sheet? Rick "J" wrote in message ... HI Rick I copy and pasted the macro but when I ran it I got a syntex error. Do my my ranges have to be noted on in the macro? My ranges a I2 thru M2 inserted unter D3 N2 thru R2 inserted under D4 S2 thru W2 inserted under D5 And so on .... I will be putting a math formula at the bottom of the D column when all of the info has be moved into place. I hope this makes more sense? If so, would you tell me how to modify the macro? Thanks, J. "Rick Rothstein (MVP - VB)" wrote: I am going to try the macro that you gave me, but it's been a very long time since I programmed code, so if you can give me any pointers that would be appreciated. Right click the worksheet tab that you want this functionality on, select View Code from the menu that pops up and Copy/Paste my macro into the window that opened up when you did that. Next, back on the worksheet, after your CSV file is imported into cell I2 through whatever column it end at, press Alt+F8, select MoveRow2Data from the list and click Run. That should be it (assuming I didn't screw up the code in my macro or that I didn't misinterpret your question). Rick |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
automaticaly moving information
I don't know... I wouldn't expect something in the system to declare lines
of code as having invalid syntax; and yet, there is no syntax error with the code that I posted, so who knows. I'm guessing you have rebooted your computer. While I've not had to do this myself, there is supposed to be a way to repair Office itself... have you tried to do that yet? Rick "J" wrote in message ... Rick, I just tried to save my file as a xls extention and I got the following errors: VBA 3821b - Internal Error 2709 and when asked to repair it, I got the next error message: Windows Installer Error 1603 So, it seems that it's my system that is most likely the problem. Would you agree? J. "J" wrote: Rick, I'm using Excel 2000 and, yes, I changed the sheet name to the one that I have renamed it too. Also, here's the error: Compile error Syntax Error And the first line is highlighted in Yellow with the second line highlighted in Blue, Thanks, "Rick Rothstein (MVP - VB)" wrote: What line did you get the "syntax error" on? By the way, I just re-ran the code here on my system without any problem at all. I am using XL2003... what version are you using? Out of curiosity, did you remember to change the Worksheets("Sheet1") reference in the With statement to the name of your actual sheet? Rick "J" wrote in message ... HI Rick I copy and pasted the macro but when I ran it I got a syntex error. Do my my ranges have to be noted on in the macro? My ranges a I2 thru M2 inserted unter D3 N2 thru R2 inserted under D4 S2 thru W2 inserted under D5 And so on .... I will be putting a math formula at the bottom of the D column when all of the info has be moved into place. I hope this makes more sense? If so, would you tell me how to modify the macro? Thanks, J. "Rick Rothstein (MVP - VB)" wrote: I am going to try the macro that you gave me, but it's been a very long time since I programmed code, so if you can give me any pointers that would be appreciated. Right click the worksheet tab that you want this functionality on, select View Code from the menu that pops up and Copy/Paste my macro into the window that opened up when you did that. Next, back on the worksheet, after your CSV file is imported into cell I2 through whatever column it end at, press Alt+F8, select MoveRow2Data from the list and click Run. That should be it (assuming I didn't screw up the code in my macro or that I didn't misinterpret your question). Rick |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
automaticaly moving information
Hi Rick,
Well I have spent some time "house cleaning" my system and I also "re-installed" Windows XP SP 2 Professional" and hopefully that will help. I reloaded the spreadsheet with your macro in it and then ran the macro. I was still getting the syntax error, so I removed all of the " " from the lines and now I don't get any colourful indications but I am getting the following error: "Script is out of Range" Would you take a look at it and see if you can determine why it's giving me that error? Here's a copy of the macro as it appears now: Thanks, J. Sub MoveRow2Data() Dim X As Long, Z As Long Dim LastColumn As Long Const StartCol As Long = 9 Const GroupCount As Long = 5 Const MoveToColumn As Long = 4 Const DataRow As Long = 2 With Worksheets(TrustDepositCSVfile) LastColumn = .Cells(DataRow, .Columns.Count).End(xlToLeft).Column For X = StartCol To LastColumn Step 5 For Z = 0 To GroupCount - 1 ..Cells(DataRow, X + Z).Copy _ Destination:=.Cells(X - 9 + DataRow + Z, MoveToColumn) Next Next ..Cells(DataRow, StartCol).Resize(1, LastColumn - _ StartCol + 1).ClearContents End With End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) End Sub "Rick Rothstein (MVP - VB)" wrote: I don't know... I wouldn't expect something in the system to declare lines of code as having invalid syntax; and yet, there is no syntax error with the code that I posted, so who knows. I'm guessing you have rebooted your computer. While I've not had to do this myself, there is supposed to be a way to repair Office itself... have you tried to do that yet? Rick "J" wrote in message ... Rick, I just tried to save my file as a xls extention and I got the following errors: VBA 3821b - Internal Error 2709 and when asked to repair it, I got the next error message: Windows Installer Error 1603 So, it seems that it's my system that is most likely the problem. Would you agree? J. "J" wrote: Rick, I'm using Excel 2000 and, yes, I changed the sheet name to the one that I have renamed it too. Also, here's the error: Compile error Syntax Error And the first line is highlighted in Yellow with the second line highlighted in Blue, Thanks, "Rick Rothstein (MVP - VB)" wrote: What line did you get the "syntax error" on? By the way, I just re-ran the code here on my system without any problem at all. I am using XL2003... what version are you using? Out of curiosity, did you remember to change the Worksheets("Sheet1") reference in the With statement to the name of your actual sheet? Rick "J" wrote in message ... HI Rick I copy and pasted the macro but when I ran it I got a syntex error. Do my my ranges have to be noted on in the macro? My ranges a I2 thru M2 inserted unter D3 N2 thru R2 inserted under D4 S2 thru W2 inserted under D5 And so on .... I will be putting a math formula at the bottom of the D column when all of the info has be moved into place. I hope this makes more sense? If so, would you tell me how to modify the macro? Thanks, J. "Rick Rothstein (MVP - VB)" wrote: I am going to try the macro that you gave me, but it's been a very long time since I programmed code, so if you can give me any pointers that would be appreciated. Right click the worksheet tab that you want this functionality on, select View Code from the menu that pops up and Copy/Paste my macro into the window that opened up when you did that. Next, back on the worksheet, after your CSV file is imported into cell I2 through whatever column it end at, press Alt+F8, select MoveRow2Data from the list and click Run. That should be it (assuming I didn't screw up the code in my macro or that I didn't misinterpret your question). Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I put in information without the page moving | Excel Discussion (Misc queries) | |||
moving Imported information | Excel Discussion (Misc queries) | |||
Moving Data Automaticaly | New Users to Excel | |||
Moving Information | Excel Worksheet Functions | |||
how do i can send a information of cell automaticaly via email? | New Users to Excel |