Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help w/ look & fill in data
Hello,
I have two different data in two worksheets, worksheet 1 is the main data that I would like to create a macro for. Sheet2 contains raw data that I pull daily or weekly. What I would like to achieve for the VBA macro is to dump the raw data in sheet2 into sheet1, but put into the right cell. The macro needs to be able to: 1) Fill in the s/o number to column D:G based on the latest operation number for each model & part# type column. 2) Each model in the row will always have 2 (1300) & 2 (1500) 3) The row with "vendor" fill in do not require s/o fill in. 4) Column H:K, I can do a Vlookup to fill in the operation number from the raw data. Example: for row with No. 1002 & 1003 that have the same model, 218029 (1300) @ operation 100 is a later operation than 215789 (1300) @ operation 90, so the first s/o to fill in row with 1002 is 218031 and so on. Here is the data sample: Sheet1 No. ShipDate Model 1300 1300 1500 1500 Operation Status 1001 17-Aug 100 vendor vendor vendor vendor #N/A #N/A #N/A #N/A 1002 18-Aug 200 218029 218031 215226 214526 100 95 320 197 1003 19-Aug 200 215789 218033 216999 216998 90 90 320 162 1004 19-Aug 100 215048 215780 215778 217693 90 90 410 180 1005 20-Aug 100 vendor vendor vendor vendor #N/A #N/A #N/A #N/A 1006 21-Aug 200 218035 216994 217000 218041 90 80 162 165 1007 25-Aug 100 216966 218975 218042 217001 80 70 300 150 1008 25-Aug 100 215765 215769 215779 215785 50 60 250 100 Sheet2 Raw Data S/O Operation Ref Model 218029 100 1300 200 215789 90 1300 200 215048 90 1300 100 218035 90 1300 200 216966 80 1300 100 215765 50 1300 100 218974 20 1300 200 218031 95 1300 200 218033 90 1300 200 215780 90 1300 100 216994 80 1300 200 218975 180 1300 100 215769 320 1300 100 219680 150 1300 200 215226 320 1500 200 216999 320 1500 200 215778 410 1500 100 217000 162 1500 200 218042 150 1500 100 215779 250 1500 100 215790 110 1500 200 214526 197 1500 200 216998 162 1500 200 217693 180 1500 100 218041 165 1500 200 217001 150 1500 100 215785 10 1500 100 218039 110 1500 200 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help w/ look & fill in data
I would of responded right after your posted your request, but I was
confused. After a long time thinking about what you want I need more details. I've been trying to determine if you are adding new rows to sheet 1, or filling in data like you requested stated. It seems to make more sense that you are adding new rows on a daily bases. The question I have about this method are as follows: 1) Where does the date come from? 2) Is it the date when the entry is made? 3) Is your Raw data really a group of data from several days of entries? If so can you split the raw data by dates? 4) Does it make a diference if the raw data goes into the first or 2nd column for each model? Is it acceptable for the 1st raw data to go into the left column and the 2nd in the right column? If you are filling in the table I'm have different questions: 1) Can you give an exanmple of the empty table before the raw data is entered. 2) How do you know which entry is for each date? 3) Does it make a diference if the raw data goes into the first or 2nd column for each model? "Cam" wrote: Hello, I have two different data in two worksheets, worksheet 1 is the main data that I would like to create a macro for. Sheet2 contains raw data that I pull daily or weekly. What I would like to achieve for the VBA macro is to dump the raw data in sheet2 into sheet1, but put into the right cell. The macro needs to be able to: 1) Fill in the s/o number to column D:G based on the latest operation number for each model & part# type column. 2) Each model in the row will always have 2 (1300) & 2 (1500) 3) The row with "vendor" fill in do not require s/o fill in. 4) Column H:K, I can do a Vlookup to fill in the operation number from the raw data. Example: for row with No. 1002 & 1003 that have the same model, 218029 (1300) @ operation 100 is a later operation than 215789 (1300) @ operation 90, so the first s/o to fill in row with 1002 is 218031 and so on. Here is the data sample: Sheet1 No. ShipDate Model 1300 1300 1500 1500 Operation Status 1001 17-Aug 100 vendor vendor vendor vendor #N/A #N/A #N/A #N/A 1002 18-Aug 200 218029 218031 215226 214526 100 95 320 197 1003 19-Aug 200 215789 218033 216999 216998 90 90 320 162 1004 19-Aug 100 215048 215780 215778 217693 90 90 410 180 1005 20-Aug 100 vendor vendor vendor vendor #N/A #N/A #N/A #N/A 1006 21-Aug 200 218035 216994 217000 218041 90 80 162 165 1007 25-Aug 100 216966 218975 218042 217001 80 70 300 150 1008 25-Aug 100 215765 215769 215779 215785 50 60 250 100 Sheet2 Raw Data S/O Operation Ref Model 218029 100 1300 200 215789 90 1300 200 215048 90 1300 100 218035 90 1300 200 216966 80 1300 100 215765 50 1300 100 218974 20 1300 200 218031 95 1300 200 218033 90 1300 200 215780 90 1300 100 216994 80 1300 200 218975 180 1300 100 215769 320 1300 100 219680 150 1300 200 215226 320 1500 200 216999 320 1500 200 215778 410 1500 100 217000 162 1500 200 218042 150 1500 100 215779 250 1500 100 215790 110 1500 200 214526 197 1500 200 216998 162 1500 200 217693 180 1500 100 218041 165 1500 200 217001 150 1500 100 215785 10 1500 100 218039 110 1500 200 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help w/ look & fill in data
Hello Joel,
I will try to explain as clear as possible. 1) Where does the date come from? The date came from a raw data from sheet2 so every S/O has a date field. 2) Is it the date when the entry is made? It is not the date where the entry is made. 3) Is your Raw data really a group of data from several days of entries? If so can you split the raw data by dates? The raw data is pulled from our system so everyday the S/O can be obtained and will have higher operation number depending whether there are work done on that S/O, when all the operations are completed, it will have a "closed" status. 4) Does it make a diference if the raw data goes into the first or 2nd column for each model? Is it acceptable for the 1st raw data to go into the left column and the 2nd in the right column? No, it does not make any different if which 1st or 2nd column of each model. Yes, it is acceptable. I hope this explain what I am trying to do manually and hopefully can translate into macro automated. The No. column from sheet1 is the next product due for the customer. There are two difference models (100 or 200)for each No. There are 4 parts (1300 (2) & 1500 (2)) to complete each No. The raw data have S/O that are tied to 1300 or 1500 ref. Each S/O goes through 15 operations before it is completed. Everyday, the operation status of the S/O changes depending on what operation it is at. The important thing is that we want to make sure that we are working on the S/O with the right model for the next No. From the data below, the next No. would be 1002 which is model 200 (we skip No. 1001 because it is outsource to a vendor). The raw data would have S/O for model 200 at different operation status. The macro would than find the right S/O based on the model 200 and latest operation, then fill in 4 columns of each No. Everyday the operation for each S/O is updated once I pulled the raw data in sheet2. Thank for your help. "Joel" wrote: I would of responded right after your posted your request, but I was confused. After a long time thinking about what you want I need more details. I've been trying to determine if you are adding new rows to sheet 1, or filling in data like you requested stated. It seems to make more sense that you are adding new rows on a daily bases. The question I have about this method are as follows: 1) Where does the date come from? 2) Is it the date when the entry is made? 3) Is your Raw data really a group of data from several days of entries? If so can you split the raw data by dates? 4) Does it make a diference if the raw data goes into the first or 2nd column for each model? Is it acceptable for the 1st raw data to go into the left column and the 2nd in the right column? If you are filling in the table I'm have different questions: 1) Can you give an exanmple of the empty table before the raw data is entered. 2) How do you know which entry is for each date? 3) Does it make a diference if the raw data goes into the first or 2nd column for each model? "Cam" wrote: Hello, I have two different data in two worksheets, worksheet 1 is the main data that I would like to create a macro for. Sheet2 contains raw data that I pull daily or weekly. What I would like to achieve for the VBA macro is to dump the raw data in sheet2 into sheet1, but put into the right cell. The macro needs to be able to: 1) Fill in the s/o number to column D:G based on the latest operation number for each model & part# type column. 2) Each model in the row will always have 2 (1300) & 2 (1500) 3) The row with "vendor" fill in do not require s/o fill in. 4) Column H:K, I can do a Vlookup to fill in the operation number from the raw data. Example: for row with No. 1002 & 1003 that have the same model, 218029 (1300) @ operation 100 is a later operation than 215789 (1300) @ operation 90, so the first s/o to fill in row with 1002 is 218031 and so on. Here is the data sample: Sheet1 No. ShipDate Model 1300 1300 1500 1500 Operation Status 1001 17-Aug 100 vendor vendor vendor vendor #N/A #N/A #N/A #N/A 1002 18-Aug 200 218029 218031 215226 214526 100 95 320 197 1003 19-Aug 200 215789 218033 216999 216998 90 90 320 162 1004 19-Aug 100 215048 215780 215778 217693 90 90 410 180 1005 20-Aug 100 vendor vendor vendor vendor #N/A #N/A #N/A #N/A 1006 21-Aug 200 218035 216994 217000 218041 90 80 162 165 1007 25-Aug 100 216966 218975 218042 217001 80 70 300 150 1008 25-Aug 100 215765 215769 215779 215785 50 60 250 100 Sheet2 Raw Data S/O Operation Ref Model 218029 100 1300 200 215789 90 1300 200 215048 90 1300 100 218035 90 1300 200 216966 80 1300 100 215765 50 1300 100 218974 20 1300 200 218031 95 1300 200 218033 90 1300 200 215780 90 1300 100 216994 80 1300 200 218975 180 1300 100 215769 320 1300 100 219680 150 1300 200 215226 320 1500 200 216999 320 1500 200 215778 410 1500 100 217000 162 1500 200 218042 150 1500 100 215779 250 1500 100 215790 110 1500 200 214526 197 1500 200 216998 162 1500 200 217693 180 1500 100 218041 165 1500 200 217001 150 1500 100 215785 10 1500 100 218039 110 1500 200 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help w/ look & fill in data
Belwo is the code I have right now. Make sure there is no spaces in the
Model Number or Operation Number in sheet 1 or 2. I'm treating them as number. they should be right justified in the cell with no double quotes in front of the numbers. I can change the code if you are using text. I'm still having problems. All the rows get the same data. I think you want a new row, but didn't really say that. I don't have a date to start a new row nor the 1001-1008 number. I'm not sure if there are two operations am I suppose to look for the two highest operation numbers in sheet 2 and then add a new row? What I can see it looks like its the columns H:K is what is changing every day, not D:G. My code is changing D:G Sub checkso() LastRowSh1 = Sheets("Sheet1"). _ Cells(Rows.Count, "A").End(xlUp).Row LastRowSh2 = Sheets("Sheet2"). _ Cells(Rows.Count, "A").End(xlUp).Row Sheets("Sheet1").Activate With Sheets("Sheet2") For Sh1RowCount = 2 To LastRowSh1 If Cells(Sh1RowCount, "D").Value < _ "vendor" Then Model = Cells(Sh1RowCount, "C").Value For Refcol = 4 To 6 Step 2 Ref = Cells(1, Refcol).Value ItemOffset = 0 Found = False For Sh2RowCount = _ 2 To LastRowSh2 If (.Cells(Sh2RowCount, "C").Value = _ Ref) And _ (.Cells(Sh2RowCount, "D").Value = _ Model) Then If ItemOffset = 0 Then Cells(Sh1RowCount, Refcol) = _ .Cells(Sh2RowCount, "A").Value ItemOffset = 1 Else Cells(Sh1RowCount, Refcol + 1) = _ .Cells(Sh2RowCount, "A").Value Found = True Exit For End If End If Next Sh2RowCount If Found = False Then If ItemOffset = 0 Then Cells(Sh1RowCount, Refcol) = "" Cells(Sh1RowCount, Refcol + 1) = "" Else Cells(Sh1RowCount, Refcol + 1) = "" End If End If Next Refcol End If Next Sh1RowCount End With End Sub "Cam" wrote: Hello Joel, I will try to explain as clear as possible. 1) Where does the date come from? The date came from a raw data from sheet2 so every S/O has a date field. 2) Is it the date when the entry is made? It is not the date where the entry is made. 3) Is your Raw data really a group of data from several days of entries? If so can you split the raw data by dates? The raw data is pulled from our system so everyday the S/O can be obtained and will have higher operation number depending whether there are work done on that S/O, when all the operations are completed, it will have a "closed" status. 4) Does it make a diference if the raw data goes into the first or 2nd column for each model? Is it acceptable for the 1st raw data to go into the left column and the 2nd in the right column? No, it does not make any different if which 1st or 2nd column of each model. Yes, it is acceptable. I hope this explain what I am trying to do manually and hopefully can translate into macro automated. The No. column from sheet1 is the next product due for the customer. There are two difference models (100 or 200)for each No. There are 4 parts (1300 (2) & 1500 (2)) to complete each No. The raw data have S/O that are tied to 1300 or 1500 ref. Each S/O goes through 15 operations before it is completed. Everyday, the operation status of the S/O changes depending on what operation it is at. The important thing is that we want to make sure that we are working on the S/O with the right model for the next No. From the data below, the next No. would be 1002 which is model 200 (we skip No. 1001 because it is outsource to a vendor). The raw data would have S/O for model 200 at different operation status. The macro would than find the right S/O based on the model 200 and latest operation, then fill in 4 columns of each No. Everyday the operation for each S/O is updated once I pulled the raw data in sheet2. Thank for your help. "Joel" wrote: I would of responded right after your posted your request, but I was confused. After a long time thinking about what you want I need more details. I've been trying to determine if you are adding new rows to sheet 1, or filling in data like you requested stated. It seems to make more sense that you are adding new rows on a daily bases. The question I have about this method are as follows: 1) Where does the date come from? 2) Is it the date when the entry is made? 3) Is your Raw data really a group of data from several days of entries? If so can you split the raw data by dates? 4) Does it make a diference if the raw data goes into the first or 2nd column for each model? Is it acceptable for the 1st raw data to go into the left column and the 2nd in the right column? If you are filling in the table I'm have different questions: 1) Can you give an exanmple of the empty table before the raw data is entered. 2) How do you know which entry is for each date? 3) Does it make a diference if the raw data goes into the first or 2nd column for each model? "Cam" wrote: Hello, I have two different data in two worksheets, worksheet 1 is the main data that I would like to create a macro for. Sheet2 contains raw data that I pull daily or weekly. What I would like to achieve for the VBA macro is to dump the raw data in sheet2 into sheet1, but put into the right cell. The macro needs to be able to: 1) Fill in the s/o number to column D:G based on the latest operation number for each model & part# type column. 2) Each model in the row will always have 2 (1300) & 2 (1500) 3) The row with "vendor" fill in do not require s/o fill in. 4) Column H:K, I can do a Vlookup to fill in the operation number from the raw data. Example: for row with No. 1002 & 1003 that have the same model, 218029 (1300) @ operation 100 is a later operation than 215789 (1300) @ operation 90, so the first s/o to fill in row with 1002 is 218031 and so on. Here is the data sample: Sheet1 No. ShipDate Model 1300 1300 1500 1500 Operation Status 1001 17-Aug 100 vendor vendor vendor vendor #N/A #N/A #N/A #N/A 1002 18-Aug 200 218029 218031 215226 214526 100 95 320 197 1003 19-Aug 200 215789 218033 216999 216998 90 90 320 162 1004 19-Aug 100 215048 215780 215778 217693 90 90 410 180 1005 20-Aug 100 vendor vendor vendor vendor #N/A #N/A #N/A #N/A 1006 21-Aug 200 218035 216994 217000 218041 90 80 162 165 1007 25-Aug 100 216966 218975 218042 217001 80 70 300 150 1008 25-Aug 100 215765 215769 215779 215785 50 60 250 100 Sheet2 Raw Data S/O Operation Ref Model 218029 100 1300 200 215789 90 1300 200 215048 90 1300 100 218035 90 1300 200 216966 80 1300 100 215765 50 1300 100 218974 20 1300 200 218031 95 1300 200 218033 90 1300 200 215780 90 1300 100 216994 80 1300 200 218975 180 1300 100 215769 320 1300 100 219680 150 1300 200 215226 320 1500 200 216999 320 1500 200 215778 410 1500 100 217000 162 1500 200 218042 150 1500 100 215779 250 1500 100 215790 110 1500 200 214526 197 1500 200 216998 162 1500 200 217693 180 1500 100 218041 165 1500 200 217001 150 1500 100 215785 10 1500 100 218039 110 1500 200 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help w/ look & fill in data
Joel,
Thank for your response. I'm still having problems. All the rows get the same data. I think you want a new row, but didn't really say that. I don't have a date to start a new row nor the 1001-1008 number. I do want to start a new row, but the rows can be the same data depending if there is any changes to the operation. I'm not sure if there are two operations am I suppose to look for the two highest operation numbers in sheet 2 and then add a new row? Yes, for 1360 Ref column, pick the two highest operations and fill in the data in sheet1, then fill in the next row with the next highest operation, same for 1560. What I can see it looks like its the columns H:K is what is changing every day, not D:G. My code is changing D:G Both D:G & H:K could be different every day where D:G changes once part# is replaced or pulled out. Then reshuffle the fill in part#. H:K changes depending on moving up the operation number. Also, I ran the code and it kept with a mismatch type error. The model & operation need to be changed to general or text instead of number. Thank again. "Joel" wrote: Belwo is the code I have right now. Make sure there is no spaces in the Model Number or Operation Number in sheet 1 or 2. I'm treating them as number. they should be right justified in the cell with no double quotes in front of the numbers. I can change the code if you are using text. I'm still having problems. All the rows get the same data. I think you want a new row, but didn't really say that. I don't have a date to start a new row nor the 1001-1008 number. I'm not sure if there are two operations am I suppose to look for the two highest operation numbers in sheet 2 and then add a new row? What I can see it looks like its the columns H:K is what is changing every day, not D:G. My code is changing D:G Sub checkso() LastRowSh1 = Sheets("Sheet1"). _ Cells(Rows.Count, "A").End(xlUp).Row LastRowSh2 = Sheets("Sheet2"). _ Cells(Rows.Count, "A").End(xlUp).Row Sheets("Sheet1").Activate With Sheets("Sheet2") For Sh1RowCount = 2 To LastRowSh1 If Cells(Sh1RowCount, "D").Value < _ "vendor" Then Model = Cells(Sh1RowCount, "C").Value For Refcol = 4 To 6 Step 2 Ref = Cells(1, Refcol).Value ItemOffset = 0 Found = False For Sh2RowCount = _ 2 To LastRowSh2 If (.Cells(Sh2RowCount, "C").Value = _ Ref) And _ (.Cells(Sh2RowCount, "D").Value = _ Model) Then If ItemOffset = 0 Then Cells(Sh1RowCount, Refcol) = _ .Cells(Sh2RowCount, "A").Value ItemOffset = 1 Else Cells(Sh1RowCount, Refcol + 1) = _ .Cells(Sh2RowCount, "A").Value Found = True Exit For End If End If Next Sh2RowCount If Found = False Then If ItemOffset = 0 Then Cells(Sh1RowCount, Refcol) = "" Cells(Sh1RowCount, Refcol + 1) = "" Else Cells(Sh1RowCount, Refcol + 1) = "" End If End If Next Refcol End If Next Sh1RowCount End With End Sub "Cam" wrote: Hello Joel, I will try to explain as clear as possible. 1) Where does the date come from? The date came from a raw data from sheet2 so every S/O has a date field. 2) Is it the date when the entry is made? It is not the date where the entry is made. 3) Is your Raw data really a group of data from several days of entries? If so can you split the raw data by dates? The raw data is pulled from our system so everyday the S/O can be obtained and will have higher operation number depending whether there are work done on that S/O, when all the operations are completed, it will have a "closed" status. 4) Does it make a diference if the raw data goes into the first or 2nd column for each model? Is it acceptable for the 1st raw data to go into the left column and the 2nd in the right column? No, it does not make any different if which 1st or 2nd column of each model. Yes, it is acceptable. I hope this explain what I am trying to do manually and hopefully can translate into macro automated. The No. column from sheet1 is the next product due for the customer. There are two difference models (100 or 200)for each No. There are 4 parts (1300 (2) & 1500 (2)) to complete each No. The raw data have S/O that are tied to 1300 or 1500 ref. Each S/O goes through 15 operations before it is completed. Everyday, the operation status of the S/O changes depending on what operation it is at. The important thing is that we want to make sure that we are working on the S/O with the right model for the next No. From the data below, the next No. would be 1002 which is model 200 (we skip No. 1001 because it is outsource to a vendor). The raw data would have S/O for model 200 at different operation status. The macro would than find the right S/O based on the model 200 and latest operation, then fill in 4 columns of each No. Everyday the operation for each S/O is updated once I pulled the raw data in sheet2. Thank for your help. "Joel" wrote: I would of responded right after your posted your request, but I was confused. After a long time thinking about what you want I need more details. I've been trying to determine if you are adding new rows to sheet 1, or filling in data like you requested stated. It seems to make more sense that you are adding new rows on a daily bases. The question I have about this method are as follows: 1) Where does the date come from? 2) Is it the date when the entry is made? 3) Is your Raw data really a group of data from several days of entries? If so can you split the raw data by dates? 4) Does it make a diference if the raw data goes into the first or 2nd column for each model? Is it acceptable for the 1st raw data to go into the left column and the 2nd in the right column? If you are filling in the table I'm have different questions: 1) Can you give an exanmple of the empty table before the raw data is entered. 2) How do you know which entry is for each date? 3) Does it make a diference if the raw data goes into the first or 2nd column for each model? "Cam" wrote: Hello, I have two different data in two worksheets, worksheet 1 is the main data that I would like to create a macro for. Sheet2 contains raw data that I pull daily or weekly. What I would like to achieve for the VBA macro is to dump the raw data in sheet2 into sheet1, but put into the right cell. The macro needs to be able to: 1) Fill in the s/o number to column D:G based on the latest operation number for each model & part# type column. 2) Each model in the row will always have 2 (1300) & 2 (1500) 3) The row with "vendor" fill in do not require s/o fill in. 4) Column H:K, I can do a Vlookup to fill in the operation number from the raw data. Example: for row with No. 1002 & 1003 that have the same model, 218029 (1300) @ operation 100 is a later operation than 215789 (1300) @ operation 90, so the first s/o to fill in row with 1002 is 218031 and so on. Here is the data sample: Sheet1 No. ShipDate Model 1300 1300 1500 1500 Operation Status 1001 17-Aug 100 vendor vendor vendor vendor #N/A #N/A #N/A #N/A 1002 18-Aug 200 218029 218031 215226 214526 100 95 320 197 1003 19-Aug 200 215789 218033 216999 216998 90 90 320 162 1004 19-Aug 100 215048 215780 215778 217693 90 90 410 180 1005 20-Aug 100 vendor vendor vendor vendor #N/A #N/A #N/A #N/A 1006 21-Aug 200 218035 216994 217000 218041 90 80 162 165 1007 25-Aug 100 216966 218975 218042 217001 80 70 300 150 1008 25-Aug 100 215765 215769 215779 215785 50 60 250 100 Sheet2 Raw Data S/O Operation Ref Model 218029 100 1300 200 215789 90 1300 200 215048 90 1300 100 218035 90 1300 200 216966 80 1300 100 215765 50 1300 100 218974 20 1300 200 218031 95 1300 200 218033 90 1300 200 215780 90 1300 100 216994 80 1300 200 218975 180 1300 100 215769 320 1300 100 219680 150 1300 200 215226 320 1500 200 216999 320 1500 200 215778 410 1500 100 217000 162 1500 200 218042 150 1500 100 215779 250 1500 100 215790 110 1500 200 214526 197 1500 200 216998 162 1500 200 217693 180 1500 100 218041 165 1500 200 217001 150 1500 100 215785 10 1500 100 218039 110 1500 200 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help w/ look & fill in data
You are going to have to fix the problems with your worksheet. Apparently
you have spaces the cells that contain numbers. Make sure all the numbers are right justified in the cell (left justified indicates they are text). You can easily get rid of the spaces by using the Spreadsheet Replace (under edit menu). Highligh the cells containing number. the replace a single space with nothing. Just enter a space in the from box and leave the replace box empty. The code becomes very complex to handle numbers and text mixed. fix both sheet 1 and 2. Below is the code I think you want. I put in the Operation number and well as the SO number. Sub checkso() Const High2 = 0 Const High1 = 1 Const OP = 0 Const SO = 1 Dim R1300M100(2, 2) Dim R1300M200(2, 2) Dim R1500M100(2, 2) Dim R1500M200(2, 2) R1300M100(High1, OP) = 0 R1300M100(High2, OP) = 0 R1300M100(High1, SO) = 0 R1300M100(High2, SO) = 0 R1300M200(High1, OP) = 0 R1300M200(High2, OP) = 0 R1300M200(High1, SO) = 0 R1300M200(High2, SO) = 0 R1500M100(High1, OP) = 0 R1500M100(High2, OP) = 0 R1500M100(High1, SO) = 0 R1500M100(High2, SO) = 0 R1500M200(High1, OP) = 0 R1500M200(High2, OP) = 0 R1500M200(High1, SO) = 0 R1500M200(High2, SO) = 0 LastRowSh1 = Sheets("Sheet1"). _ Cells(Rows.Count, "A").End(xlUp).Row LastRowSh2 = Sheets("Sheet2"). _ Cells(Rows.Count, "A").End(xlUp).Row Sh1NewRow = LastRowSh1 + 1 With Sheets("Sheet2") For Sh2RowCount = 2 To LastRowSh2 Operation = .Cells(Sh2RowCount, "B") ShippingOrder = .Cells(Sh2RowCount, "A") If .Cells(Sh2RowCount, "C").Value = 1300 Then If .Cells(Sh2RowCount, "D").Value = 100 Then If Operation R1300M100(High1, OP) Then R1300M100(High2, OP) = _ R1300M100(High1, OP) R1300M100(High1, OP) = _ Operation R1300M100(High2, SO) = _ R1300M100(High1, SO) R1300M100(High1, SO) = _ ShippingOrder Else If Operation R1300M100(High2, OP) Then R1300M100(High2, OP) = _ Operation R1300M100(High2, SO) = _ ShippingOrder End If End If End If If .Cells(Sh2RowCount, "D").Value = 200 Then If Operation R1300M200(High1, OP) Then R1300M200(High2, OP) = _ R1300M200(High1, OP) R1300M200(High1, OP) = _ Operation R1300M200(High2, SO) = _ R1300M200(High1, SO) R1300M200(High1, SO) = _ ShippingOrder Else If Operation R1300M200(High2, OP) Then R1300M200(High2, OP) = _ Operation R1300M200(High2, SO) = _ ShippingOrder End If End If End If End If If .Cells(Sh2RowCount, "C").Value = 1500 Then If .Cells(Sh2RowCount, "D").Value = 100 Then If Operation R1500M100(High1, OP) Then R1500M100(High2, OP) = _ R1500M100(High1, OP) R1500M100(High1, OP) = _ Operation R1500M100(High2, SO) = _ R1500M100(High1, SO) R1500M100(High1, SO) = _ ShippingOrder Else If Operation R1500M100(High2, OP) Then R1500M100(High2, OP) = _ Operation R1500M100(High2, SO) = _ ShippingOrder End If End If End If If .Cells(Sh2RowCount, "D").Value = 200 Then If Operation R1500M200(High1, OP) Then R1500M200(High2, OP) = _ R1500M200(High1, OP) R1500M200(High1, OP) = _ Operation R1500M200(High2, SO) = _ R1500M200(High1, SO) R1500M200(High1, SO) = _ ShippingOrder Else If Operation R1500M200(High2, OP) Then R1500M200(High2, OP) = _ Operation R1500M200(High2, SO) = _ ShippingOrder End If End If End If End If Next Sh2RowCount End With With Sheets("Sheet1") .Cells(Sh1NewRow, "C").Value = 100 .Cells(Sh1NewRow, "D").Value = _ R1300M100(High1, SO) .Cells(Sh1NewRow, "E").Value = _ R1300M100(High2, SO) .Cells(Sh1NewRow, "F").Value = _ R1500M100(High1, SO) .Cells(Sh1NewRow, "G").Value = _ R1500M100(High2, SO) .Cells(Sh1NewRow, "H").Value = _ R1300M100(High1, OP) .Cells(Sh1NewRow, "I").Value = _ R1300M100(High2, OP) .Cells(Sh1NewRow, "J").Value = _ R1500M100(High1, OP) .Cells(Sh1NewRow, "K").Value = _ R1500M100(High2, OP) Sh1NewRow = Sh1NewRow + 1 .Cells(Sh1NewRow, "C").Value = 200 .Cells(Sh1NewRow, "D").Value = _ R1300M200(High1, SO) .Cells(Sh1NewRow, "E").Value = _ R1300M200(High2, SO) .Cells(Sh1NewRow, "F").Value = _ R1500M200(High1, SO) .Cells(Sh1NewRow, "G").Value = _ R1500M200(High2, SO) .Cells(Sh1NewRow, "H").Value = _ R1300M200(High1, OP) .Cells(Sh1NewRow, "I").Value = _ R1300M200(High2, OP) .Cells(Sh1NewRow, "J").Value = _ R1500M200(High1, OP) .Cells(Sh1NewRow, "K").Value = _ R1500M200(High2, OP) End With End Sub "Cam" wrote: Joel, Thank for your response. I'm still having problems. All the rows get the same data. I think you want a new row, but didn't really say that. I don't have a date to start a new row nor the 1001-1008 number. I do want to start a new row, but the rows can be the same data depending if there is any changes to the operation. I'm not sure if there are two operations am I suppose to look for the two highest operation numbers in sheet 2 and then add a new row? Yes, for 1360 Ref column, pick the two highest operations and fill in the data in sheet1, then fill in the next row with the next highest operation, same for 1560. What I can see it looks like its the columns H:K is what is changing every day, not D:G. My code is changing D:G Both D:G & H:K could be different every day where D:G changes once part# is replaced or pulled out. Then reshuffle the fill in part#. H:K changes depending on moving up the operation number. Also, I ran the code and it kept with a mismatch type error. The model & operation need to be changed to general or text instead of number. Thank again. "Joel" wrote: Belwo is the code I have right now. Make sure there is no spaces in the Model Number or Operation Number in sheet 1 or 2. I'm treating them as number. they should be right justified in the cell with no double quotes in front of the numbers. I can change the code if you are using text. I'm still having problems. All the rows get the same data. I think you want a new row, but didn't really say that. I don't have a date to start a new row nor the 1001-1008 number. I'm not sure if there are two operations am I suppose to look for the two highest operation numbers in sheet 2 and then add a new row? What I can see it looks like its the columns H:K is what is changing every day, not D:G. My code is changing D:G Sub checkso() LastRowSh1 = Sheets("Sheet1"). _ Cells(Rows.Count, "A").End(xlUp).Row LastRowSh2 = Sheets("Sheet2"). _ Cells(Rows.Count, "A").End(xlUp).Row Sheets("Sheet1").Activate With Sheets("Sheet2") For Sh1RowCount = 2 To LastRowSh1 If Cells(Sh1RowCount, "D").Value < _ "vendor" Then Model = Cells(Sh1RowCount, "C").Value For Refcol = 4 To 6 Step 2 Ref = Cells(1, Refcol).Value ItemOffset = 0 Found = False For Sh2RowCount = _ 2 To LastRowSh2 If (.Cells(Sh2RowCount, "C").Value = _ Ref) And _ (.Cells(Sh2RowCount, "D").Value = _ Model) Then If ItemOffset = 0 Then Cells(Sh1RowCount, Refcol) = _ .Cells(Sh2RowCount, "A").Value ItemOffset = 1 Else Cells(Sh1RowCount, Refcol + 1) = _ .Cells(Sh2RowCount, "A").Value Found = True Exit For End If End If Next Sh2RowCount If Found = False Then If ItemOffset = 0 Then Cells(Sh1RowCount, Refcol) = "" Cells(Sh1RowCount, Refcol + 1) = "" Else Cells(Sh1RowCount, Refcol + 1) = "" End If End If Next Refcol End If Next Sh1RowCount End With End Sub "Cam" wrote: Hello Joel, I will try to explain as clear as possible. 1) Where does the date come from? The date came from a raw data from sheet2 so every S/O has a date field. 2) Is it the date when the entry is made? It is not the date where the entry is made. 3) Is your Raw data really a group of data from several days of entries? If so can you split the raw data by dates? The raw data is pulled from our system so everyday the S/O can be obtained and will have higher operation number depending whether there are work done on that S/O, when all the operations are completed, it will have a "closed" status. 4) Does it make a diference if the raw data goes into the first or 2nd column for each model? Is it acceptable for the 1st raw data to go into the left column and the 2nd in the right column? No, it does not make any different if which 1st or 2nd column of each model. Yes, it is acceptable. I hope this explain what I am trying to do manually and hopefully can translate into macro automated. The No. column from sheet1 is the next product due for the customer. There are two difference models (100 or 200)for each No. There are 4 parts (1300 (2) & 1500 (2)) to complete each No. The raw data have S/O that are tied to 1300 or 1500 ref. Each S/O goes through 15 operations before it is completed. Everyday, the operation status of the S/O changes depending on what operation it is at. The important thing is that we want to make sure that we are working on the S/O with the right model for the next No. From the data below, the next No. would be 1002 which is model 200 (we skip No. 1001 because it is outsource to a vendor). The raw data would have S/O for model 200 at different operation status. The macro would than find the right S/O based on the model 200 and latest operation, then fill in 4 columns of each No. Everyday the operation for each S/O is updated once I pulled the raw data in sheet2. Thank for your help. "Joel" wrote: I would of responded right after your posted your request, but I was confused. After a long time thinking about what you want I need more details. I've been trying to determine if you are adding new rows to sheet 1, or filling in data like you requested stated. It seems to make more sense that you are adding new rows on a daily bases. The question I have about this method are as follows: 1) Where does the date come from? 2) Is it the date when the entry is made? 3) Is your Raw data really a group of data from several days of entries? If so can you split the raw data by dates? 4) Does it make a diference if the raw data goes into the first or 2nd column for each model? Is it acceptable for the 1st raw data to go into the left column and the 2nd in the right column? If you are filling in the table I'm have different questions: 1) Can you give an exanmple of the empty table before the raw data is entered. 2) How do you know which entry is for each date? 3) Does it make a diference if the raw data goes into the first or 2nd column for each model? "Cam" wrote: Hello, I have two different data in two worksheets, worksheet 1 is the main data that I would like to create a macro for. Sheet2 contains raw data that I pull daily or weekly. What I would like to achieve for the VBA macro is to dump the raw data in sheet2 into sheet1, but put into the right cell. The macro needs to be able to: 1) Fill in the s/o number to column D:G based on the latest operation number for each model & part# type column. 2) Each model in the row will always have 2 (1300) & 2 (1500) 3) The row with "vendor" fill in do not require s/o fill in. 4) Column H:K, I can do a Vlookup to fill in the operation number from the raw data. Example: for row with No. 1002 & 1003 that have the same model, 218029 (1300) @ operation 100 is a later operation than 215789 (1300) @ operation 90, so the first s/o to fill in row with 1002 is 218031 and so on. Here is the data sample: Sheet1 No. ShipDate Model 1300 1300 1500 1500 Operation Status 1001 17-Aug 100 vendor vendor vendor vendor #N/A #N/A #N/A #N/A 1002 18-Aug 200 218029 218031 215226 214526 100 95 320 197 1003 19-Aug 200 215789 218033 216999 216998 90 90 320 162 1004 19-Aug 100 215048 215780 215778 217693 90 90 410 180 1005 20-Aug 100 vendor vendor vendor vendor #N/A #N/A #N/A #N/A 1006 21-Aug 200 218035 216994 217000 218041 90 80 162 165 1007 25-Aug 100 216966 218975 218042 217001 80 70 300 150 1008 25-Aug 100 215765 215769 215779 215785 50 60 250 100 Sheet2 Raw Data S/O Operation Ref Model 218029 100 1300 200 215789 90 1300 200 215048 90 1300 100 218035 90 1300 200 216966 80 1300 100 215765 50 1300 100 218974 20 1300 200 218031 95 1300 200 218033 90 1300 200 215780 90 1300 100 216994 80 1300 200 218975 180 1300 100 215769 320 1300 100 219680 150 1300 200 215226 320 1500 200 216999 320 1500 200 215778 410 1500 100 217000 162 1500 200 218042 150 1500 100 215779 250 1500 100 215790 110 1500 200 214526 197 1500 200 216998 162 1500 200 217693 180 1500 100 218041 165 1500 200 217001 150 1500 100 215785 10 1500 100 218039 110 1500 200 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help w/ look & fill in data
Joel,
Thanks for your patience and help. The code is almost what I wanted. When I run your data, it seemed like it look for data in sheet2 and add to existence data in sheet1. I would like to update all the data in sheet1 by using all data in sheet2. Example: Before running the macro, my data will have No., ShipDate & Model filled out up to 50 rows. Then column D:K could be blank. Once the macro is ran, it will fill in all the data starting from row 2 (No. 1001). Also, not fill in row 5 (No. 1004). The S/O on sheet 2 will not shown up once all the operations are completed and closed out. So if No. 1001 has all the S/O (not in sheet2 anymore) that are completed I will hide 1001 row. The same process repeat again. No. ShipDate Model 1300 1300 1500 1500 Operation Status 1001 17-Aug 100 1002 18-Aug 200 1003 19-Aug 200 1004 19-Aug 100 vendor vendor vendor vendor N/A N/A N/A N/A 1006 21-Aug 200 1007 25-Aug 100 1008 25-Aug 100 "Joel" wrote: You are going to have to fix the problems with your worksheet. Apparently you have spaces the cells that contain numbers. Make sure all the numbers are right justified in the cell (left justified indicates they are text). You can easily get rid of the spaces by using the Spreadsheet Replace (under edit menu). Highligh the cells containing number. the replace a single space with nothing. Just enter a space in the from box and leave the replace box empty. The code becomes very complex to handle numbers and text mixed. fix both sheet 1 and 2. Below is the code I think you want. I put in the Operation number and well as the SO number. Sub checkso() Const High2 = 0 Const High1 = 1 Const OP = 0 Const SO = 1 Dim R1300M100(2, 2) Dim R1300M200(2, 2) Dim R1500M100(2, 2) Dim R1500M200(2, 2) R1300M100(High1, OP) = 0 R1300M100(High2, OP) = 0 R1300M100(High1, SO) = 0 R1300M100(High2, SO) = 0 R1300M200(High1, OP) = 0 R1300M200(High2, OP) = 0 R1300M200(High1, SO) = 0 R1300M200(High2, SO) = 0 R1500M100(High1, OP) = 0 R1500M100(High2, OP) = 0 R1500M100(High1, SO) = 0 R1500M100(High2, SO) = 0 R1500M200(High1, OP) = 0 R1500M200(High2, OP) = 0 R1500M200(High1, SO) = 0 R1500M200(High2, SO) = 0 LastRowSh1 = Sheets("Sheet1"). _ Cells(Rows.Count, "A").End(xlUp).Row LastRowSh2 = Sheets("Sheet2"). _ Cells(Rows.Count, "A").End(xlUp).Row Sh1NewRow = LastRowSh1 + 1 With Sheets("Sheet2") For Sh2RowCount = 2 To LastRowSh2 Operation = .Cells(Sh2RowCount, "B") ShippingOrder = .Cells(Sh2RowCount, "A") If .Cells(Sh2RowCount, "C").Value = 1300 Then If .Cells(Sh2RowCount, "D").Value = 100 Then If Operation R1300M100(High1, OP) Then R1300M100(High2, OP) = _ R1300M100(High1, OP) R1300M100(High1, OP) = _ Operation R1300M100(High2, SO) = _ R1300M100(High1, SO) R1300M100(High1, SO) = _ ShippingOrder Else If Operation R1300M100(High2, OP) Then R1300M100(High2, OP) = _ Operation R1300M100(High2, SO) = _ ShippingOrder End If End If End If If .Cells(Sh2RowCount, "D").Value = 200 Then If Operation R1300M200(High1, OP) Then R1300M200(High2, OP) = _ R1300M200(High1, OP) R1300M200(High1, OP) = _ Operation R1300M200(High2, SO) = _ R1300M200(High1, SO) R1300M200(High1, SO) = _ ShippingOrder Else If Operation R1300M200(High2, OP) Then R1300M200(High2, OP) = _ Operation R1300M200(High2, SO) = _ ShippingOrder End If End If End If End If If .Cells(Sh2RowCount, "C").Value = 1500 Then If .Cells(Sh2RowCount, "D").Value = 100 Then If Operation R1500M100(High1, OP) Then R1500M100(High2, OP) = _ R1500M100(High1, OP) R1500M100(High1, OP) = _ Operation R1500M100(High2, SO) = _ R1500M100(High1, SO) R1500M100(High1, SO) = _ ShippingOrder Else If Operation R1500M100(High2, OP) Then R1500M100(High2, OP) = _ Operation R1500M100(High2, SO) = _ ShippingOrder End If End If End If If .Cells(Sh2RowCount, "D").Value = 200 Then If Operation R1500M200(High1, OP) Then R1500M200(High2, OP) = _ R1500M200(High1, OP) R1500M200(High1, OP) = _ Operation R1500M200(High2, SO) = _ R1500M200(High1, SO) R1500M200(High1, SO) = _ ShippingOrder Else If Operation R1500M200(High2, OP) Then R1500M200(High2, OP) = _ Operation R1500M200(High2, SO) = _ ShippingOrder End If End If End If End If Next Sh2RowCount End With With Sheets("Sheet1") .Cells(Sh1NewRow, "C").Value = 100 .Cells(Sh1NewRow, "D").Value = _ R1300M100(High1, SO) .Cells(Sh1NewRow, "E").Value = _ R1300M100(High2, SO) .Cells(Sh1NewRow, "F").Value = _ R1500M100(High1, SO) .Cells(Sh1NewRow, "G").Value = _ R1500M100(High2, SO) .Cells(Sh1NewRow, "H").Value = _ R1300M100(High1, OP) .Cells(Sh1NewRow, "I").Value = _ R1300M100(High2, OP) .Cells(Sh1NewRow, "J").Value = _ R1500M100(High1, OP) .Cells(Sh1NewRow, "K").Value = _ R1500M100(High2, OP) Sh1NewRow = Sh1NewRow + 1 .Cells(Sh1NewRow, "C").Value = 200 .Cells(Sh1NewRow, "D").Value = _ R1300M200(High1, SO) .Cells(Sh1NewRow, "E").Value = _ R1300M200(High2, SO) .Cells(Sh1NewRow, "F").Value = _ R1500M200(High1, SO) .Cells(Sh1NewRow, "G").Value = _ R1500M200(High2, SO) .Cells(Sh1NewRow, "H").Value = _ R1300M200(High1, OP) .Cells(Sh1NewRow, "I").Value = _ R1300M200(High2, OP) .Cells(Sh1NewRow, "J").Value = _ R1500M200(High1, OP) .Cells(Sh1NewRow, "K").Value = _ R1500M200(High2, OP) End With End Sub "Cam" wrote: Joel, Thank for your response. I'm still having problems. All the rows get the same data. I think you want a new row, but didn't really say that. I don't have a date to start a new row nor the 1001-1008 number. I do want to start a new row, but the rows can be the same data depending if there is any changes to the operation. I'm not sure if there are two operations am I suppose to look for the two highest operation numbers in sheet 2 and then add a new row? Yes, for 1360 Ref column, pick the two highest operations and fill in the data in sheet1, then fill in the next row with the next highest operation, same for 1560. What I can see it looks like its the columns H:K is what is changing every day, not D:G. My code is changing D:G Both D:G & H:K could be different every day where D:G changes once part# is replaced or pulled out. Then reshuffle the fill in part#. H:K changes depending on moving up the operation number. Also, I ran the code and it kept with a mismatch type error. The model & operation need to be changed to general or text instead of number. Thank again. "Joel" wrote: Belwo is the code I have right now. Make sure there is no spaces in the Model Number or Operation Number in sheet 1 or 2. I'm treating them as number. they should be right justified in the cell with no double quotes in front of the numbers. I can change the code if you are using text. I'm still having problems. All the rows get the same data. I think you want a new row, but didn't really say that. I don't have a date to start a new row nor the 1001-1008 number. I'm not sure if there are two operations am I suppose to look for the two highest operation numbers in sheet 2 and then add a new row? What I can see it looks like its the columns H:K is what is changing every day, not D:G. My code is changing D:G Sub checkso() LastRowSh1 = Sheets("Sheet1"). _ Cells(Rows.Count, "A").End(xlUp).Row LastRowSh2 = Sheets("Sheet2"). _ Cells(Rows.Count, "A").End(xlUp).Row Sheets("Sheet1").Activate With Sheets("Sheet2") For Sh1RowCount = 2 To LastRowSh1 If Cells(Sh1RowCount, "D").Value < _ "vendor" Then Model = Cells(Sh1RowCount, "C").Value For Refcol = 4 To 6 Step 2 Ref = Cells(1, Refcol).Value ItemOffset = 0 Found = False For Sh2RowCount = _ 2 To LastRowSh2 If (.Cells(Sh2RowCount, "C").Value = _ Ref) And _ (.Cells(Sh2RowCount, "D").Value = _ Model) Then If ItemOffset = 0 Then Cells(Sh1RowCount, Refcol) = _ .Cells(Sh2RowCount, "A").Value ItemOffset = 1 Else Cells(Sh1RowCount, Refcol + 1) = _ .Cells(Sh2RowCount, "A").Value Found = True Exit For End If End If Next Sh2RowCount If Found = False Then If ItemOffset = 0 Then Cells(Sh1RowCount, Refcol) = "" Cells(Sh1RowCount, Refcol + 1) = "" Else Cells(Sh1RowCount, Refcol + 1) = "" End If End If Next Refcol End If Next Sh1RowCount End With End Sub "Cam" wrote: Hello Joel, I will try to explain as clear as possible. 1) Where does the date come from? The date came from a raw data from sheet2 so every S/O has a date field. 2) Is it the date when the entry is made? It is not the date where the entry is made. 3) Is your Raw data really a group of data from several days of entries? If |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help w/ look & fill in data
Cam: It looks like you want my 1st solution, not the second solution! The
problem is there are multiple rows on Sheet 1 with the same model number. I don't know which items one sheet 2 go on which rows on sheet 1. I don't know how important column A (No) and B (Date) are in sheet1, but ithere is no way it can be correlated to the data in sheet2. I think what you really want is to take the data in sheet 2 and place it in sheet 1 with highest operation number going first. Think about what I wrote above and let me know how to proceeed. "Cam" wrote: Joel, Thanks for your patience and help. The code is almost what I wanted. When I run your data, it seemed like it look for data in sheet2 and add to existence data in sheet1. I would like to update all the data in sheet1 by using all data in sheet2. Example: Before running the macro, my data will have No., ShipDate & Model filled out up to 50 rows. Then column D:K could be blank. Once the macro is ran, it will fill in all the data starting from row 2 (No. 1001). Also, not fill in row 5 (No. 1004). The S/O on sheet 2 will not shown up once all the operations are completed and closed out. So if No. 1001 has all the S/O (not in sheet2 anymore) that are completed I will hide 1001 row. The same process repeat again. No. ShipDate Model 1300 1300 1500 1500 Operation Status 1001 17-Aug 100 1002 18-Aug 200 1003 19-Aug 200 1004 19-Aug 100 vendor vendor vendor vendor N/A N/A N/A N/A 1006 21-Aug 200 1007 25-Aug 100 1008 25-Aug 100 "Joel" wrote: You are going to have to fix the problems with your worksheet. Apparently you have spaces the cells that contain numbers. Make sure all the numbers are right justified in the cell (left justified indicates they are text). You can easily get rid of the spaces by using the Spreadsheet Replace (under edit menu). Highligh the cells containing number. the replace a single space with nothing. Just enter a space in the from box and leave the replace box empty. The code becomes very complex to handle numbers and text mixed. fix both sheet 1 and 2. Below is the code I think you want. I put in the Operation number and well as the SO number. Sub checkso() Const High2 = 0 Const High1 = 1 Const OP = 0 Const SO = 1 Dim R1300M100(2, 2) Dim R1300M200(2, 2) Dim R1500M100(2, 2) Dim R1500M200(2, 2) R1300M100(High1, OP) = 0 R1300M100(High2, OP) = 0 R1300M100(High1, SO) = 0 R1300M100(High2, SO) = 0 R1300M200(High1, OP) = 0 R1300M200(High2, OP) = 0 R1300M200(High1, SO) = 0 R1300M200(High2, SO) = 0 R1500M100(High1, OP) = 0 R1500M100(High2, OP) = 0 R1500M100(High1, SO) = 0 R1500M100(High2, SO) = 0 R1500M200(High1, OP) = 0 R1500M200(High2, OP) = 0 R1500M200(High1, SO) = 0 R1500M200(High2, SO) = 0 LastRowSh1 = Sheets("Sheet1"). _ Cells(Rows.Count, "A").End(xlUp).Row LastRowSh2 = Sheets("Sheet2"). _ Cells(Rows.Count, "A").End(xlUp).Row Sh1NewRow = LastRowSh1 + 1 With Sheets("Sheet2") For Sh2RowCount = 2 To LastRowSh2 Operation = .Cells(Sh2RowCount, "B") ShippingOrder = .Cells(Sh2RowCount, "A") If .Cells(Sh2RowCount, "C").Value = 1300 Then If .Cells(Sh2RowCount, "D").Value = 100 Then If Operation R1300M100(High1, OP) Then R1300M100(High2, OP) = _ R1300M100(High1, OP) R1300M100(High1, OP) = _ Operation R1300M100(High2, SO) = _ R1300M100(High1, SO) R1300M100(High1, SO) = _ ShippingOrder Else If Operation R1300M100(High2, OP) Then R1300M100(High2, OP) = _ Operation R1300M100(High2, SO) = _ ShippingOrder End If End If End If If .Cells(Sh2RowCount, "D").Value = 200 Then If Operation R1300M200(High1, OP) Then R1300M200(High2, OP) = _ R1300M200(High1, OP) R1300M200(High1, OP) = _ Operation R1300M200(High2, SO) = _ R1300M200(High1, SO) R1300M200(High1, SO) = _ ShippingOrder Else If Operation R1300M200(High2, OP) Then R1300M200(High2, OP) = _ Operation R1300M200(High2, SO) = _ ShippingOrder End If End If End If End If If .Cells(Sh2RowCount, "C").Value = 1500 Then If .Cells(Sh2RowCount, "D").Value = 100 Then If Operation R1500M100(High1, OP) Then R1500M100(High2, OP) = _ R1500M100(High1, OP) R1500M100(High1, OP) = _ Operation R1500M100(High2, SO) = _ R1500M100(High1, SO) R1500M100(High1, SO) = _ ShippingOrder Else If Operation R1500M100(High2, OP) Then R1500M100(High2, OP) = _ Operation R1500M100(High2, SO) = _ ShippingOrder End If End If End If If .Cells(Sh2RowCount, "D").Value = 200 Then If Operation R1500M200(High1, OP) Then R1500M200(High2, OP) = _ R1500M200(High1, OP) R1500M200(High1, OP) = _ Operation R1500M200(High2, SO) = _ R1500M200(High1, SO) R1500M200(High1, SO) = _ ShippingOrder Else If Operation R1500M200(High2, OP) Then R1500M200(High2, OP) = _ Operation R1500M200(High2, SO) = _ ShippingOrder End If End If End If End If Next Sh2RowCount End With With Sheets("Sheet1") .Cells(Sh1NewRow, "C").Value = 100 .Cells(Sh1NewRow, "D").Value = _ R1300M100(High1, SO) .Cells(Sh1NewRow, "E").Value = _ R1300M100(High2, SO) .Cells(Sh1NewRow, "F").Value = _ R1500M100(High1, SO) .Cells(Sh1NewRow, "G").Value = _ R1500M100(High2, SO) .Cells(Sh1NewRow, "H").Value = _ R1300M100(High1, OP) .Cells(Sh1NewRow, "I").Value = _ R1300M100(High2, OP) .Cells(Sh1NewRow, "J").Value = _ R1500M100(High1, OP) .Cells(Sh1NewRow, "K").Value = _ R1500M100(High2, OP) Sh1NewRow = Sh1NewRow + 1 .Cells(Sh1NewRow, "C").Value = 200 .Cells(Sh1NewRow, "D").Value = _ R1300M200(High1, SO) .Cells(Sh1NewRow, "E").Value = _ R1300M200(High2, SO) .Cells(Sh1NewRow, "F").Value = _ R1500M200(High1, SO) .Cells(Sh1NewRow, "G").Value = _ R1500M200(High2, SO) .Cells(Sh1NewRow, "H").Value = _ R1300M200(High1, OP) .Cells(Sh1NewRow, "I").Value = _ R1300M200(High2, OP) .Cells(Sh1NewRow, "J").Value = _ R1500M200(High1, OP) .Cells(Sh1NewRow, "K").Value = _ R1500M200(High2, OP) End With End Sub "Cam" wrote: Joel, Thank for your response. I'm still having problems. All the rows get the same data. I think you want a new row, but didn't really say that. I don't have a date to start a new row nor the 1001-1008 number. I do want to start a new row, but the rows can be the same data depending if there is any changes to the operation. I'm not sure if there are two operations am I suppose to look for the two highest operation numbers in sheet 2 and then add a new row? Yes, for 1360 Ref column, pick the two highest operations and fill in the data in sheet1, then fill in the next row with the next highest operation, same for 1560. What I can see it looks like its the columns H:K is what is changing every day, not D:G. My code is changing D:G Both D:G & H:K could be different every day where D:G changes once part# is replaced or pulled out. Then reshuffle the fill in part#. H:K changes depending on moving up the operation number. Also, I ran the code and it kept with a mismatch type error. The model & operation need to be changed to general or text instead of number. Thank again. "Joel" wrote: Belwo is the code I have right now. Make sure there is no spaces in the Model Number or Operation Number in sheet 1 or 2. I'm treating them as number. they should be right justified in the cell with no double quotes in front of the numbers. I can change the code if you are using text. I'm still having problems. All the rows get the same data. I think you want a new row, but didn't really say that. I don't have a date to start a new row nor the 1001-1008 number. I'm not sure if there are two operations am I suppose to look for the two highest operation numbers in sheet 2 and then add a new row? What I can see it looks like its the columns H:K is what is changing every day, not D:G. My code is changing D:G Sub checkso() LastRowSh1 = Sheets("Sheet1"). _ Cells(Rows.Count, "A").End(xlUp).Row LastRowSh2 = Sheets("Sheet2"). _ Cells(Rows.Count, "A").End(xlUp).Row Sheets("Sheet1").Activate With Sheets("Sheet2") For Sh1RowCount = 2 To LastRowSh1 If Cells(Sh1RowCount, "D").Value < _ "vendor" Then Model = Cells(Sh1RowCount, "C").Value For Refcol = 4 To 6 Step 2 Ref = Cells(1, Refcol).Value ItemOffset = 0 Found = False For Sh2RowCount = _ 2 To LastRowSh2 If (.Cells(Sh2RowCount, "C").Value = _ Ref) And _ (.Cells(Sh2RowCount, "D").Value = _ Model) Then If ItemOffset = 0 Then Cells(Sh1RowCount, Refcol) = _ .Cells(Sh2RowCount, "A").Value ItemOffset = 1 Else Cells(Sh1RowCount, Refcol + 1) = _ .Cells(Sh2RowCount, "A").Value Found = True Exit For End If |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help w/ look & fill in data
Joel,
Thank you for leading me to some thought. I think you are right that all I wanted was to take the raw data in sheet2 pulled from our system then place S/O in sheet2 based on the model and highest operation number going first. So, what I need to include in the code is the refresh (delete) all existing data in sheet1 first. Also, what I need to do manually is also, hide row with the No. (etc.. 1001) that are already completed so it will always start filling in from the first row and down. And I am not sure if the code also need to say not to fill in the data with the rows hidden. "Joel" wrote: Cam: It looks like you want my 1st solution, not the second solution! The problem is there are multiple rows on Sheet 1 with the same model number. I don't know which items one sheet 2 go on which rows on sheet 1. I don't know how important column A (No) and B (Date) are in sheet1, but ithere is no way it can be correlated to the data in sheet2. I think what you really want is to take the data in sheet 2 and place it in sheet 1 with highest operation number going first. Think about what I wrote above and let me know how to proceeed. "Cam" wrote: Joel, Thanks for your patience and help. The code is almost what I wanted. When I run your data, it seemed like it look for data in sheet2 and add to existence data in sheet1. I would like to update all the data in sheet1 by using all data in sheet2. Example: Before running the macro, my data will have No., ShipDate & Model filled out up to 50 rows. Then column D:K could be blank. Once the macro is ran, it will fill in all the data starting from row 2 (No. 1001). Also, not fill in row 5 (No. 1004). The S/O on sheet 2 will not shown up once all the operations are completed and closed out. So if No. 1001 has all the S/O (not in sheet2 anymore) that are completed I will hide 1001 row. The same process repeat again. No. ShipDate Model 1300 1300 1500 1500 Operation Status 1001 17-Aug 100 1002 18-Aug 200 1003 19-Aug 200 1004 19-Aug 100 vendor vendor vendor vendor N/A N/A N/A N/A 1006 21-Aug 200 1007 25-Aug 100 1008 25-Aug 100 "Joel" wrote: You are going to have to fix the problems with your worksheet. Apparently you have spaces the cells that contain numbers. Make sure all the numbers are right justified in the cell (left justified indicates they are text). You can easily get rid of the spaces by using the Spreadsheet Replace (under edit menu). Highligh the cells containing number. the replace a single space with nothing. Just enter a space in the from box and leave the replace box empty. The code becomes very complex to handle numbers and text mixed. fix both sheet 1 and 2. Below is the code I think you want. I put in the Operation number and well as the SO number. Sub checkso() Const High2 = 0 Const High1 = 1 Const OP = 0 Const SO = 1 Dim R1300M100(2, 2) Dim R1300M200(2, 2) Dim R1500M100(2, 2) Dim R1500M200(2, 2) R1300M100(High1, OP) = 0 R1300M100(High2, OP) = 0 R1300M100(High1, SO) = 0 R1300M100(High2, SO) = 0 R1300M200(High1, OP) = 0 R1300M200(High2, OP) = 0 R1300M200(High1, SO) = 0 R1300M200(High2, SO) = 0 R1500M100(High1, OP) = 0 R1500M100(High2, OP) = 0 R1500M100(High1, SO) = 0 R1500M100(High2, SO) = 0 R1500M200(High1, OP) = 0 R1500M200(High2, OP) = 0 R1500M200(High1, SO) = 0 R1500M200(High2, SO) = 0 LastRowSh1 = Sheets("Sheet1"). _ Cells(Rows.Count, "A").End(xlUp).Row LastRowSh2 = Sheets("Sheet2"). _ Cells(Rows.Count, "A").End(xlUp).Row Sh1NewRow = LastRowSh1 + 1 With Sheets("Sheet2") For Sh2RowCount = 2 To LastRowSh2 Operation = .Cells(Sh2RowCount, "B") ShippingOrder = .Cells(Sh2RowCount, "A") If .Cells(Sh2RowCount, "C").Value = 1300 Then If .Cells(Sh2RowCount, "D").Value = 100 Then If Operation R1300M100(High1, OP) Then R1300M100(High2, OP) = _ R1300M100(High1, OP) R1300M100(High1, OP) = _ Operation R1300M100(High2, SO) = _ R1300M100(High1, SO) R1300M100(High1, SO) = _ ShippingOrder Else If Operation R1300M100(High2, OP) Then R1300M100(High2, OP) = _ Operation R1300M100(High2, SO) = _ ShippingOrder End If End If End If If .Cells(Sh2RowCount, "D").Value = 200 Then If Operation R1300M200(High1, OP) Then R1300M200(High2, OP) = _ R1300M200(High1, OP) R1300M200(High1, OP) = _ Operation R1300M200(High2, SO) = _ R1300M200(High1, SO) R1300M200(High1, SO) = _ ShippingOrder Else If Operation R1300M200(High2, OP) Then R1300M200(High2, OP) = _ Operation R1300M200(High2, SO) = _ ShippingOrder End If End If End If End If If .Cells(Sh2RowCount, "C").Value = 1500 Then If .Cells(Sh2RowCount, "D").Value = 100 Then If Operation R1500M100(High1, OP) Then R1500M100(High2, OP) = _ R1500M100(High1, OP) R1500M100(High1, OP) = _ Operation R1500M100(High2, SO) = _ R1500M100(High1, SO) R1500M100(High1, SO) = _ ShippingOrder Else If Operation R1500M100(High2, OP) Then R1500M100(High2, OP) = _ Operation R1500M100(High2, SO) = _ ShippingOrder End If End If End If If .Cells(Sh2RowCount, "D").Value = 200 Then If Operation R1500M200(High1, OP) Then R1500M200(High2, OP) = _ R1500M200(High1, OP) R1500M200(High1, OP) = _ Operation R1500M200(High2, SO) = _ R1500M200(High1, SO) R1500M200(High1, SO) = _ ShippingOrder Else If Operation R1500M200(High2, OP) Then R1500M200(High2, OP) = _ Operation R1500M200(High2, SO) = _ ShippingOrder End If End If End If End If Next Sh2RowCount End With With Sheets("Sheet1") .Cells(Sh1NewRow, "C").Value = 100 .Cells(Sh1NewRow, "D").Value = _ R1300M100(High1, SO) .Cells(Sh1NewRow, "E").Value = _ R1300M100(High2, SO) .Cells(Sh1NewRow, "F").Value = _ R1500M100(High1, SO) .Cells(Sh1NewRow, "G").Value = _ R1500M100(High2, SO) .Cells(Sh1NewRow, "H").Value = _ R1300M100(High1, OP) .Cells(Sh1NewRow, "I").Value = _ R1300M100(High2, OP) .Cells(Sh1NewRow, "J").Value = _ R1500M100(High1, OP) .Cells(Sh1NewRow, "K").Value = _ R1500M100(High2, OP) Sh1NewRow = Sh1NewRow + 1 .Cells(Sh1NewRow, "C").Value = 200 .Cells(Sh1NewRow, "D").Value = _ R1300M200(High1, SO) .Cells(Sh1NewRow, "E").Value = _ R1300M200(High2, SO) .Cells(Sh1NewRow, "F").Value = _ R1500M200(High1, SO) .Cells(Sh1NewRow, "G").Value = _ R1500M200(High2, SO) .Cells(Sh1NewRow, "H").Value = _ R1300M200(High1, OP) .Cells(Sh1NewRow, "I").Value = _ R1300M200(High2, OP) .Cells(Sh1NewRow, "J").Value = _ R1500M200(High1, OP) .Cells(Sh1NewRow, "K").Value = _ R1500M200(High2, OP) End With End Sub "Cam" wrote: Joel, Thank for your response. I'm still having problems. All the rows get the same data. I think you want a new row, but didn't really say that. I don't have a date to start a new row nor the 1001-1008 number. I do want to start a new row, but the rows can be the same data depending if there is any changes to the operation. I'm not sure if there are two operations am I suppose to look for the two highest operation numbers in sheet 2 and then add a new row? Yes, for 1360 Ref column, pick the two highest operations and fill in the data in sheet1, then fill in the next row with the next highest operation, same for 1560. What I can see it looks like its the columns H:K is what is changing every day, not D:G. My code is changing D:G Both D:G & H:K could be different every day where D:G changes once part# is replaced or pulled out. Then reshuffle the fill in part#. H:K changes depending on moving up the operation number. Also, I ran the code and it kept with a mismatch type error. The model & operation need to be changed to general or text instead of number. Thank again. "Joel" wrote: Belwo is the code I have right now. Make sure there is no spaces in the Model Number or Operation Number in sheet 1 or 2. I'm treating them as number. they should be right justified in the cell with no double quotes in front of the numbers. I can change the code if you are using text. I'm still having problems. All the rows get the same data. I think you want a new row, but didn't really say that. I don't have a date to start a new row nor the 1001-1008 number. I'm not sure if there are two operations am I suppose to look for the two highest operation numbers in sheet 2 and then add a new row? What I can see it looks like its the columns H:K is what is changing every day, not D:G. My code is changing D:G Sub checkso() LastRowSh1 = Sheets("Sheet1"). _ Cells(Rows.Count, "A").End(xlUp).Row LastRowSh2 = Sheets("Sheet2"). _ Cells(Rows.Count, "A").End(xlUp).Row Sheets("Sheet1").Activate With Sheets("Sheet2") For Sh1RowCount = 2 To LastRowSh1 If Cells(Sh1RowCount, "D").Value < _ "vendor" Then Model = Cells(Sh1RowCount, "C").Value For Refcol = 4 To 6 Step 2 Ref = Cells(1, Refcol).Value ItemOffset = 0 Found = False For Sh2RowCount = _ 2 To LastRowSh2 If (.Cells(Sh2RowCount, "C").Value = _ Ref) And _ (.Cells(Sh2RowCount, "D").Value = _ Model) Then |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help w/ look & fill in data
I ws expecting that you would agree with me. Have been working the code
before you asked for it. The code will clear sheet 1 and skip and rows with vendor in column D. I also cleared column A and B because I don't know what should be in these columns. the code has 3 subroutines to make the code common for all models and Ref values. Run checkso() Const OP = 0 Const SO = 1 Const Ref1300 = 0 Const Ref1500 = 2 Sub checkso() Dim R1300M100(1000, 2) Dim R1300M200(1000, 2) Dim R1500M100(1000, 2) Dim R1500M200(1000, 2) With Sheets("Sheet1") LastRowSh1 = _ .Cells(Rows.Count, "C").End(xlUp).Row Set ColDRange = .Range(.Cells(2, "D"), _ .Cells(LastRowSh1, "D")) For Each cell In ColDRange If cell < "vendor" Then Rows(cell.Row).ClearContents End If Next cell End With LastRowSh2 = Sheets("Sheet2"). _ Cells(Rows.Count, "A").End(xlUp).Row R1300M100Count = 0 R1300M200Count = 0 R1500M100Count = 0 R1500M200Count = 0 With Sheets("Sheet2") For Sh2RowCount = 2 To LastRowSh2 OPeration = .Cells(Sh2RowCount, "B") ShippingOrder = .Cells(Sh2RowCount, "A") Model = .Cells(Sh2RowCount, "D") If .Cells(Sh2RowCount, "C").Value = 1300 Then If .Cells(Sh2RowCount, "D").Value = 100 Then R1300M100Count = R1300M100Count + 1 R1300M100(R1300M100Count, OP) = _ OPeration R1300M100(R1300M100Count, SO) = _ ShippingOrder End If If .Cells(Sh2RowCount, "D").Value = 200 Then R1300M200Count = R1300M200Count + 1 R1300M200(R1300M200Count, OP) = _ OPeration R1300M200(R1300M200Count, SO) = _ ShippingOrder End If End If If .Cells(Sh2RowCount, "C").Value = 1500 Then If .Cells(Sh2RowCount, "D").Value = 100 Then R1500M100Count = R1500M100Count + 1 R1500M100(R1500M100Count, OP) = _ OPeration R1500M100(R1500M100Count, SO) = _ ShippingOrder End If If .Cells(Sh2RowCount, "D").Value = 200 Then R1500M200Count = R1500M200Count + 1 R1500M200(R1500M200Count, OP) = _ OPeration R1500M200(R1500M200Count, SO) = _ ShippingOrder End If End If Next Sh2RowCount End With Call SortData(R1300M100, R1300M100Count) Call SortData(R1300M200, R1300M200Count) Call SortData(R1500M100, R1500M100Count) Call SortData(R1500M200, R1500M200Count) Call InsertData(R1300M100, R1300M100Count, _ Ref1300, 100) Call InsertData(R1300M200, R1300M200Count, _ Ref1300, 200) Call InsertData(R1500M100, R1500M100Count, _ Ref1500, 100) Call InsertData(R1500M200, R1500M200Count, _ Ref1500, 200) End Sub Sub SortData(ByRef MyArray() As Variant, Count) For i = 0 To (Count - 2) For j = (i + 1) To (Count - 1) If MyArray(j, OP) MyArray(i, OP) Then Temp = MyArray(i, OP) MyArray(i, OP) = MyArray(j, OP) MyArray(j, OP) = Temp Temp = MyArray(i, SO) MyArray(i, SO) = MyArray(j, SO) MyArray(j, SO) = Temp End If Next j Next i End Sub Sub InsertData(ByRef MyArray() As Variant, _ Count, Ref, Model) With Sheets("Sheet1") RowCount = 2 MyOffset = 0 Do While (.Cells(RowCount, "D"). _ Offset(0, Ref) < "") And _ ((.Cells(RowCount, "C") < Model) Or _ (.Cells(RowCount, "D") = "vendor")) RowCount = RowCount + 1 Loop For LoopCount = 0 To (Count - 1) .Cells(RowCount, "D"). _ Offset(0, Ref + MyOffset) = _ MyArray(LoopCount, SO) .Cells(RowCount, "H"). _ Offset(0, Ref + MyOffset) = _ MyArray(LoopCount, OP) If MyOffset = 0 Then .Cells(RowCount, "C").Value = Model MyOffset = 1 Else RowCount = RowCount + 1 Do While (.Cells(RowCount, "D"). _ Offset(0, Ref) < "") And _ ((.Cells(RowCount, "C") < Model) Or _ (.Cells(RowCount, "D") = "vendor")) RowCount = RowCount + 1 Loop MyOffset = 0 End If Next LoopCount End With End Sub "Cam" wrote: Joel, Thank you for leading me to some thought. I think you are right that all I wanted was to take the raw data in sheet2 pulled from our system then place S/O in sheet2 based on the model and highest operation number going first. So, what I need to include in the code is the refresh (delete) all existing data in sheet1 first. Also, what I need to do manually is also, hide row with the No. (etc.. 1001) that are already completed so it will always start filling in from the first row and down. And I am not sure if the code also need to say not to fill in the data with the rows hidden. "Joel" wrote: Cam: It looks like you want my 1st solution, not the second solution! The problem is there are multiple rows on Sheet 1 with the same model number. I don't know which items one sheet 2 go on which rows on sheet 1. I don't know how important column A (No) and B (Date) are in sheet1, but ithere is no way it can be correlated to the data in sheet2. I think what you really want is to take the data in sheet 2 and place it in sheet 1 with highest operation number going first. Think about what I wrote above and let me know how to proceeed. "Cam" wrote: Joel, Thanks for your patience and help. The code is almost what I wanted. When I run your data, it seemed like it look for data in sheet2 and add to existence data in sheet1. I would like to update all the data in sheet1 by using all data in sheet2. Example: Before running the macro, my data will have No., ShipDate & Model filled out up to 50 rows. Then column D:K could be blank. Once the macro is ran, it will fill in all the data starting from row 2 (No. 1001). Also, not fill in row 5 (No. 1004). The S/O on sheet 2 will not shown up once all the operations are completed and closed out. So if No. 1001 has all the S/O (not in sheet2 anymore) that are completed I will hide 1001 row. The same process repeat again. No. ShipDate Model 1300 1300 1500 1500 Operation Status 1001 17-Aug 100 1002 18-Aug 200 1003 19-Aug 200 1004 19-Aug 100 vendor vendor vendor vendor N/A N/A N/A N/A 1006 21-Aug 200 1007 25-Aug 100 1008 25-Aug 100 "Joel" wrote: You are going to have to fix the problems with your worksheet. Apparently you have spaces the cells that contain numbers. Make sure all the numbers are right justified in the cell (left justified indicates they are text). You can easily get rid of the spaces by using the Spreadsheet Replace (under edit menu). Highligh the cells containing number. the replace a single space with nothing. Just enter a space in the from box and leave the replace box empty. The code becomes very complex to handle numbers and text mixed. fix both sheet 1 and 2. Below is the code I think you want. I put in the Operation number and well as the SO number. Sub checkso() Const High2 = 0 Const High1 = 1 Const OP = 0 Const SO = 1 Dim R1300M100(2, 2) Dim R1300M200(2, 2) Dim R1500M100(2, 2) Dim R1500M200(2, 2) R1300M100(High1, OP) = 0 R1300M100(High2, OP) = 0 R1300M100(High1, SO) = 0 R1300M100(High2, SO) = 0 R1300M200(High1, OP) = 0 R1300M200(High2, OP) = 0 R1300M200(High1, SO) = 0 R1300M200(High2, SO) = 0 R1500M100(High1, OP) = 0 R1500M100(High2, OP) = 0 R1500M100(High1, SO) = 0 R1500M100(High2, SO) = 0 R1500M200(High1, OP) = 0 R1500M200(High2, OP) = 0 R1500M200(High1, SO) = 0 R1500M200(High2, SO) = 0 LastRowSh1 = Sheets("Sheet1"). _ Cells(Rows.Count, "A").End(xlUp).Row LastRowSh2 = Sheets("Sheet2"). _ Cells(Rows.Count, "A").End(xlUp).Row Sh1NewRow = LastRowSh1 + 1 With Sheets("Sheet2") For Sh2RowCount = 2 To LastRowSh2 Operation = .Cells(Sh2RowCount, "B") ShippingOrder = .Cells(Sh2RowCount, "A") If .Cells(Sh2RowCount, "C").Value = 1300 Then If .Cells(Sh2RowCount, "D").Value = 100 Then If Operation R1300M100(High1, OP) Then R1300M100(High2, OP) = _ R1300M100(High1, OP) R1300M100(High1, OP) = _ Operation R1300M100(High2, SO) = _ R1300M100(High1, SO) R1300M100(High1, SO) = _ ShippingOrder Else If Operation R1300M100(High2, OP) Then R1300M100(High2, OP) = _ Operation R1300M100(High2, SO) = _ ShippingOrder End If End If End If If .Cells(Sh2RowCount, "D").Value = 200 Then If Operation R1300M200(High1, OP) Then R1300M200(High2, OP) = _ R1300M200(High1, OP) R1300M200(High1, OP) = _ Operation R1300M200(High2, SO) = _ R1300M200(High1, SO) R1300M200(High1, SO) = _ ShippingOrder Else If Operation R1300M200(High2, OP) Then R1300M200(High2, OP) = _ Operation R1300M200(High2, SO) = _ ShippingOrder End If End If End If End If If .Cells(Sh2RowCount, "C").Value = 1500 Then If .Cells(Sh2RowCount, "D").Value = 100 Then If Operation R1500M100(High1, OP) Then R1500M100(High2, OP) = _ R1500M100(High1, OP) R1500M100(High1, OP) = _ Operation R1500M100(High2, SO) = _ R1500M100(High1, SO) R1500M100(High1, SO) = _ ShippingOrder Else If Operation R1500M100(High2, OP) Then R1500M100(High2, OP) = _ Operation R1500M100(High2, SO) = _ ShippingOrder End If End If End If If .Cells(Sh2RowCount, "D").Value = 200 Then If Operation R1500M200(High1, OP) Then R1500M200(High2, OP) = _ R1500M200(High1, OP) R1500M200(High1, OP) = _ Operation R1500M200(High2, SO) = _ R1500M200(High1, SO) R1500M200(High1, SO) = _ ShippingOrder Else If Operation R1500M200(High2, OP) Then R1500M200(High2, OP) = _ Operation R1500M200(High2, SO) = _ ShippingOrder End If End If End If End If Next Sh2RowCount End With With Sheets("Sheet1") .Cells(Sh1NewRow, "C").Value = 100 .Cells(Sh1NewRow, "D").Value = _ R1300M100(High1, SO) .Cells(Sh1NewRow, "E").Value = _ R1300M100(High2, SO) .Cells(Sh1NewRow, "F").Value = _ R1500M100(High1, SO) .Cells(Sh1NewRow, "G").Value = _ R1500M100(High2, SO) .Cells(Sh1NewRow, "H").Value = _ R1300M100(High1, OP) .Cells(Sh1NewRow, "I").Value = _ R1300M100(High2, OP) .Cells(Sh1NewRow, "J").Value = _ R1500M100(High1, OP) .Cells(Sh1NewRow, "K").Value = _ R1500M100(High2, OP) Sh1NewRow = Sh1NewRow + 1 .Cells(Sh1NewRow, "C").Value = 200 .Cells(Sh1NewRow, "D").Value = _ R1300M200(High1, SO) .Cells(Sh1NewRow, "E").Value = _ R1300M200(High2, SO) .Cells(Sh1NewRow, "F").Value = _ R1500M200(High1, SO) .Cells(Sh1NewRow, "G").Value = _ R1500M200(High2, SO) .Cells(Sh1NewRow, "H").Value = _ R1300M200(High1, OP) .Cells(Sh1NewRow, "I").Value = _ R1300M200(High2, OP) .Cells(Sh1NewRow, "J").Value = _ R1500M200(High1, OP) .Cells(Sh1NewRow, "K").Value = _ R1500M200(High2, OP) End With End Sub "Cam" wrote: Joel, Thank for your response. I'm still having problems. All the rows get the same data. I think you want a new row, but didn't really say that. I don't have a date to start a new row nor the 1001-1008 number. I do want to start a new row, but the rows can be the same data depending if there is any changes to the operation. I'm not sure if there are two operations am I suppose to look for the two highest operation numbers in sheet 2 and then add a new row? Yes, for 1360 Ref column, pick the two highest operations and fill in the data in sheet1, then fill in the next row with the next highest operation, same for 1560. What I can see it looks like its the columns H:K is what is changing every day, not D:G. My code is changing D:G Both D:G & H:K could be different every day where D:G changes once part# is replaced or pulled out. Then reshuffle the fill in part#. H:K changes depending on moving up the operation number. Also, I ran the code and it kept with a mismatch type error. The model & operation need to be changed to general or text instead of number. Thank again. "Joel" wrote: Belwo is the code I have right now. Make sure there is no spaces in the Model Number or Operation Number in sheet 1 or 2. I'm treating them as number. they should be right justified in the cell with no double quotes in front of the numbers. I can change the code if you are using text. I'm still having problems. All the rows get the same data. I think you want a new row, but didn't really say that. I don't have a date to start a new row nor the 1001-1008 number. I'm not sure if there are two operations am I suppose to look for the two highest operation numbers in sheet 2 and then add a new row? What I can see it looks like its the columns H:K is what is changing every day, not D:G. My code is changing D:G Sub checkso() LastRowSh1 = Sheets("Sheet1"). _ Cells(Rows.Count, "A").End(xlUp).Row LastRowSh2 = Sheets("Sheet2"). _ Cells(Rows.Count, "A").End(xlUp).Row Sheets("Sheet1").Activate With Sheets("Sheet2") For Sh1RowCount = 2 To LastRowSh1 If Cells(Sh1RowCount, "D").Value < _ "vendor" Then |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help w/ look & fill in data
Thank you Joel.
Can I not clear column A & B on sheet1? They will be template and information is fixed (all filled out up to No. 1100, etc). "Joel" wrote: I ws expecting that you would agree with me. Have been working the code before you asked for it. The code will clear sheet 1 and skip and rows with vendor in column D. I also cleared column A and B because I don't know what should be in these columns. the code has 3 subroutines to make the code common for all models and Ref values. Run checkso() Const OP = 0 Const SO = 1 Const Ref1300 = 0 Const Ref1500 = 2 Sub checkso() Dim R1300M100(1000, 2) Dim R1300M200(1000, 2) Dim R1500M100(1000, 2) Dim R1500M200(1000, 2) With Sheets("Sheet1") LastRowSh1 = _ .Cells(Rows.Count, "C").End(xlUp).Row Set ColDRange = .Range(.Cells(2, "D"), _ .Cells(LastRowSh1, "D")) For Each cell In ColDRange If cell < "vendor" Then Rows(cell.Row).ClearContents End If Next cell End With LastRowSh2 = Sheets("Sheet2"). _ Cells(Rows.Count, "A").End(xlUp).Row R1300M100Count = 0 R1300M200Count = 0 R1500M100Count = 0 R1500M200Count = 0 With Sheets("Sheet2") For Sh2RowCount = 2 To LastRowSh2 OPeration = .Cells(Sh2RowCount, "B") ShippingOrder = .Cells(Sh2RowCount, "A") Model = .Cells(Sh2RowCount, "D") If .Cells(Sh2RowCount, "C").Value = 1300 Then If .Cells(Sh2RowCount, "D").Value = 100 Then R1300M100Count = R1300M100Count + 1 R1300M100(R1300M100Count, OP) = _ OPeration R1300M100(R1300M100Count, SO) = _ ShippingOrder End If If .Cells(Sh2RowCount, "D").Value = 200 Then R1300M200Count = R1300M200Count + 1 R1300M200(R1300M200Count, OP) = _ OPeration R1300M200(R1300M200Count, SO) = _ ShippingOrder End If End If If .Cells(Sh2RowCount, "C").Value = 1500 Then If .Cells(Sh2RowCount, "D").Value = 100 Then R1500M100Count = R1500M100Count + 1 R1500M100(R1500M100Count, OP) = _ OPeration R1500M100(R1500M100Count, SO) = _ ShippingOrder End If If .Cells(Sh2RowCount, "D").Value = 200 Then R1500M200Count = R1500M200Count + 1 R1500M200(R1500M200Count, OP) = _ OPeration R1500M200(R1500M200Count, SO) = _ ShippingOrder End If End If Next Sh2RowCount End With Call SortData(R1300M100, R1300M100Count) Call SortData(R1300M200, R1300M200Count) Call SortData(R1500M100, R1500M100Count) Call SortData(R1500M200, R1500M200Count) Call InsertData(R1300M100, R1300M100Count, _ Ref1300, 100) Call InsertData(R1300M200, R1300M200Count, _ Ref1300, 200) Call InsertData(R1500M100, R1500M100Count, _ Ref1500, 100) Call InsertData(R1500M200, R1500M200Count, _ Ref1500, 200) End Sub Sub SortData(ByRef MyArray() As Variant, Count) For i = 0 To (Count - 2) For j = (i + 1) To (Count - 1) If MyArray(j, OP) MyArray(i, OP) Then Temp = MyArray(i, OP) MyArray(i, OP) = MyArray(j, OP) MyArray(j, OP) = Temp Temp = MyArray(i, SO) MyArray(i, SO) = MyArray(j, SO) MyArray(j, SO) = Temp End If Next j Next i End Sub Sub InsertData(ByRef MyArray() As Variant, _ Count, Ref, Model) With Sheets("Sheet1") RowCount = 2 MyOffset = 0 Do While (.Cells(RowCount, "D"). _ Offset(0, Ref) < "") And _ ((.Cells(RowCount, "C") < Model) Or _ (.Cells(RowCount, "D") = "vendor")) RowCount = RowCount + 1 Loop For LoopCount = 0 To (Count - 1) .Cells(RowCount, "D"). _ Offset(0, Ref + MyOffset) = _ MyArray(LoopCount, SO) .Cells(RowCount, "H"). _ Offset(0, Ref + MyOffset) = _ MyArray(LoopCount, OP) If MyOffset = 0 Then .Cells(RowCount, "C").Value = Model MyOffset = 1 Else RowCount = RowCount + 1 Do While (.Cells(RowCount, "D"). _ Offset(0, Ref) < "") And _ ((.Cells(RowCount, "C") < Model) Or _ (.Cells(RowCount, "D") = "vendor")) RowCount = RowCount + 1 Loop MyOffset = 0 End If Next LoopCount End With End Sub "Cam" wrote: Joel, Thank you for leading me to some thought. I think you are right that all I wanted was to take the raw data in sheet2 pulled from our system then place S/O in sheet2 based on the model and highest operation number going first. So, what I need to include in the code is the refresh (delete) all existing data in sheet1 first. Also, what I need to do manually is also, hide row with the No. (etc.. 1001) that are already completed so it will always start filling in from the first row and down. And I am not sure if the code also need to say not to fill in the data with the rows hidden. "Joel" wrote: Cam: It looks like you want my 1st solution, not the second solution! The problem is there are multiple rows on Sheet 1 with the same model number. I don't know which items one sheet 2 go on which rows on sheet 1. I don't know how important column A (No) and B (Date) are in sheet1, but ithere is no way it can be correlated to the data in sheet2. I think what you really want is to take the data in sheet 2 and place it in sheet 1 with highest operation number going first. Think about what I wrote above and let me know how to proceeed. "Cam" wrote: Joel, Thanks for your patience and help. The code is almost what I wanted. When I run your data, it seemed like it look for data in sheet2 and add to existence data in sheet1. I would like to update all the data in sheet1 by using all data in sheet2. Example: Before running the macro, my data will have No., ShipDate & Model filled out up to 50 rows. Then column D:K could be blank. Once the macro is ran, it will fill in all the data starting from row 2 (No. 1001). Also, not fill in row 5 (No. 1004). The S/O on sheet 2 will not shown up once all the operations are completed and closed out. So if No. 1001 has all the S/O (not in sheet2 anymore) that are completed I will hide 1001 row. The same process repeat again. No. ShipDate Model 1300 1300 1500 1500 Operation Status 1001 17-Aug 100 1002 18-Aug 200 1003 19-Aug 200 1004 19-Aug 100 vendor vendor vendor vendor N/A N/A N/A N/A 1006 21-Aug 200 1007 25-Aug 100 1008 25-Aug 100 "Joel" wrote: You are going to have to fix the problems with your worksheet. Apparently you have spaces the cells that contain numbers. Make sure all the numbers are right justified in the cell (left justified indicates they are text). You can easily get rid of the spaces by using the Spreadsheet Replace (under edit menu). Highligh the cells containing number. the replace a single space with nothing. Just enter a space in the from box and leave the replace box empty. The code becomes very complex to handle numbers and text mixed. fix both sheet 1 and 2. Below is the code I think you want. I put in the Operation number and well as the SO number. Sub checkso() Const High2 = 0 Const High1 = 1 Const OP = 0 Const SO = 1 Dim R1300M100(2, 2) Dim R1300M200(2, 2) Dim R1500M100(2, 2) Dim R1500M200(2, 2) R1300M100(High1, OP) = 0 R1300M100(High2, OP) = 0 R1300M100(High1, SO) = 0 R1300M100(High2, SO) = 0 R1300M200(High1, OP) = 0 R1300M200(High2, OP) = 0 R1300M200(High1, SO) = 0 R1300M200(High2, SO) = 0 R1500M100(High1, OP) = 0 R1500M100(High2, OP) = 0 R1500M100(High1, SO) = 0 R1500M100(High2, SO) = 0 R1500M200(High1, OP) = 0 R1500M200(High2, OP) = 0 R1500M200(High1, SO) = 0 R1500M200(High2, SO) = 0 LastRowSh1 = Sheets("Sheet1"). _ Cells(Rows.Count, "A").End(xlUp).Row LastRowSh2 = Sheets("Sheet2"). _ Cells(Rows.Count, "A").End(xlUp).Row Sh1NewRow = LastRowSh1 + 1 With Sheets("Sheet2") For Sh2RowCount = 2 To LastRowSh2 Operation = .Cells(Sh2RowCount, "B") ShippingOrder = .Cells(Sh2RowCount, "A") If .Cells(Sh2RowCount, "C").Value = 1300 Then If .Cells(Sh2RowCount, "D").Value = 100 Then If Operation R1300M100(High1, OP) Then R1300M100(High2, OP) = _ R1300M100(High1, OP) R1300M100(High1, OP) = _ Operation R1300M100(High2, SO) = _ R1300M100(High1, SO) R1300M100(High1, SO) = _ ShippingOrder Else If Operation R1300M100(High2, OP) Then R1300M100(High2, OP) = _ Operation R1300M100(High2, SO) = _ ShippingOrder End If End If End If If .Cells(Sh2RowCount, "D").Value = 200 Then If Operation R1300M200(High1, OP) Then |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help w/ look & fill in data
Change one line of code to clear cell C to Z (make wnhat ever you need)
From: clear entire row Rows(cell.Row).ClearContents To: clear cells C to Z .Range("C" & cell.Row & ":Z" & _ cell.Row).ClearContents "Cam" wrote: Thank you Joel. Can I not clear column A & B on sheet1? They will be template and information is fixed (all filled out up to No. 1100, etc). "Joel" wrote: I ws expecting that you would agree with me. Have been working the code before you asked for it. The code will clear sheet 1 and skip and rows with vendor in column D. I also cleared column A and B because I don't know what should be in these columns. the code has 3 subroutines to make the code common for all models and Ref values. Run checkso() Const OP = 0 Const SO = 1 Const Ref1300 = 0 Const Ref1500 = 2 Sub checkso() Dim R1300M100(1000, 2) Dim R1300M200(1000, 2) Dim R1500M100(1000, 2) Dim R1500M200(1000, 2) With Sheets("Sheet1") LastRowSh1 = _ .Cells(Rows.Count, "C").End(xlUp).Row Set ColDRange = .Range(.Cells(2, "D"), _ .Cells(LastRowSh1, "D")) For Each cell In ColDRange If cell < "vendor" Then Rows(cell.Row).ClearContents End If Next cell End With LastRowSh2 = Sheets("Sheet2"). _ Cells(Rows.Count, "A").End(xlUp).Row R1300M100Count = 0 R1300M200Count = 0 R1500M100Count = 0 R1500M200Count = 0 With Sheets("Sheet2") For Sh2RowCount = 2 To LastRowSh2 OPeration = .Cells(Sh2RowCount, "B") ShippingOrder = .Cells(Sh2RowCount, "A") Model = .Cells(Sh2RowCount, "D") If .Cells(Sh2RowCount, "C").Value = 1300 Then If .Cells(Sh2RowCount, "D").Value = 100 Then R1300M100Count = R1300M100Count + 1 R1300M100(R1300M100Count, OP) = _ OPeration R1300M100(R1300M100Count, SO) = _ ShippingOrder End If If .Cells(Sh2RowCount, "D").Value = 200 Then R1300M200Count = R1300M200Count + 1 R1300M200(R1300M200Count, OP) = _ OPeration R1300M200(R1300M200Count, SO) = _ ShippingOrder End If End If If .Cells(Sh2RowCount, "C").Value = 1500 Then If .Cells(Sh2RowCount, "D").Value = 100 Then R1500M100Count = R1500M100Count + 1 R1500M100(R1500M100Count, OP) = _ OPeration R1500M100(R1500M100Count, SO) = _ ShippingOrder End If If .Cells(Sh2RowCount, "D").Value = 200 Then R1500M200Count = R1500M200Count + 1 R1500M200(R1500M200Count, OP) = _ OPeration R1500M200(R1500M200Count, SO) = _ ShippingOrder End If End If Next Sh2RowCount End With Call SortData(R1300M100, R1300M100Count) Call SortData(R1300M200, R1300M200Count) Call SortData(R1500M100, R1500M100Count) Call SortData(R1500M200, R1500M200Count) Call InsertData(R1300M100, R1300M100Count, _ Ref1300, 100) Call InsertData(R1300M200, R1300M200Count, _ Ref1300, 200) Call InsertData(R1500M100, R1500M100Count, _ Ref1500, 100) Call InsertData(R1500M200, R1500M200Count, _ Ref1500, 200) End Sub Sub SortData(ByRef MyArray() As Variant, Count) For i = 0 To (Count - 2) For j = (i + 1) To (Count - 1) If MyArray(j, OP) MyArray(i, OP) Then Temp = MyArray(i, OP) MyArray(i, OP) = MyArray(j, OP) MyArray(j, OP) = Temp Temp = MyArray(i, SO) MyArray(i, SO) = MyArray(j, SO) MyArray(j, SO) = Temp End If Next j Next i End Sub Sub InsertData(ByRef MyArray() As Variant, _ Count, Ref, Model) With Sheets("Sheet1") RowCount = 2 MyOffset = 0 Do While (.Cells(RowCount, "D"). _ Offset(0, Ref) < "") And _ ((.Cells(RowCount, "C") < Model) Or _ (.Cells(RowCount, "D") = "vendor")) RowCount = RowCount + 1 Loop For LoopCount = 0 To (Count - 1) .Cells(RowCount, "D"). _ Offset(0, Ref + MyOffset) = _ MyArray(LoopCount, SO) .Cells(RowCount, "H"). _ Offset(0, Ref + MyOffset) = _ MyArray(LoopCount, OP) If MyOffset = 0 Then .Cells(RowCount, "C").Value = Model MyOffset = 1 Else RowCount = RowCount + 1 Do While (.Cells(RowCount, "D"). _ Offset(0, Ref) < "") And _ ((.Cells(RowCount, "C") < Model) Or _ (.Cells(RowCount, "D") = "vendor")) RowCount = RowCount + 1 Loop MyOffset = 0 End If Next LoopCount End With End Sub "Cam" wrote: Joel, Thank you for leading me to some thought. I think you are right that all I wanted was to take the raw data in sheet2 pulled from our system then place S/O in sheet2 based on the model and highest operation number going first. So, what I need to include in the code is the refresh (delete) all existing data in sheet1 first. Also, what I need to do manually is also, hide row with the No. (etc.. 1001) that are already completed so it will always start filling in from the first row and down. And I am not sure if the code also need to say not to fill in the data with the rows hidden. "Joel" wrote: Cam: It looks like you want my 1st solution, not the second solution! The problem is there are multiple rows on Sheet 1 with the same model number. I don't know which items one sheet 2 go on which rows on sheet 1. I don't know how important column A (No) and B (Date) are in sheet1, but ithere is no way it can be correlated to the data in sheet2. I think what you really want is to take the data in sheet 2 and place it in sheet 1 with highest operation number going first. Think about what I wrote above and let me know how to proceeed. "Cam" wrote: Joel, Thanks for your patience and help. The code is almost what I wanted. When I run your data, it seemed like it look for data in sheet2 and add to existence data in sheet1. I would like to update all the data in sheet1 by using all data in sheet2. Example: Before running the macro, my data will have No., ShipDate & Model filled out up to 50 rows. Then column D:K could be blank. Once the macro is ran, it will fill in all the data starting from row 2 (No. 1001). Also, not fill in row 5 (No. 1004). The S/O on sheet 2 will not shown up once all the operations are completed and closed out. So if No. 1001 has all the S/O (not in sheet2 anymore) that are completed I will hide 1001 row. The same process repeat again. No. ShipDate Model 1300 1300 1500 1500 Operation Status 1001 17-Aug 100 1002 18-Aug 200 1003 19-Aug 200 1004 19-Aug 100 vendor vendor vendor vendor N/A N/A N/A N/A 1006 21-Aug 200 1007 25-Aug 100 1008 25-Aug 100 "Joel" wrote: You are going to have to fix the problems with your worksheet. Apparently you have spaces the cells that contain numbers. Make sure all the numbers are right justified in the cell (left justified indicates they are text). You can easily get rid of the spaces by using the Spreadsheet Replace (under edit menu). Highligh the cells containing number. the replace a single space with nothing. Just enter a space in the from box and leave the replace box empty. The code becomes very complex to handle numbers and text mixed. fix both sheet 1 and 2. Below is the code I think you want. I put in the Operation number and well as the SO number. Sub checkso() Const High2 = 0 Const High1 = 1 Const OP = 0 Const SO = 1 Dim R1300M100(2, 2) Dim R1300M200(2, 2) Dim R1500M100(2, 2) Dim R1500M200(2, 2) R1300M100(High1, OP) = 0 R1300M100(High2, OP) = 0 R1300M100(High1, SO) = 0 R1300M100(High2, SO) = 0 R1300M200(High1, OP) = 0 R1300M200(High2, OP) = 0 R1300M200(High1, SO) = 0 R1300M200(High2, SO) = 0 R1500M100(High1, OP) = 0 R1500M100(High2, OP) = 0 R1500M100(High1, SO) = 0 R1500M100(High2, SO) = 0 R1500M200(High1, OP) = 0 R1500M200(High2, OP) = 0 R1500M200(High1, SO) = 0 R1500M200(High2, SO) = 0 LastRowSh1 = Sheets("Sheet1"). _ Cells(Rows.Count, "A").End(xlUp).Row LastRowSh2 = Sheets("Sheet2"). _ Cells(Rows.Count, "A").End(xlUp).Row Sh1NewRow = LastRowSh1 + 1 With Sheets("Sheet2") For Sh2RowCount = 2 To LastRowSh2 Operation = .Cells(Sh2RowCount, "B") ShippingOrder = .Cells(Sh2RowCount, "A") If .Cells(Sh2RowCount, "C").Value = 1300 Then If .Cells(Sh2RowCount, "D").Value = 100 Then If Operation R1300M100(High1, OP) Then R1300M100(High2, OP) = _ R1300M100(High1, OP) R1300M100(High1, OP) = _ Operation R1300M100(High2, SO) = _ R1300M100(High1, SO) R1300M100(High1, SO) = _ ShippingOrder Else If Operation R1300M100(High2, OP) Then R1300M100(High2, OP) = _ Operation R1300M100(High2, SO) = _ |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help w/ look & fill in data
Joel,
The code is not working for me. It just filled out blank data in sheet1. I thought maybe to make it simplier by seperating the two models into individual sheet. So, sheet3 will still be the raw data, but now sheet1 is for 100 model and sheet2 for 200 model. look something like below. All the code have to do is to look at the raw data in sheet3, sort by model number and operation. Then, for all the data for model 100 in sheet1, fill in the S/O starting column D:J with the S/O that have the highest operation go first. Same for model 200. I forgot to mention that the Ref column in raw data sheet has difference number for model 100 & 200. See sample below. sheet1 No. ShipDate Model 1301 1301 1501 1501 Operation 1001 17-Aug 100 1004 19-Aug 100 vendor vendor vendor vendor N/A N/A N/A N/A 1007 25-Aug 100 1008 25-Aug 100 sheet2 No. ShipDate Model 1302 1302 1502 1502 Operation 1002 18-Aug 200 1003 19-Aug 200 1006 21-Aug 200 Sorry, for all the confusion. "Joel" wrote: Change one line of code to clear cell C to Z (make wnhat ever you need) From: clear entire row Rows(cell.Row).ClearContents To: clear cells C to Z .Range("C" & cell.Row & ":Z" & _ cell.Row).ClearContents "Cam" wrote: Thank you Joel. Can I not clear column A & B on sheet1? They will be template and information is fixed (all filled out up to No. 1100, etc). "Joel" wrote: I ws expecting that you would agree with me. Have been working the code before you asked for it. The code will clear sheet 1 and skip and rows with vendor in column D. I also cleared column A and B because I don't know what should be in these columns. the code has 3 subroutines to make the code common for all models and Ref values. Run checkso() Const OP = 0 Const SO = 1 Const Ref1300 = 0 Const Ref1500 = 2 Sub checkso() Dim R1300M100(1000, 2) Dim R1300M200(1000, 2) Dim R1500M100(1000, 2) Dim R1500M200(1000, 2) With Sheets("Sheet1") LastRowSh1 = _ .Cells(Rows.Count, "C").End(xlUp).Row Set ColDRange = .Range(.Cells(2, "D"), _ .Cells(LastRowSh1, "D")) For Each cell In ColDRange If cell < "vendor" Then Rows(cell.Row).ClearContents End If Next cell End With LastRowSh2 = Sheets("Sheet2"). _ Cells(Rows.Count, "A").End(xlUp).Row R1300M100Count = 0 R1300M200Count = 0 R1500M100Count = 0 R1500M200Count = 0 With Sheets("Sheet2") For Sh2RowCount = 2 To LastRowSh2 OPeration = .Cells(Sh2RowCount, "B") ShippingOrder = .Cells(Sh2RowCount, "A") Model = .Cells(Sh2RowCount, "D") If .Cells(Sh2RowCount, "C").Value = 1300 Then If .Cells(Sh2RowCount, "D").Value = 100 Then R1300M100Count = R1300M100Count + 1 R1300M100(R1300M100Count, OP) = _ OPeration R1300M100(R1300M100Count, SO) = _ ShippingOrder End If If .Cells(Sh2RowCount, "D").Value = 200 Then R1300M200Count = R1300M200Count + 1 R1300M200(R1300M200Count, OP) = _ OPeration R1300M200(R1300M200Count, SO) = _ ShippingOrder End If End If If .Cells(Sh2RowCount, "C").Value = 1500 Then If .Cells(Sh2RowCount, "D").Value = 100 Then R1500M100Count = R1500M100Count + 1 R1500M100(R1500M100Count, OP) = _ OPeration R1500M100(R1500M100Count, SO) = _ ShippingOrder End If If .Cells(Sh2RowCount, "D").Value = 200 Then R1500M200Count = R1500M200Count + 1 R1500M200(R1500M200Count, OP) = _ OPeration R1500M200(R1500M200Count, SO) = _ ShippingOrder End If End If Next Sh2RowCount End With Call SortData(R1300M100, R1300M100Count) Call SortData(R1300M200, R1300M200Count) Call SortData(R1500M100, R1500M100Count) Call SortData(R1500M200, R1500M200Count) Call InsertData(R1300M100, R1300M100Count, _ Ref1300, 100) Call InsertData(R1300M200, R1300M200Count, _ Ref1300, 200) Call InsertData(R1500M100, R1500M100Count, _ Ref1500, 100) Call InsertData(R1500M200, R1500M200Count, _ Ref1500, 200) End Sub Sub SortData(ByRef MyArray() As Variant, Count) For i = 0 To (Count - 2) For j = (i + 1) To (Count - 1) If MyArray(j, OP) MyArray(i, OP) Then Temp = MyArray(i, OP) MyArray(i, OP) = MyArray(j, OP) MyArray(j, OP) = Temp Temp = MyArray(i, SO) MyArray(i, SO) = MyArray(j, SO) MyArray(j, SO) = Temp End If Next j Next i End Sub Sub InsertData(ByRef MyArray() As Variant, _ Count, Ref, Model) With Sheets("Sheet1") RowCount = 2 MyOffset = 0 Do While (.Cells(RowCount, "D"). _ Offset(0, Ref) < "") And _ ((.Cells(RowCount, "C") < Model) Or _ (.Cells(RowCount, "D") = "vendor")) RowCount = RowCount + 1 Loop For LoopCount = 0 To (Count - 1) .Cells(RowCount, "D"). _ Offset(0, Ref + MyOffset) = _ MyArray(LoopCount, SO) .Cells(RowCount, "H"). _ Offset(0, Ref + MyOffset) = _ MyArray(LoopCount, OP) If MyOffset = 0 Then .Cells(RowCount, "C").Value = Model MyOffset = 1 Else RowCount = RowCount + 1 Do While (.Cells(RowCount, "D"). _ Offset(0, Ref) < "") And _ ((.Cells(RowCount, "C") < Model) Or _ (.Cells(RowCount, "D") = "vendor")) RowCount = RowCount + 1 Loop MyOffset = 0 End If Next LoopCount End With End Sub "Cam" wrote: Joel, Thank you for leading me to some thought. I think you are right that all I wanted was to take the raw data in sheet2 pulled from our system then place S/O in sheet2 based on the model and highest operation number going first. So, what I need to include in the code is the refresh (delete) all existing data in sheet1 first. Also, what I need to do manually is also, hide row with the No. (etc.. 1001) that are already completed so it will always start filling in from the first row and down. And I am not sure if the code also need to say not to fill in the data with the rows hidden. "Joel" wrote: Cam: It looks like you want my 1st solution, not the second solution! The problem is there are multiple rows on Sheet 1 with the same model number. I don't know which items one sheet 2 go on which rows on sheet 1. I don't know how important column A (No) and B (Date) are in sheet1, but ithere is no way it can be correlated to the data in sheet2. I think what you really want is to take the data in sheet 2 and place it in sheet 1 with highest operation number going first. Think about what I wrote above and let me know how to proceeed. "Cam" wrote: Joel, Thanks for your patience and help. The code is almost what I wanted. When I run your data, it seemed like it look for data in sheet2 and add to existence data in sheet1. I would like to update all the data in sheet1 by using all data in sheet2. Example: Before running the macro, my data will have No., ShipDate & Model filled out up to 50 rows. Then column D:K could be blank. Once the macro is ran, it will fill in all the data starting from row 2 (No. 1001). Also, not fill in row 5 (No. 1004). The S/O on sheet 2 will not shown up once all the operations are completed and closed out. So if No. 1001 has all the S/O (not in sheet2 anymore) that are completed I will hide 1001 row. The same process repeat again. No. ShipDate Model 1300 1300 1500 1500 Operation Status 1001 17-Aug 100 1002 18-Aug 200 1003 19-Aug 200 1004 19-Aug 100 vendor vendor vendor vendor N/A N/A N/A N/A 1006 21-Aug 200 1007 25-Aug 100 1008 25-Aug 100 "Joel" wrote: You are going to have to fix the problems with your worksheet. Apparently you have spaces the cells that contain numbers. Make sure all the numbers are right justified in the cell (left justified indicates they are text). You can easily get rid of the spaces by using the Spreadsheet Replace (under edit menu). Highligh the cells containing number. the replace a single space with nothing. Just enter a space in the from box and leave the replace box empty. The code becomes very complex to handle numbers and text mixed. fix both sheet 1 and 2. Below is the code I think you want. I put in the Operation number and well as the SO number. Sub checkso() Const High2 = 0 Const High1 = 1 Const OP = 0 Const SO = 1 Dim R1300M100(2, 2) Dim R1300M200(2, 2) Dim R1500M100(2, 2) Dim R1500M200(2, 2) R1300M100(High1, OP) = 0 R1300M100(High2, OP) = 0 R1300M100(High1, SO) = 0 R1300M100(High2, SO) = 0 R1300M200(High1, OP) = 0 R1300M200(High2, OP) = 0 R1300M200(High1, SO) = 0 R1300M200(High2, SO) = 0 R1500M100(High1, OP) = 0 R1500M100(High2, OP) = 0 R1500M100(High1, SO) = 0 R1500M100(High2, SO) = 0 R1500M200(High1, OP) = 0 R1500M200(High2, OP) = 0 R1500M200(High1, SO) = 0 R1500M200(High2, SO) = 0 LastRowSh1 = Sheets("Sheet1"). _ Cells(Rows.Count, "A").End(xlUp).Row LastRowSh2 = Sheets("Sheet2"). _ Cells(Rows.Count, "A").End(xlUp).Row Sh1NewRow = LastRowSh1 + 1 With Sheets("Sheet2") For Sh2RowCount = 2 To LastRowSh2 Operation = .Cells(Sh2RowCount, "B") ShippingOrder = .Cells(Sh2RowCount, "A") If .Cells(Sh2RowCount, "C").Value = 1300 Then If .Cells(Sh2RowCount, "D").Value = 100 Then If Operation R1300M100(High1, OP) Then R1300M100(High2, OP) = _ R1300M100(High1, OP) |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help w/ look & fill in data
Did the code every work? You have a new problem now which is more
complicated and before we start changing code lets get the easier first problem solved. Once I have the first problem solve the changes are pretty easy to get the 2nd version working. Why wasn't the original code working for you. Explain in detail! "Cam" wrote: Joel, The code is not working for me. It just filled out blank data in sheet1. I thought maybe to make it simplier by seperating the two models into individual sheet. So, sheet3 will still be the raw data, but now sheet1 is for 100 model and sheet2 for 200 model. look something like below. All the code have to do is to look at the raw data in sheet3, sort by model number and operation. Then, for all the data for model 100 in sheet1, fill in the S/O starting column D:J with the S/O that have the highest operation go first. Same for model 200. I forgot to mention that the Ref column in raw data sheet has difference number for model 100 & 200. See sample below. sheet1 No. ShipDate Model 1301 1301 1501 1501 Operation 1001 17-Aug 100 1004 19-Aug 100 vendor vendor vendor vendor N/A N/A N/A N/A 1007 25-Aug 100 1008 25-Aug 100 sheet2 No. ShipDate Model 1302 1302 1502 1502 Operation 1002 18-Aug 200 1003 19-Aug 200 1006 21-Aug 200 Sorry, for all the confusion. "Joel" wrote: Change one line of code to clear cell C to Z (make wnhat ever you need) From: clear entire row Rows(cell.Row).ClearContents To: clear cells C to Z .Range("C" & cell.Row & ":Z" & _ cell.Row).ClearContents "Cam" wrote: Thank you Joel. Can I not clear column A & B on sheet1? They will be template and information is fixed (all filled out up to No. 1100, etc). "Joel" wrote: I ws expecting that you would agree with me. Have been working the code before you asked for it. The code will clear sheet 1 and skip and rows with vendor in column D. I also cleared column A and B because I don't know what should be in these columns. the code has 3 subroutines to make the code common for all models and Ref values. Run checkso() Const OP = 0 Const SO = 1 Const Ref1300 = 0 Const Ref1500 = 2 Sub checkso() Dim R1300M100(1000, 2) Dim R1300M200(1000, 2) Dim R1500M100(1000, 2) Dim R1500M200(1000, 2) With Sheets("Sheet1") LastRowSh1 = _ .Cells(Rows.Count, "C").End(xlUp).Row Set ColDRange = .Range(.Cells(2, "D"), _ .Cells(LastRowSh1, "D")) For Each cell In ColDRange If cell < "vendor" Then Rows(cell.Row).ClearContents End If Next cell End With LastRowSh2 = Sheets("Sheet2"). _ Cells(Rows.Count, "A").End(xlUp).Row R1300M100Count = 0 R1300M200Count = 0 R1500M100Count = 0 R1500M200Count = 0 With Sheets("Sheet2") For Sh2RowCount = 2 To LastRowSh2 OPeration = .Cells(Sh2RowCount, "B") ShippingOrder = .Cells(Sh2RowCount, "A") Model = .Cells(Sh2RowCount, "D") If .Cells(Sh2RowCount, "C").Value = 1300 Then If .Cells(Sh2RowCount, "D").Value = 100 Then R1300M100Count = R1300M100Count + 1 R1300M100(R1300M100Count, OP) = _ OPeration R1300M100(R1300M100Count, SO) = _ ShippingOrder End If If .Cells(Sh2RowCount, "D").Value = 200 Then R1300M200Count = R1300M200Count + 1 R1300M200(R1300M200Count, OP) = _ OPeration R1300M200(R1300M200Count, SO) = _ ShippingOrder End If End If If .Cells(Sh2RowCount, "C").Value = 1500 Then If .Cells(Sh2RowCount, "D").Value = 100 Then R1500M100Count = R1500M100Count + 1 R1500M100(R1500M100Count, OP) = _ OPeration R1500M100(R1500M100Count, SO) = _ ShippingOrder End If If .Cells(Sh2RowCount, "D").Value = 200 Then R1500M200Count = R1500M200Count + 1 R1500M200(R1500M200Count, OP) = _ OPeration R1500M200(R1500M200Count, SO) = _ ShippingOrder End If End If Next Sh2RowCount End With Call SortData(R1300M100, R1300M100Count) Call SortData(R1300M200, R1300M200Count) Call SortData(R1500M100, R1500M100Count) Call SortData(R1500M200, R1500M200Count) Call InsertData(R1300M100, R1300M100Count, _ Ref1300, 100) Call InsertData(R1300M200, R1300M200Count, _ Ref1300, 200) Call InsertData(R1500M100, R1500M100Count, _ Ref1500, 100) Call InsertData(R1500M200, R1500M200Count, _ Ref1500, 200) End Sub Sub SortData(ByRef MyArray() As Variant, Count) For i = 0 To (Count - 2) For j = (i + 1) To (Count - 1) If MyArray(j, OP) MyArray(i, OP) Then Temp = MyArray(i, OP) MyArray(i, OP) = MyArray(j, OP) MyArray(j, OP) = Temp Temp = MyArray(i, SO) MyArray(i, SO) = MyArray(j, SO) MyArray(j, SO) = Temp End If Next j Next i End Sub Sub InsertData(ByRef MyArray() As Variant, _ Count, Ref, Model) With Sheets("Sheet1") RowCount = 2 MyOffset = 0 Do While (.Cells(RowCount, "D"). _ Offset(0, Ref) < "") And _ ((.Cells(RowCount, "C") < Model) Or _ (.Cells(RowCount, "D") = "vendor")) RowCount = RowCount + 1 Loop For LoopCount = 0 To (Count - 1) .Cells(RowCount, "D"). _ Offset(0, Ref + MyOffset) = _ MyArray(LoopCount, SO) .Cells(RowCount, "H"). _ Offset(0, Ref + MyOffset) = _ MyArray(LoopCount, OP) If MyOffset = 0 Then .Cells(RowCount, "C").Value = Model MyOffset = 1 Else RowCount = RowCount + 1 Do While (.Cells(RowCount, "D"). _ Offset(0, Ref) < "") And _ ((.Cells(RowCount, "C") < Model) Or _ (.Cells(RowCount, "D") = "vendor")) RowCount = RowCount + 1 Loop MyOffset = 0 End If Next LoopCount End With End Sub "Cam" wrote: Joel, Thank you for leading me to some thought. I think you are right that all I wanted was to take the raw data in sheet2 pulled from our system then place S/O in sheet2 based on the model and highest operation number going first. So, what I need to include in the code is the refresh (delete) all existing data in sheet1 first. Also, what I need to do manually is also, hide row with the No. (etc.. 1001) that are already completed so it will always start filling in from the first row and down. And I am not sure if the code also need to say not to fill in the data with the rows hidden. "Joel" wrote: Cam: It looks like you want my 1st solution, not the second solution! The problem is there are multiple rows on Sheet 1 with the same model number. I don't know which items one sheet 2 go on which rows on sheet 1. I don't know how important column A (No) and B (Date) are in sheet1, but ithere is no way it can be correlated to the data in sheet2. I think what you really want is to take the data in sheet 2 and place it in sheet 1 with highest operation number going first. Think about what I wrote above and let me know how to proceeed. "Cam" wrote: Joel, Thanks for your patience and help. The code is almost what I wanted. When I run your data, it seemed like it look for data in sheet2 and add to existence data in sheet1. I would like to update all the data in sheet1 by using all data in sheet2. Example: Before running the macro, my data will have No., ShipDate & Model filled out up to 50 rows. Then column D:K could be blank. Once the macro is ran, it will fill in all the data starting from row 2 (No. 1001). Also, not fill in row 5 (No. 1004). The S/O on sheet 2 will not shown up once all the operations are completed and closed out. So if No. 1001 has all the S/O (not in sheet2 anymore) that are completed I will hide 1001 row. The same process repeat again. No. ShipDate Model 1300 1300 1500 1500 Operation Status 1001 17-Aug 100 1002 18-Aug 200 1003 19-Aug 200 1004 19-Aug 100 vendor vendor vendor vendor N/A N/A N/A N/A 1006 21-Aug 200 1007 25-Aug 100 1008 25-Aug 100 "Joel" wrote: You are going to have to fix the problems with your worksheet. Apparently you have spaces the cells that contain numbers. Make sure all the numbers are right justified in the cell (left justified indicates they are text). You can easily get rid of the spaces by using the Spreadsheet Replace (under edit menu). Highligh the cells containing number. the replace a single space with nothing. Just enter a space in the from box and leave the replace box empty. The code becomes very complex to handle numbers and text mixed. fix both sheet 1 and 2. Below is the code I think you want. I put in the Operation number and well as the SO number. Sub checkso() Const High2 = 0 Const High1 = 1 Const OP = 0 Const SO = 1 Dim R1300M100(2, 2) Dim R1300M200(2, 2) Dim R1500M100(2, 2) Dim R1500M200(2, 2) R1300M100(High1, OP) = 0 R1300M100(High2, OP) = 0 R1300M100(High1, SO) = 0 R1300M100(High2, SO) = 0 R1300M200(High1, OP) = 0 R1300M200(High2, OP) = 0 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help w/ look & fill in data
Joel,
The original code has mismatch error cause the model column is a formula, I guess. The second time, it only filled in two rows out of all the raw data in sheet2. Can we start fresh? Another set requirement of 1760 was added. Also, the column changed. The letter represent column and because the model are split into two sheets, I hope the code is simplier. Here's the new data I would like to perform on the VBA. I hope this example is a lot more clearer. sheet1 LINE B C D E F G MODEL 1360-100 1560-100 1760-100 1002 100 1004 100 1005 100 1006 100 1010 100 sheet2 LINE B C D E F G MODEL 1360-100 1560-100 1760-100 1003 200 1007 200 1008 200 1009 200 sheet3 Order B Item D E F G H I J K Oper. M N 220720 1360-101 90 220721 1360-101 40 205659 1360-101 30 205660 1560-101 40 224256 1760-101 50 224253 1760-101 10 224251 1360-100 100 224252 1360-100 90 225157 1360-100 50 225158 1560-100 40 225159 1560-100 30 214058 1760-100 80 214063 1760-100 70 218799 1760-100 70 218800 1760-100 50 RESULTS: sheet1 LINE B C D E F G MODEL 1360-100 1560-100 1760-100 1002 100 100 90 40 30 80 70 1004 100 50 70 50 1005 100 1006 100 1010 100 sheet2 LINE B C D E F G MODEL 1360-100 1560-100 1760-100 1003 200 90 40 40 50 10 1007 200 30 1008 200 1009 200 "Joel" wrote: Did the code every work? You have a new problem now which is more complicated and before we start changing code lets get the easier first problem solved. Once I have the first problem solve the changes are pretty easy to get the 2nd version working. Why wasn't the original code working for you. Explain in detail! "Cam" wrote: Joel, The code is not working for me. It just filled out blank data in sheet1. I thought maybe to make it simplier by seperating the two models into individual sheet. So, sheet3 will still be the raw data, but now sheet1 is for 100 model and sheet2 for 200 model. look something like below. All the code have to do is to look at the raw data in sheet3, sort by model number and operation. Then, for all the data for model 100 in sheet1, fill in the S/O starting column D:J with the S/O that have the highest operation go first. Same for model 200. I forgot to mention that the Ref column in raw data sheet has difference number for model 100 & 200. See sample below. sheet1 No. ShipDate Model 1301 1301 1501 1501 Operation 1001 17-Aug 100 1004 19-Aug 100 vendor vendor vendor vendor N/A N/A N/A N/A 1007 25-Aug 100 1008 25-Aug 100 sheet2 No. ShipDate Model 1302 1302 1502 1502 Operation 1002 18-Aug 200 1003 19-Aug 200 1006 21-Aug 200 Sorry, for all the confusion. "Joel" wrote: Change one line of code to clear cell C to Z (make wnhat ever you need) From: clear entire row Rows(cell.Row).ClearContents To: clear cells C to Z .Range("C" & cell.Row & ":Z" & _ cell.Row).ClearContents "Cam" wrote: Thank you Joel. Can I not clear column A & B on sheet1? They will be template and information is fixed (all filled out up to No. 1100, etc). "Joel" wrote: I ws expecting that you would agree with me. Have been working the code before you asked for it. The code will clear sheet 1 and skip and rows with vendor in column D. I also cleared column A and B because I don't know what should be in these columns. the code has 3 subroutines to make the code common for all models and Ref values. Run checkso() Const OP = 0 Const SO = 1 Const Ref1300 = 0 Const Ref1500 = 2 Sub checkso() Dim R1300M100(1000, 2) Dim R1300M200(1000, 2) Dim R1500M100(1000, 2) Dim R1500M200(1000, 2) With Sheets("Sheet1") LastRowSh1 = _ .Cells(Rows.Count, "C").End(xlUp).Row Set ColDRange = .Range(.Cells(2, "D"), _ .Cells(LastRowSh1, "D")) For Each cell In ColDRange If cell < "vendor" Then Rows(cell.Row).ClearContents End If Next cell End With LastRowSh2 = Sheets("Sheet2"). _ Cells(Rows.Count, "A").End(xlUp).Row R1300M100Count = 0 R1300M200Count = 0 R1500M100Count = 0 R1500M200Count = 0 With Sheets("Sheet2") For Sh2RowCount = 2 To LastRowSh2 OPeration = .Cells(Sh2RowCount, "B") ShippingOrder = .Cells(Sh2RowCount, "A") Model = .Cells(Sh2RowCount, "D") If .Cells(Sh2RowCount, "C").Value = 1300 Then If .Cells(Sh2RowCount, "D").Value = 100 Then R1300M100Count = R1300M100Count + 1 R1300M100(R1300M100Count, OP) = _ OPeration R1300M100(R1300M100Count, SO) = _ ShippingOrder End If If .Cells(Sh2RowCount, "D").Value = 200 Then R1300M200Count = R1300M200Count + 1 R1300M200(R1300M200Count, OP) = _ OPeration R1300M200(R1300M200Count, SO) = _ ShippingOrder End If End If If .Cells(Sh2RowCount, "C").Value = 1500 Then If .Cells(Sh2RowCount, "D").Value = 100 Then R1500M100Count = R1500M100Count + 1 R1500M100(R1500M100Count, OP) = _ OPeration R1500M100(R1500M100Count, SO) = _ ShippingOrder End If If .Cells(Sh2RowCount, "D").Value = 200 Then R1500M200Count = R1500M200Count + 1 R1500M200(R1500M200Count, OP) = _ OPeration R1500M200(R1500M200Count, SO) = _ ShippingOrder End If End If Next Sh2RowCount End With Call SortData(R1300M100, R1300M100Count) Call SortData(R1300M200, R1300M200Count) Call SortData(R1500M100, R1500M100Count) Call SortData(R1500M200, R1500M200Count) Call InsertData(R1300M100, R1300M100Count, _ Ref1300, 100) Call InsertData(R1300M200, R1300M200Count, _ Ref1300, 200) Call InsertData(R1500M100, R1500M100Count, _ Ref1500, 100) Call InsertData(R1500M200, R1500M200Count, _ Ref1500, 200) End Sub Sub SortData(ByRef MyArray() As Variant, Count) For i = 0 To (Count - 2) For j = (i + 1) To (Count - 1) If MyArray(j, OP) MyArray(i, OP) Then Temp = MyArray(i, OP) MyArray(i, OP) = MyArray(j, OP) MyArray(j, OP) = Temp Temp = MyArray(i, SO) MyArray(i, SO) = MyArray(j, SO) MyArray(j, SO) = Temp End If Next j Next i End Sub Sub InsertData(ByRef MyArray() As Variant, _ Count, Ref, Model) With Sheets("Sheet1") RowCount = 2 MyOffset = 0 Do While (.Cells(RowCount, "D"). _ Offset(0, Ref) < "") And _ ((.Cells(RowCount, "C") < Model) Or _ (.Cells(RowCount, "D") = "vendor")) RowCount = RowCount + 1 Loop For LoopCount = 0 To (Count - 1) .Cells(RowCount, "D"). _ Offset(0, Ref + MyOffset) = _ MyArray(LoopCount, SO) .Cells(RowCount, "H"). _ Offset(0, Ref + MyOffset) = _ MyArray(LoopCount, OP) If MyOffset = 0 Then .Cells(RowCount, "C").Value = Model MyOffset = 1 Else RowCount = RowCount + 1 Do While (.Cells(RowCount, "D"). _ Offset(0, Ref) < "") And _ ((.Cells(RowCount, "C") < Model) Or _ (.Cells(RowCount, "D") = "vendor")) RowCount = RowCount + 1 Loop MyOffset = 0 End If Next LoopCount End With End Sub "Cam" wrote: Joel, Thank you for leading me to some thought. I think you are right that all I wanted was to take the raw data in sheet2 pulled from our system then place S/O in sheet2 based on the model and highest operation number going first. So, what I need to include in the code is the refresh (delete) all existing data in sheet1 first. Also, what I need to do manually is also, hide row with the No. (etc.. 1001) that are already completed so it will always start filling in from the first row and down. And I am not sure if the code also need to say not to fill in the data with the rows hidden. "Joel" wrote: Cam: It looks like you want my 1st solution, not the second solution! The problem is there are multiple rows on Sheet 1 with the same model number. I don't know which items one sheet 2 go on which rows on sheet 1. I don't know how important column A (No) and B (Date) are in sheet1, but ithere is no way it can be correlated to the data in sheet2. I think what you really want is to take the data in sheet 2 and place it in sheet 1 with highest operation number going first. Think about what I wrote above and let me know how to proceeed. "Cam" wrote: Joel, Thanks for your patience and help. The code is almost what I wanted. When I run your data, it seemed like it look for data in sheet2 and add to existence data in sheet1. I would like to update all the data in sheet1 by using all data in sheet2. Example: Before running the macro, my data will have No., ShipDate & Model filled out up to 50 rows. Then column D:K could be blank. Once the macro is ran, it will fill in all the data starting from row 2 (No. 1001). Also, not fill in row 5 (No. 1004). The S/O on sheet 2 will not shown up once all the operations are completed and closed out. So if No. 1001 has all the S/O (not in sheet2 anymore) that are completed I will hide 1001 row. The same process repeat again. No. ShipDate Model 1300 1300 1500 1500 Operation Status 1001 17-Aug 100 1002 18-Aug 200 1003 19-Aug 200 1004 19-Aug 100 vendor vendor vendor vendor N/A N/A N/A N/A 1006 21-Aug 200 1007 25-Aug 100 1008 25-Aug 100 "Joel" wrote: You are going to have to fix the problems with your worksheet. Apparently you have spaces the cells that contain numbers. Make sure all the numbers are right justified in the cell (left justified indicates they are text). You can easily get rid of the spaces by using the Spreadsheet Replace (under edit menu). Highligh the cells containing number. the replace a single space with nothing. Just enter a space in the from box and leave the replace box empty. The code becomes very complex to handle numbers and text mixed. fix both sheet 1 and 2. Below is the code I think you want. I put in the Operation number and well as the SO number. Sub checkso() Const High2 = 0 Const High1 = 1 Const OP = 0 Const SO = 1 Dim R1300M100(2, 2) Dim R1300M200(2, 2) |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help w/ look & fill in data
Lets not guess. It doesn't make sense that only two rows of data would get
filled in if there is a formula in the cells. It wouldn't of filled in any rows. Can you send me the data of what sheet 1 looked like after the first time it ran and the sheet 2 data you tried to put in the 2nd time. Thanks. I want to fixed the first problem before attacking the new new code. "Cam" wrote: Joel, The original code has mismatch error cause the model column is a formula, I guess. The second time, it only filled in two rows out of all the raw data in sheet2. Can we start fresh? Another set requirement of 1760 was added. Also, the column changed. The letter represent column and because the model are split into two sheets, I hope the code is simplier. Here's the new data I would like to perform on the VBA. I hope this example is a lot more clearer. sheet1 LINE B C D E F G MODEL 1360-100 1560-100 1760-100 1002 100 1004 100 1005 100 1006 100 1010 100 sheet2 LINE B C D E F G MODEL 1360-100 1560-100 1760-100 1003 200 1007 200 1008 200 1009 200 sheet3 Order B Item D E F G H I J K Oper. M N 220720 1360-101 90 220721 1360-101 40 205659 1360-101 30 205660 1560-101 40 224256 1760-101 50 224253 1760-101 10 224251 1360-100 100 224252 1360-100 90 225157 1360-100 50 225158 1560-100 40 225159 1560-100 30 214058 1760-100 80 214063 1760-100 70 218799 1760-100 70 218800 1760-100 50 RESULTS: sheet1 LINE B C D E F G MODEL 1360-100 1560-100 1760-100 1002 100 100 90 40 30 80 70 1004 100 50 70 50 1005 100 1006 100 1010 100 sheet2 LINE B C D E F G MODEL 1360-100 1560-100 1760-100 1003 200 90 40 40 50 10 1007 200 30 1008 200 1009 200 "Joel" wrote: Did the code every work? You have a new problem now which is more complicated and before we start changing code lets get the easier first problem solved. Once I have the first problem solve the changes are pretty easy to get the 2nd version working. Why wasn't the original code working for you. Explain in detail! "Cam" wrote: Joel, The code is not working for me. It just filled out blank data in sheet1. I thought maybe to make it simplier by seperating the two models into individual sheet. So, sheet3 will still be the raw data, but now sheet1 is for 100 model and sheet2 for 200 model. look something like below. All the code have to do is to look at the raw data in sheet3, sort by model number and operation. Then, for all the data for model 100 in sheet1, fill in the S/O starting column D:J with the S/O that have the highest operation go first. Same for model 200. I forgot to mention that the Ref column in raw data sheet has difference number for model 100 & 200. See sample below. sheet1 No. ShipDate Model 1301 1301 1501 1501 Operation 1001 17-Aug 100 1004 19-Aug 100 vendor vendor vendor vendor N/A N/A N/A N/A 1007 25-Aug 100 1008 25-Aug 100 sheet2 No. ShipDate Model 1302 1302 1502 1502 Operation 1002 18-Aug 200 1003 19-Aug 200 1006 21-Aug 200 Sorry, for all the confusion. "Joel" wrote: Change one line of code to clear cell C to Z (make wnhat ever you need) From: clear entire row Rows(cell.Row).ClearContents To: clear cells C to Z .Range("C" & cell.Row & ":Z" & _ cell.Row).ClearContents "Cam" wrote: Thank you Joel. Can I not clear column A & B on sheet1? They will be template and information is fixed (all filled out up to No. 1100, etc). "Joel" wrote: I ws expecting that you would agree with me. Have been working the code before you asked for it. The code will clear sheet 1 and skip and rows with vendor in column D. I also cleared column A and B because I don't know what should be in these columns. the code has 3 subroutines to make the code common for all models and Ref values. Run checkso() Const OP = 0 Const SO = 1 Const Ref1300 = 0 Const Ref1500 = 2 Sub checkso() Dim R1300M100(1000, 2) Dim R1300M200(1000, 2) Dim R1500M100(1000, 2) Dim R1500M200(1000, 2) With Sheets("Sheet1") LastRowSh1 = _ .Cells(Rows.Count, "C").End(xlUp).Row Set ColDRange = .Range(.Cells(2, "D"), _ .Cells(LastRowSh1, "D")) For Each cell In ColDRange If cell < "vendor" Then Rows(cell.Row).ClearContents End If Next cell End With LastRowSh2 = Sheets("Sheet2"). _ Cells(Rows.Count, "A").End(xlUp).Row R1300M100Count = 0 R1300M200Count = 0 R1500M100Count = 0 R1500M200Count = 0 With Sheets("Sheet2") For Sh2RowCount = 2 To LastRowSh2 OPeration = .Cells(Sh2RowCount, "B") ShippingOrder = .Cells(Sh2RowCount, "A") Model = .Cells(Sh2RowCount, "D") If .Cells(Sh2RowCount, "C").Value = 1300 Then If .Cells(Sh2RowCount, "D").Value = 100 Then R1300M100Count = R1300M100Count + 1 R1300M100(R1300M100Count, OP) = _ OPeration R1300M100(R1300M100Count, SO) = _ ShippingOrder End If If .Cells(Sh2RowCount, "D").Value = 200 Then R1300M200Count = R1300M200Count + 1 R1300M200(R1300M200Count, OP) = _ OPeration R1300M200(R1300M200Count, SO) = _ ShippingOrder End If End If If .Cells(Sh2RowCount, "C").Value = 1500 Then If .Cells(Sh2RowCount, "D").Value = 100 Then R1500M100Count = R1500M100Count + 1 R1500M100(R1500M100Count, OP) = _ OPeration R1500M100(R1500M100Count, SO) = _ ShippingOrder End If If .Cells(Sh2RowCount, "D").Value = 200 Then R1500M200Count = R1500M200Count + 1 R1500M200(R1500M200Count, OP) = _ OPeration R1500M200(R1500M200Count, SO) = _ ShippingOrder End If End If Next Sh2RowCount End With Call SortData(R1300M100, R1300M100Count) Call SortData(R1300M200, R1300M200Count) Call SortData(R1500M100, R1500M100Count) Call SortData(R1500M200, R1500M200Count) Call InsertData(R1300M100, R1300M100Count, _ Ref1300, 100) Call InsertData(R1300M200, R1300M200Count, _ Ref1300, 200) Call InsertData(R1500M100, R1500M100Count, _ Ref1500, 100) Call InsertData(R1500M200, R1500M200Count, _ Ref1500, 200) End Sub Sub SortData(ByRef MyArray() As Variant, Count) For i = 0 To (Count - 2) For j = (i + 1) To (Count - 1) If MyArray(j, OP) MyArray(i, OP) Then Temp = MyArray(i, OP) MyArray(i, OP) = MyArray(j, OP) MyArray(j, OP) = Temp Temp = MyArray(i, SO) MyArray(i, SO) = MyArray(j, SO) MyArray(j, SO) = Temp End If Next j Next i End Sub Sub InsertData(ByRef MyArray() As Variant, _ Count, Ref, Model) With Sheets("Sheet1") RowCount = 2 MyOffset = 0 Do While (.Cells(RowCount, "D"). _ Offset(0, Ref) < "") And _ ((.Cells(RowCount, "C") < Model) Or _ (.Cells(RowCount, "D") = "vendor")) RowCount = RowCount + 1 Loop For LoopCount = 0 To (Count - 1) .Cells(RowCount, "D"). _ Offset(0, Ref + MyOffset) = _ MyArray(LoopCount, SO) .Cells(RowCount, "H"). _ Offset(0, Ref + MyOffset) = _ MyArray(LoopCount, OP) If MyOffset = 0 Then .Cells(RowCount, "C").Value = Model MyOffset = 1 Else RowCount = RowCount + 1 Do While (.Cells(RowCount, "D"). _ Offset(0, Ref) < "") And _ ((.Cells(RowCount, "C") < Model) Or _ (.Cells(RowCount, "D") = "vendor")) RowCount = RowCount + 1 Loop MyOffset = 0 End If Next LoopCount End With End Sub "Cam" wrote: Joel, Thank you for leading me to some thought. I think you are right that all I wanted was to take the raw data in sheet2 pulled from our system then place S/O in sheet2 based on the model and highest operation number going first. So, what I need to include in the code is the refresh (delete) all existing data in sheet1 first. Also, what I need to do manually is also, hide row with the No. (etc.. 1001) that are already completed so it will always start filling in from the first row and down. And I am not sure if the code also need to say not to fill in the data with the rows hidden. "Joel" wrote: Cam: It looks like you want my 1st solution, not the second solution! The problem is there are multiple rows on Sheet 1 with the same model number. I |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help w/ look & fill in data
Joel,
All my data are in difference location and name and it will take me a long time modified the code or my existence data to match my data location. I have been working with it and I am completely lost. The previous response is actually where everything is located on my actual data so I think it will be much earlier for me to start from there. Sorry, I did not make this clear in the beginning. I cannot share the actual data on my company info. so I tried to modify several things even model number etc.. and hope to modified the code to match my actual data once the code worked. But I guess it is not working as I initially planned. If you don't mind modifying the code to match the sample data I indicated on previous response, I'll appreciated it. Thank you again. "Joel" wrote: Lets not guess. It doesn't make sense that only two rows of data would get filled in if there is a formula in the cells. It wouldn't of filled in any rows. Can you send me the data of what sheet 1 looked like after the first time it ran and the sheet 2 data you tried to put in the 2nd time. Thanks. I want to fixed the first problem before attacking the new new code. "Cam" wrote: Joel, The original code has mismatch error cause the model column is a formula, I guess. The second time, it only filled in two rows out of all the raw data in sheet2. Can we start fresh? Another set requirement of 1760 was added. Also, the column changed. The letter represent column and because the model are split into two sheets, I hope the code is simplier. Here's the new data I would like to perform on the VBA. I hope this example is a lot more clearer. sheet1 LINE B C D E F G MODEL 1360-100 1560-100 1760-100 1002 100 1004 100 1005 100 1006 100 1010 100 sheet2 LINE B C D E F G MODEL 1360-100 1560-100 1760-100 1003 200 1007 200 1008 200 1009 200 sheet3 Order B Item D E F G H I J K Oper. M N 220720 1360-101 90 220721 1360-101 40 205659 1360-101 30 205660 1560-101 40 224256 1760-101 50 224253 1760-101 10 224251 1360-100 100 224252 1360-100 90 225157 1360-100 50 225158 1560-100 40 225159 1560-100 30 214058 1760-100 80 214063 1760-100 70 218799 1760-100 70 218800 1760-100 50 RESULTS: sheet1 LINE B C D E F G MODEL 1360-100 1560-100 1760-100 1002 100 100 90 40 30 80 70 1004 100 50 70 50 1005 100 1006 100 1010 100 sheet2 LINE B C D E F G MODEL 1360-100 1560-100 1760-100 1003 200 90 40 40 50 10 1007 200 30 1008 200 1009 200 "Joel" wrote: Did the code every work? You have a new problem now which is more complicated and before we start changing code lets get the easier first problem solved. Once I have the first problem solve the changes are pretty easy to get the 2nd version working. Why wasn't the original code working for you. Explain in detail! "Cam" wrote: Joel, The code is not working for me. It just filled out blank data in sheet1. I thought maybe to make it simplier by seperating the two models into individual sheet. So, sheet3 will still be the raw data, but now sheet1 is for 100 model and sheet2 for 200 model. look something like below. All the code have to do is to look at the raw data in sheet3, sort by model number and operation. Then, for all the data for model 100 in sheet1, fill in the S/O starting column D:J with the S/O that have the highest operation go first. Same for model 200. I forgot to mention that the Ref column in raw data sheet has difference number for model 100 & 200. See sample below. sheet1 No. ShipDate Model 1301 1301 1501 1501 Operation 1001 17-Aug 100 1004 19-Aug 100 vendor vendor vendor vendor N/A N/A N/A N/A 1007 25-Aug 100 1008 25-Aug 100 sheet2 No. ShipDate Model 1302 1302 1502 1502 Operation 1002 18-Aug 200 1003 19-Aug 200 1006 21-Aug 200 Sorry, for all the confusion. "Joel" wrote: Change one line of code to clear cell C to Z (make wnhat ever you need) From: clear entire row Rows(cell.Row).ClearContents To: clear cells C to Z .Range("C" & cell.Row & ":Z" & _ cell.Row).ClearContents "Cam" wrote: Thank you Joel. Can I not clear column A & B on sheet1? They will be template and information is fixed (all filled out up to No. 1100, etc). "Joel" wrote: I ws expecting that you would agree with me. Have been working the code before you asked for it. The code will clear sheet 1 and skip and rows with vendor in column D. I also cleared column A and B because I don't know what should be in these columns. the code has 3 subroutines to make the code common for all models and Ref values. Run checkso() Const OP = 0 Const SO = 1 Const Ref1300 = 0 Const Ref1500 = 2 Sub checkso() Dim R1300M100(1000, 2) Dim R1300M200(1000, 2) Dim R1500M100(1000, 2) Dim R1500M200(1000, 2) With Sheets("Sheet1") LastRowSh1 = _ .Cells(Rows.Count, "C").End(xlUp).Row Set ColDRange = .Range(.Cells(2, "D"), _ .Cells(LastRowSh1, "D")) For Each cell In ColDRange If cell < "vendor" Then Rows(cell.Row).ClearContents End If Next cell End With LastRowSh2 = Sheets("Sheet2"). _ Cells(Rows.Count, "A").End(xlUp).Row R1300M100Count = 0 R1300M200Count = 0 R1500M100Count = 0 R1500M200Count = 0 With Sheets("Sheet2") For Sh2RowCount = 2 To LastRowSh2 OPeration = .Cells(Sh2RowCount, "B") ShippingOrder = .Cells(Sh2RowCount, "A") Model = .Cells(Sh2RowCount, "D") If .Cells(Sh2RowCount, "C").Value = 1300 Then If .Cells(Sh2RowCount, "D").Value = 100 Then R1300M100Count = R1300M100Count + 1 R1300M100(R1300M100Count, OP) = _ OPeration R1300M100(R1300M100Count, SO) = _ ShippingOrder End If If .Cells(Sh2RowCount, "D").Value = 200 Then R1300M200Count = R1300M200Count + 1 R1300M200(R1300M200Count, OP) = _ OPeration R1300M200(R1300M200Count, SO) = _ ShippingOrder End If End If If .Cells(Sh2RowCount, "C").Value = 1500 Then If .Cells(Sh2RowCount, "D").Value = 100 Then R1500M100Count = R1500M100Count + 1 R1500M100(R1500M100Count, OP) = _ OPeration R1500M100(R1500M100Count, SO) = _ ShippingOrder End If If .Cells(Sh2RowCount, "D").Value = 200 Then R1500M200Count = R1500M200Count + 1 R1500M200(R1500M200Count, OP) = _ OPeration R1500M200(R1500M200Count, SO) = _ ShippingOrder End If End If Next Sh2RowCount End With Call SortData(R1300M100, R1300M100Count) Call SortData(R1300M200, R1300M200Count) Call SortData(R1500M100, R1500M100Count) Call SortData(R1500M200, R1500M200Count) Call InsertData(R1300M100, R1300M100Count, _ Ref1300, 100) Call InsertData(R1300M200, R1300M200Count, _ Ref1300, 200) Call InsertData(R1500M100, R1500M100Count, _ Ref1500, 100) Call InsertData(R1500M200, R1500M200Count, _ Ref1500, 200) End Sub Sub SortData(ByRef MyArray() As Variant, Count) For i = 0 To (Count - 2) For j = (i + 1) To (Count - 1) If MyArray(j, OP) MyArray(i, OP) Then Temp = MyArray(i, OP) MyArray(i, OP) = MyArray(j, OP) MyArray(j, OP) = Temp Temp = MyArray(i, SO) MyArray(i, SO) = MyArray(j, SO) MyArray(j, SO) = Temp End If Next j Next i End Sub Sub InsertData(ByRef MyArray() As Variant, _ Count, Ref, Model) With Sheets("Sheet1") RowCount = 2 MyOffset = 0 Do While (.Cells(RowCount, "D"). _ Offset(0, Ref) < "") And _ ((.Cells(RowCount, "C") < Model) Or _ (.Cells(RowCount, "D") = "vendor")) RowCount = RowCount + 1 Loop For LoopCount = 0 To (Count - 1) .Cells(RowCount, "D"). _ Offset(0, Ref + MyOffset) = _ MyArray(LoopCount, SO) .Cells(RowCount, "H"). _ Offset(0, Ref + MyOffset) = _ MyArray(LoopCount, OP) If MyOffset = 0 Then .Cells(RowCount, "C").Value = Model MyOffset = 1 Else RowCount = RowCount + 1 Do While (.Cells(RowCount, "D"). _ Offset(0, Ref) < "") And _ ((.Cells(RowCount, "C") < Model) Or _ (.Cells(RowCount, "D") = "vendor")) RowCount = RowCount + 1 Loop MyOffset = 0 End If Next LoopCount End With End Sub "Cam" wrote: Joel, Thank you for leading me to some thought. I think you are right that all I wanted was to take the raw data in sheet2 pulled from our system then place S/O in sheet2 based on the model and highest operation number going first. |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help w/ look & fill in data
I will try to get to it tonight. From you descrion the problem has to be in
this do loop Do While (.Cells(RowCount, "D"). _ Offset(0, Ref) < "") And _ ((.Cells(RowCount, "C") < Model) Or _ (.Cells(RowCount, "D") = "vendor")) I don't plan to change this loop, but will try to as some debug statement to help find the problem. Because two line of data get output it means the sub InsertDat is failing which is the last thing run in the macro. The Do statementt is the only place in this sub that will cause a failure.. If a mismatch is occuring it means a cell is empty or the code is comparing a number with a string. Try making this change and let me know what happens Do While (not isempty(.Cells(RowCount, "D"). _ Offset(0, Ref))) And _ ((.Cells(RowCount, "C") < Model) Or _ (.Cells(RowCount, "D") = "vendor")) "Cam" wrote: Joel, The original code has mismatch error cause the model column is a formula, I guess. The second time, it only filled in two rows out of all the raw data in sheet2. Can we start fresh? Another set requirement of 1760 was added. Also, the column changed. The letter represent column and because the model are split into two sheets, I hope the code is simplier. Here's the new data I would like to perform on the VBA. I hope this example is a lot more clearer. sheet1 LINE B C D E F G MODEL 1360-100 1560-100 1760-100 1002 100 1004 100 1005 100 1006 100 1010 100 sheet2 LINE B C D E F G MODEL 1360-100 1560-100 1760-100 1003 200 1007 200 1008 200 1009 200 sheet3 Order B Item D E F G H I J K Oper. M N 220720 1360-101 90 220721 1360-101 40 205659 1360-101 30 205660 1560-101 40 224256 1760-101 50 224253 1760-101 10 224251 1360-100 100 224252 1360-100 90 225157 1360-100 50 225158 1560-100 40 225159 1560-100 30 214058 1760-100 80 214063 1760-100 70 218799 1760-100 70 218800 1760-100 50 RESULTS: sheet1 LINE B C D E F G MODEL 1360-100 1560-100 1760-100 1002 100 100 90 40 30 80 70 1004 100 50 70 50 1005 100 1006 100 1010 100 sheet2 LINE B C D E F G MODEL 1360-100 1560-100 1760-100 1003 200 90 40 40 50 10 1007 200 30 1008 200 1009 200 "Joel" wrote: Did the code every work? You have a new problem now which is more complicated and before we start changing code lets get the easier first problem solved. Once I have the first problem solve the changes are pretty easy to get the 2nd version working. Why wasn't the original code working for you. Explain in detail! "Cam" wrote: Joel, The code is not working for me. It just filled out blank data in sheet1. I thought maybe to make it simplier by seperating the two models into individual sheet. So, sheet3 will still be the raw data, but now sheet1 is for 100 model and sheet2 for 200 model. look something like below. All the code have to do is to look at the raw data in sheet3, sort by model number and operation. Then, for all the data for model 100 in sheet1, fill in the S/O starting column D:J with the S/O that have the highest operation go first. Same for model 200. I forgot to mention that the Ref column in raw data sheet has difference number for model 100 & 200. See sample below. sheet1 No. ShipDate Model 1301 1301 1501 1501 Operation 1001 17-Aug 100 1004 19-Aug 100 vendor vendor vendor vendor N/A N/A N/A N/A 1007 25-Aug 100 1008 25-Aug 100 sheet2 No. ShipDate Model 1302 1302 1502 1502 Operation 1002 18-Aug 200 1003 19-Aug 200 1006 21-Aug 200 Sorry, for all the confusion. "Joel" wrote: Change one line of code to clear cell C to Z (make wnhat ever you need) From: clear entire row Rows(cell.Row).ClearContents To: clear cells C to Z .Range("C" & cell.Row & ":Z" & _ cell.Row).ClearContents "Cam" wrote: Thank you Joel. Can I not clear column A & B on sheet1? They will be template and information is fixed (all filled out up to No. 1100, etc). "Joel" wrote: I ws expecting that you would agree with me. Have been working the code before you asked for it. The code will clear sheet 1 and skip and rows with vendor in column D. I also cleared column A and B because I don't know what should be in these columns. the code has 3 subroutines to make the code common for all models and Ref values. Run checkso() Const OP = 0 Const SO = 1 Const Ref1300 = 0 Const Ref1500 = 2 Sub checkso() Dim R1300M100(1000, 2) Dim R1300M200(1000, 2) Dim R1500M100(1000, 2) Dim R1500M200(1000, 2) With Sheets("Sheet1") LastRowSh1 = _ .Cells(Rows.Count, "C").End(xlUp).Row Set ColDRange = .Range(.Cells(2, "D"), _ .Cells(LastRowSh1, "D")) For Each cell In ColDRange If cell < "vendor" Then Rows(cell.Row).ClearContents End If Next cell End With LastRowSh2 = Sheets("Sheet2"). _ Cells(Rows.Count, "A").End(xlUp).Row R1300M100Count = 0 R1300M200Count = 0 R1500M100Count = 0 R1500M200Count = 0 With Sheets("Sheet2") For Sh2RowCount = 2 To LastRowSh2 OPeration = .Cells(Sh2RowCount, "B") ShippingOrder = .Cells(Sh2RowCount, "A") Model = .Cells(Sh2RowCount, "D") If .Cells(Sh2RowCount, "C").Value = 1300 Then If .Cells(Sh2RowCount, "D").Value = 100 Then R1300M100Count = R1300M100Count + 1 R1300M100(R1300M100Count, OP) = _ OPeration R1300M100(R1300M100Count, SO) = _ ShippingOrder End If If .Cells(Sh2RowCount, "D").Value = 200 Then R1300M200Count = R1300M200Count + 1 R1300M200(R1300M200Count, OP) = _ OPeration R1300M200(R1300M200Count, SO) = _ ShippingOrder End If End If If .Cells(Sh2RowCount, "C").Value = 1500 Then If .Cells(Sh2RowCount, "D").Value = 100 Then R1500M100Count = R1500M100Count + 1 R1500M100(R1500M100Count, OP) = _ OPeration R1500M100(R1500M100Count, SO) = _ ShippingOrder End If If .Cells(Sh2RowCount, "D").Value = 200 Then R1500M200Count = R1500M200Count + 1 R1500M200(R1500M200Count, OP) = _ OPeration R1500M200(R1500M200Count, SO) = _ ShippingOrder End If End If Next Sh2RowCount End With Call SortData(R1300M100, R1300M100Count) Call SortData(R1300M200, R1300M200Count) Call SortData(R1500M100, R1500M100Count) Call SortData(R1500M200, R1500M200Count) Call InsertData(R1300M100, R1300M100Count, _ Ref1300, 100) Call InsertData(R1300M200, R1300M200Count, _ Ref1300, 200) Call InsertData(R1500M100, R1500M100Count, _ Ref1500, 100) Call InsertData(R1500M200, R1500M200Count, _ Ref1500, 200) End Sub Sub SortData(ByRef MyArray() As Variant, Count) For i = 0 To (Count - 2) For j = (i + 1) To (Count - 1) If MyArray(j, OP) MyArray(i, OP) Then Temp = MyArray(i, OP) MyArray(i, OP) = MyArray(j, OP) MyArray(j, OP) = Temp Temp = MyArray(i, SO) MyArray(i, SO) = MyArray(j, SO) MyArray(j, SO) = Temp End If Next j Next i End Sub Sub InsertData(ByRef MyArray() As Variant, _ Count, Ref, Model) With Sheets("Sheet1") RowCount = 2 MyOffset = 0 Do While (.Cells(RowCount, "D"). _ Offset(0, Ref) < "") And _ ((.Cells(RowCount, "C") < Model) Or _ (.Cells(RowCount, "D") = "vendor")) RowCount = RowCount + 1 Loop For LoopCount = 0 To (Count - 1) .Cells(RowCount, "D"). _ Offset(0, Ref + MyOffset) = _ MyArray(LoopCount, SO) .Cells(RowCount, "H"). _ Offset(0, Ref + MyOffset) = _ MyArray(LoopCount, OP) If MyOffset = 0 Then .Cells(RowCount, "C").Value = Model MyOffset = 1 Else RowCount = RowCount + 1 Do While (.Cells(RowCount, "D"). _ Offset(0, Ref) < "") And _ ((.Cells(RowCount, "C") < Model) Or _ (.Cells(RowCount, "D") = "vendor")) RowCount = RowCount + 1 Loop MyOffset = 0 End If Next LoopCount End With End Sub "Cam" wrote: Joel, Thank you for leading me to some thought. I think you are right that all I wanted was to take the raw data in sheet2 pulled from our system then place S/O in sheet2 based on the model and highest operation number going first. So, what I need to include in the code is the refresh (delete) all existing data in sheet1 first. Also, what I need to do manually is also, hide row with the No. (etc.. 1001) that are already completed so it will always start filling in from the first row and down. And I am not sure if the code also need to say not to fill in the data with the rows hidden. "Joel" wrote: Cam: It looks like you want my 1st solution, not the second solution! The problem is there are multiple rows on Sheet 1 with the same model number. I |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help w/ look & fill in data
I did the best I could with the data you had posted. i couldn't tell what
columns the data was in. Also you were missing the Model number in sheet 3. I put the Model in Column B of sheet 3. Const OP = 0 Const SO = 1 Const Ref1300 = 0 Const Ref1500 = 1 Const REf1700 = 2 Sub checkso() Dim R1300M100(1000, 2) Dim R1300M200(1000, 2) Dim R1500M100(1000, 2) Dim R1500M200(1000, 2) Dim R1700M100(1000, 2) Dim R1700M200(1000, 2) With Sheets("Sheet1") LastRowSh1 = _ .Cells(Rows.Count, "H").End(xlUp).Row If LastRowSh1 < 1 Then Set ColHRange = .Range(.Cells(2, "H"), _ .Cells(LastRowSh1, "H")) For Each cell In ColHRange If cell < "vendor" Then Rows(cell.Row).ClearContents End If Next cell End If End With With Sheets("Sheet2") LastRowSh2 = _ .Cells(Rows.Count, "H").End(xlUp).Row If LastRowSh2 < 1 Then Set ColHRange = .Range(.Cells(2, "H"), _ .Cells(LastRowSh2, "H")) For Each cell In ColHRange If cell < "vendor" Then Rows(cell.Row).ClearContents End If Next cell End If End With LastRowSh3 = Sheets("Sheet3"). _ Cells(Rows.Count, "A").End(xlUp).Row R1300M100Count = 0 R1300M200Count = 0 R1500M100Count = 0 R1500M200Count = 0 R1700M100Count = 0 R1700M200Count = 0 With Sheets("Sheet3") For Sh3RowCount = 2 To LastRowSh3 OPeration = _ .Cells(Sh3RowCount, "L").Value ShippingOrder = _ .Cells(Sh3RowCount, "A").Value Model = _ .Cells(Sh3RowCount, "B").Value ITEM = Trim(.Cells(Sh3RowCount, "C")) If Left(ITEM, 2) = "13" Then If Model = 100 Then R1300M100Count = R1300M100Count + 1 R1300M100(R1300M100Count, OP) = _ OPeration R1300M100(R1300M100Count, SO) = _ ShippingOrder End If If Model = 200 Then R1300M200Count = R1300M200Count + 1 R1300M200(R1300M200Count, OP) = _ OPeration R1300M200(R1300M200Count, SO) = _ ShippingOrder End If End If If Left(ITEM, 2) = "15" Then If Model = 100 Then R1500M100Count = R1500M100Count + 1 R1500M100(R1500M100Count, OP) = _ OPeration R1500M100(R1500M100Count, SO) = _ ShippingOrder End If If Model = 200 Then R1500M200Count = R1500M200Count + 1 R1500M200(R1500M200Count, OP) = _ OPeration R1500M200(R1500M200Count, SO) = _ ShippingOrder End If End If If Left(ITEM, 2) = "17" Then If Model = 100 Then R1700M100Count = R1700M100Count + 1 R1700M100(R1700M100Count, OP) = _ OPeration R1700M100(R1700M100Count, SO) = _ ShippingOrder End If If Model = 200 Then R1700M200Count = R1700M200Count + 1 R1700M200(R1700M200Count, OP) = _ OPeration R1700M200(R1700M200Count, SO) = _ ShippingOrder End If End If Next Sh3RowCount End With Call SortData(R1300M100, R1300M100Count) Call SortData(R1300M200, R1300M200Count) Call SortData(R1500M100, R1500M100Count) Call SortData(R1500M200, R1500M200Count) Call SortData(R1700M100, R1700M100Count) Call SortData(R1700M200, R1700M200Count) Call InsertData(R1300M100, R1300M100Count, _ Ref1300, 100, "Sheet1") Call InsertData(R1300M200, R1300M200Count, _ Ref1300, 200, "Sheet2") Call InsertData(R1500M100, R1500M100Count, _ Ref1500, 100, "Sheet1") Call InsertData(R1500M200, R1500M200Count, _ Ref1500, 200, "Sheet2") Call InsertData(R1700M100, R1700M100Count, _ REf1700, 100, "Sheet1") Call InsertData(R1700M200, R1700M200Count, _ REf1700, 200, "Sheet2") End Sub Sub SortData(ByRef MyArray() As Variant, Count) For i = 0 To (Count - 1) For j = (i + 1) To Count If MyArray(j, OP) MyArray(i, OP) Then Temp = MyArray(i, OP) MyArray(i, OP) = MyArray(j, OP) MyArray(j, OP) = Temp Temp = MyArray(i, SO) MyArray(i, SO) = MyArray(j, SO) MyArray(j, SO) = Temp End If Next j Next i End Sub Sub InsertData(ByRef MyArray() As Variant, _ Count, Ref, Model, InsertSheet) With Sheets(InsertSheet) RowCount = 2 MyOffset = 0 Do While (Not IsEmpty(.Cells(RowCount, "B"). _ Offset(0, 2 * Ref))) And _ ((.Cells(RowCount, "H") < Model) Or _ (.Cells(RowCount, "I") = "vendor")) RowCount = RowCount + 1 Loop For LoopCount = 0 To (Count - 1) .Cells(RowCount, "I"). _ Offset(0, (2 * Ref) + MyOffset) = _ MyArray(LoopCount, SO) .Cells(RowCount, "B"). _ Offset(0, (2 * Ref) + MyOffset) = _ MyArray(LoopCount, OP) If MyOffset = 0 Then .Cells(RowCount, "H").Value = Model MyOffset = 1 Else RowCount = RowCount + 1 Do While (Not IsEmpty( _ .Cells(RowCount, "B"). _ Offset(0, 2 * Ref))) And _ ((.Cells(RowCount, "H") < Model) Or _ (.Cells(RowCount, "I") = "vendor")) RowCount = RowCount + 1 Loop MyOffset = 0 End If Next LoopCount End With End Sub "Joel" wrote: I will try to get to it tonight. From you descrion the problem has to be in this do loop Do While (.Cells(RowCount, "D"). _ Offset(0, Ref) < "") And _ ((.Cells(RowCount, "C") < Model) Or _ (.Cells(RowCount, "D") = "vendor")) I don't plan to change this loop, but will try to as some debug statement to help find the problem. Because two line of data get output it means the sub InsertDat is failing which is the last thing run in the macro. The Do statementt is the only place in this sub that will cause a failure.. If a mismatch is occuring it means a cell is empty or the code is comparing a number with a string. Try making this change and let me know what happens Do While (not isempty(.Cells(RowCount, "D"). _ Offset(0, Ref))) And _ ((.Cells(RowCount, "C") < Model) Or _ (.Cells(RowCount, "D") = "vendor")) "Cam" wrote: Joel, The original code has mismatch error cause the model column is a formula, I guess. The second time, it only filled in two rows out of all the raw data in sheet2. Can we start fresh? Another set requirement of 1760 was added. Also, the column changed. The letter represent column and because the model are split into two sheets, I hope the code is simplier. Here's the new data I would like to perform on the VBA. I hope this example is a lot more clearer. sheet1 LINE B C D E F G MODEL 1360-100 1560-100 1760-100 1002 100 1004 100 1005 100 1006 100 1010 100 sheet2 LINE B C D E F G MODEL 1360-100 1560-100 1760-100 1003 200 1007 200 1008 200 1009 200 sheet3 Order B Item D E F G H I J K Oper. M N 220720 1360-101 90 220721 1360-101 40 205659 1360-101 30 205660 1560-101 40 224256 1760-101 50 224253 1760-101 10 224251 1360-100 100 224252 1360-100 90 225157 1360-100 50 225158 1560-100 40 225159 1560-100 30 214058 1760-100 80 214063 1760-100 70 218799 1760-100 70 218800 1760-100 50 RESULTS: sheet1 LINE B C D E F G MODEL 1360-100 1560-100 1760-100 1002 100 100 90 40 30 80 70 1004 100 50 70 50 1005 100 1006 100 1010 100 sheet2 LINE B C D E F G MODEL 1360-100 1560-100 1760-100 1003 200 90 40 40 50 10 1007 200 30 1008 200 1009 200 "Joel" wrote: Did the code every work? You have a new problem now which is more complicated and before we start changing code lets get the easier first problem solved. Once I have the first problem solve the changes are pretty easy to get the 2nd version working. Why wasn't the original code working for you. Explain in detail! "Cam" wrote: Joel, The code is not working for me. It just filled out blank data in sheet1. I thought maybe to make it simplier by seperating the two models into individual sheet. So, sheet3 will still be the raw data, but now sheet1 is for 100 model and sheet2 for 200 model. look something like below. All the code have to do is to look at the raw data in sheet3, sort by model number and operation. Then, for all the data for model 100 in sheet1, fill in the S/O starting column D:J with the S/O that have the highest operation go first. Same for model 200. I forgot to mention that the Ref column in raw data sheet has difference number for model 100 & 200. See sample below. sheet1 No. ShipDate Model 1301 1301 1501 1501 Operation 1001 17-Aug 100 1004 19-Aug 100 vendor vendor vendor vendor N/A N/A N/A N/A 1007 25-Aug 100 1008 25-Aug 100 sheet2 No. ShipDate Model 1302 1302 1502 1502 Operation 1002 18-Aug 200 1003 19-Aug 200 1006 21-Aug 200 Sorry, for all the confusion. "Joel" wrote: Change one line of code to clear cell C to Z (make wnhat ever you need) From: clear entire row Rows(cell.Row).ClearContents To: clear cells C to Z .Range("C" & cell.Row & ":Z" & _ cell.Row).ClearContents "Cam" wrote: Thank you Joel. Can I not clear column A & B on sheet1? They will be template and information is fixed (all filled out up to No. 1100, etc). "Joel" wrote: I ws expecting that you would agree with me. Have been working the code before you asked for it. The code will clear sheet 1 and skip and rows with vendor in column D. I also cleared column A and B because I don't know what should be in these columns. the code has 3 subroutines to make the code common for all models and Ref values. Run checkso() Const OP = 0 Const SO = 1 Const Ref1300 = 0 Const Ref1500 = 2 Sub checkso() Dim R1300M100(1000, 2) Dim R1300M200(1000, 2) Dim R1500M100(1000, 2) Dim R1500M200(1000, 2) With Sheets("Sheet1") LastRowSh1 = _ .Cells(Rows.Count, "C").End(xlUp).Row Set ColDRange = .Range(.Cells(2, "D"), _ .Cells(LastRowSh1, "D")) For Each cell In ColDRange If cell < "vendor" Then Rows(cell.Row).ClearContents End If Next cell End With LastRowSh2 = Sheets("Sheet2"). _ Cells(Rows.Count, "A").End(xlUp).Row R1300M100Count = 0 R1300M200Count = 0 R1500M100Count = 0 R1500M200Count = 0 With Sheets("Sheet2") For Sh2RowCount = 2 To LastRowSh2 OPeration = .Cells(Sh2RowCount, "B") ShippingOrder = .Cells(Sh2RowCount, "A") Model = .Cells(Sh2RowCount, "D") If .Cells(Sh2RowCount, "C").Value = 1300 Then If .Cells(Sh2RowCount, "D").Value = 100 Then R1300M100Count = R1300M100Count + 1 R1300M100(R1300M100Count, OP) = _ OPeration R1300M100(R1300M100Count, SO) = _ ShippingOrder End If If .Cells(Sh2RowCount, "D").Value = 200 Then R1300M200Count = R1300M200Count + 1 R1300M200(R1300M200Count, OP) = _ OPeration R1300M200(R1300M200Count, SO) = _ ShippingOrder End If End If If .Cells(Sh2RowCount, "C").Value = 1500 Then If .Cells(Sh2RowCount, "D").Value = 100 Then R1500M100Count = R1500M100Count + 1 R1500M100(R1500M100Count, OP) = _ OPeration R1500M100(R1500M100Count, SO) = _ ShippingOrder End If If .Cells(Sh2RowCount, "D").Value = 200 Then R1500M200Count = R1500M200Count + 1 R1500M200(R1500M200Count, OP) = _ OPeration R1500M200(R1500M200Count, SO) = _ ShippingOrder End If End If Next Sh2RowCount End With Call SortData(R1300M100, R1300M100Count) Call SortData(R1300M200, R1300M200Count) Call SortData(R1500M100, R1500M100Count) Call SortData(R1500M200, R1500M200Count) Call InsertData(R1300M100, R1300M100Count, _ Ref1300, 100) Call InsertData(R1300M200, R1300M200Count, _ Ref1300, 200) Call InsertData(R1500M100, R1500M100Count, _ Ref1500, 100) Call InsertData(R1500M200, R1500M200Count, _ Ref1500, 200) End Sub Sub SortData(ByRef MyArray() As Variant, Count) For i = 0 To (Count - 2) For j = (i + 1) To (Count - 1) If MyArray(j, OP) MyArray(i, OP) Then Temp = MyArray(i, OP) MyArray(i, OP) = MyArray(j, OP) MyArray(j, OP) = Temp Temp = MyArray(i, SO) MyArray(i, SO) = MyArray(j, SO) MyArray(j, SO) = Temp End If Next j Next i End Sub Sub InsertData(ByRef MyArray() As Variant, _ Count, Ref, Model) With Sheets("Sheet1") RowCount = 2 MyOffset = 0 Do While (.Cells(RowCount, "D"). _ Offset(0, Ref) < "") And _ ((.Cells(RowCount, "C") < Model) Or _ (.Cells(RowCount, "D") = "vendor")) RowCount = RowCount + 1 Loop For LoopCount = 0 To (Count - 1) .Cells(RowCount, "D"). _ Offset(0, Ref + MyOffset) = _ MyArray(LoopCount, SO) .Cells(RowCount, "H"). _ Offset(0, Ref + MyOffset) = _ MyArray(LoopCount, OP) If MyOffset = 0 Then .Cells(RowCount, "C").Value = Model MyOffset = 1 Else RowCount = RowCount + 1 Do While (.Cells(RowCount, "D"). _ Offset(0, Ref) < "") And _ ((.Cells(RowCount, "C") < Model) Or _ (.Cells(RowCount, "D") = "vendor")) RowCount = RowCount + 1 Loop |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help w/ look & fill in data
Joel,
Thank you again, I ran some test data and it looked like it's working, but I do need couple changes to the code. 1) The Model number is in column "O" on sheet3 2) After the code is ran, the corresponding operation number is in column B: G, but they need to be in column O:T (right after the 6 columns for Item) on sheet1 & 2. Also, need to add: 1) on sheet3 where column H is the delivery date, so if I have more than one orders that have are in the same model, item and operation, I would like to also sort (ascending) by delivery date before transfering the data to sheet1 or sheet2. 2) If later on (expecting) I have more model (like 300) to add (extra sheet), is it easy to change the code to add them in? Again, thank you. "Joel" wrote: I did the best I could with the data you had posted. i couldn't tell what columns the data was in. Also you were missing the Model number in sheet 3. I put the Model in Column B of sheet 3. Const OP = 0 Const SO = 1 Const Ref1300 = 0 Const Ref1500 = 1 Const REf1700 = 2 Sub checkso() Dim R1300M100(1000, 2) Dim R1300M200(1000, 2) Dim R1500M100(1000, 2) Dim R1500M200(1000, 2) Dim R1700M100(1000, 2) Dim R1700M200(1000, 2) With Sheets("Sheet1") LastRowSh1 = _ .Cells(Rows.Count, "H").End(xlUp).Row If LastRowSh1 < 1 Then Set ColHRange = .Range(.Cells(2, "H"), _ .Cells(LastRowSh1, "H")) For Each cell In ColHRange If cell < "vendor" Then Rows(cell.Row).ClearContents End If Next cell End If End With With Sheets("Sheet2") LastRowSh2 = _ .Cells(Rows.Count, "H").End(xlUp).Row If LastRowSh2 < 1 Then Set ColHRange = .Range(.Cells(2, "H"), _ .Cells(LastRowSh2, "H")) For Each cell In ColHRange If cell < "vendor" Then Rows(cell.Row).ClearContents End If Next cell End If End With LastRowSh3 = Sheets("Sheet3"). _ Cells(Rows.Count, "A").End(xlUp).Row R1300M100Count = 0 R1300M200Count = 0 R1500M100Count = 0 R1500M200Count = 0 R1700M100Count = 0 R1700M200Count = 0 With Sheets("Sheet3") For Sh3RowCount = 2 To LastRowSh3 OPeration = _ .Cells(Sh3RowCount, "L").Value ShippingOrder = _ .Cells(Sh3RowCount, "A").Value Model = _ .Cells(Sh3RowCount, "B").Value ITEM = Trim(.Cells(Sh3RowCount, "C")) If Left(ITEM, 2) = "13" Then If Model = 100 Then R1300M100Count = R1300M100Count + 1 R1300M100(R1300M100Count, OP) = _ OPeration R1300M100(R1300M100Count, SO) = _ ShippingOrder End If If Model = 200 Then R1300M200Count = R1300M200Count + 1 R1300M200(R1300M200Count, OP) = _ OPeration R1300M200(R1300M200Count, SO) = _ ShippingOrder End If End If If Left(ITEM, 2) = "15" Then If Model = 100 Then R1500M100Count = R1500M100Count + 1 R1500M100(R1500M100Count, OP) = _ OPeration R1500M100(R1500M100Count, SO) = _ ShippingOrder End If If Model = 200 Then R1500M200Count = R1500M200Count + 1 R1500M200(R1500M200Count, OP) = _ OPeration R1500M200(R1500M200Count, SO) = _ ShippingOrder End If End If If Left(ITEM, 2) = "17" Then If Model = 100 Then R1700M100Count = R1700M100Count + 1 R1700M100(R1700M100Count, OP) = _ OPeration R1700M100(R1700M100Count, SO) = _ ShippingOrder End If If Model = 200 Then R1700M200Count = R1700M200Count + 1 R1700M200(R1700M200Count, OP) = _ OPeration R1700M200(R1700M200Count, SO) = _ ShippingOrder End If End If Next Sh3RowCount End With Call SortData(R1300M100, R1300M100Count) Call SortData(R1300M200, R1300M200Count) Call SortData(R1500M100, R1500M100Count) Call SortData(R1500M200, R1500M200Count) Call SortData(R1700M100, R1700M100Count) Call SortData(R1700M200, R1700M200Count) Call InsertData(R1300M100, R1300M100Count, _ Ref1300, 100, "Sheet1") Call InsertData(R1300M200, R1300M200Count, _ Ref1300, 200, "Sheet2") Call InsertData(R1500M100, R1500M100Count, _ Ref1500, 100, "Sheet1") Call InsertData(R1500M200, R1500M200Count, _ Ref1500, 200, "Sheet2") Call InsertData(R1700M100, R1700M100Count, _ REf1700, 100, "Sheet1") Call InsertData(R1700M200, R1700M200Count, _ REf1700, 200, "Sheet2") End Sub Sub SortData(ByRef MyArray() As Variant, Count) For i = 0 To (Count - 1) For j = (i + 1) To Count If MyArray(j, OP) MyArray(i, OP) Then Temp = MyArray(i, OP) MyArray(i, OP) = MyArray(j, OP) MyArray(j, OP) = Temp Temp = MyArray(i, SO) MyArray(i, SO) = MyArray(j, SO) MyArray(j, SO) = Temp End If Next j Next i End Sub Sub InsertData(ByRef MyArray() As Variant, _ Count, Ref, Model, InsertSheet) With Sheets(InsertSheet) RowCount = 2 MyOffset = 0 Do While (Not IsEmpty(.Cells(RowCount, "B"). _ Offset(0, 2 * Ref))) And _ ((.Cells(RowCount, "H") < Model) Or _ (.Cells(RowCount, "I") = "vendor")) RowCount = RowCount + 1 Loop For LoopCount = 0 To (Count - 1) .Cells(RowCount, "I"). _ Offset(0, (2 * Ref) + MyOffset) = _ MyArray(LoopCount, SO) .Cells(RowCount, "B"). _ Offset(0, (2 * Ref) + MyOffset) = _ MyArray(LoopCount, OP) If MyOffset = 0 Then .Cells(RowCount, "H").Value = Model MyOffset = 1 Else RowCount = RowCount + 1 Do While (Not IsEmpty( _ .Cells(RowCount, "B"). _ Offset(0, 2 * Ref))) And _ ((.Cells(RowCount, "H") < Model) Or _ (.Cells(RowCount, "I") = "vendor")) RowCount = RowCount + 1 Loop MyOffset = 0 End If Next LoopCount End With End Sub "Joel" wrote: I will try to get to it tonight. From you descrion the problem has to be in this do loop Do While (.Cells(RowCount, "D"). _ Offset(0, Ref) < "") And _ ((.Cells(RowCount, "C") < Model) Or _ (.Cells(RowCount, "D") = "vendor")) I don't plan to change this loop, but will try to as some debug statement to help find the problem. Because two line of data get output it means the sub InsertDat is failing which is the last thing run in the macro. The Do statementt is the only place in this sub that will cause a failure.. If a mismatch is occuring it means a cell is empty or the code is comparing a number with a string. Try making this change and let me know what happens Do While (not isempty(.Cells(RowCount, "D"). _ Offset(0, Ref))) And _ ((.Cells(RowCount, "C") < Model) Or _ (.Cells(RowCount, "D") = "vendor")) "Cam" wrote: Joel, The original code has mismatch error cause the model column is a formula, I guess. The second time, it only filled in two rows out of all the raw data in sheet2. Can we start fresh? Another set requirement of 1760 was added. Also, the column changed. The letter represent column and because the model are split into two sheets, I hope the code is simplier. Here's the new data I would like to perform on the VBA. I hope this example is a lot more clearer. sheet1 LINE B C D E F G MODEL 1360-100 1560-100 1760-100 1002 100 1004 100 1005 100 1006 100 1010 100 sheet2 LINE B C D E F G MODEL 1360-100 1560-100 1760-100 1003 200 1007 200 1008 200 1009 200 sheet3 Order B Item D E F G H I J K Oper. M N 220720 1360-101 90 220721 1360-101 40 205659 1360-101 30 205660 1560-101 40 224256 1760-101 50 224253 1760-101 10 224251 1360-100 100 224252 1360-100 90 225157 1360-100 50 225158 1560-100 40 225159 1560-100 30 214058 1760-100 80 214063 1760-100 70 218799 1760-100 70 218800 1760-100 50 RESULTS: sheet1 LINE B C D E F G MODEL 1360-100 1560-100 1760-100 1002 100 100 90 40 30 80 70 1004 100 50 70 50 1005 100 1006 100 1010 100 sheet2 LINE B C D E F G MODEL 1360-100 1560-100 1760-100 1003 200 90 40 40 50 10 1007 200 30 1008 200 1009 200 "Joel" wrote: Did the code every work? You have a new problem now which is more complicated and before we start changing code lets get the easier first problem solved. Once I have the first problem solve the changes are pretty |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA help w/ look & fill in data
Joel,
I also forgot to mention two more things about the current code. On sheet1 & 2, when "vendor" is write across the row of 1300 to 1700, the code overwrite the vendor with the S/O number. I need to code to skip that entire row and put in the S/O on the next row after. If any row is hidden (entire row "hide") in sheet1 or 2, I want to data to skip (not fill data) on those hidden rows. "Cam" wrote: Joel, Thank you again, I ran some test data and it looked like it's working, but I do need couple changes to the code. 1) The Model number is in column "O" on sheet3 2) After the code is ran, the corresponding operation number is in column B: G, but they need to be in column O:T (right after the 6 columns for Item) on sheet1 & 2. Also, need to add: 1) on sheet3 where column H is the delivery date, so if I have more than one orders that have are in the same model, item and operation, I would like to also sort (ascending) by delivery date before transfering the data to sheet1 or sheet2. 2) If later on (expecting) I have more model (like 300) to add (extra sheet), is it easy to change the code to add them in? Again, thank you. "Joel" wrote: I did the best I could with the data you had posted. i couldn't tell what columns the data was in. Also you were missing the Model number in sheet 3. I put the Model in Column B of sheet 3. Const OP = 0 Const SO = 1 Const Ref1300 = 0 Const Ref1500 = 1 Const REf1700 = 2 Sub checkso() Dim R1300M100(1000, 2) Dim R1300M200(1000, 2) Dim R1500M100(1000, 2) Dim R1500M200(1000, 2) Dim R1700M100(1000, 2) Dim R1700M200(1000, 2) With Sheets("Sheet1") LastRowSh1 = _ .Cells(Rows.Count, "H").End(xlUp).Row If LastRowSh1 < 1 Then Set ColHRange = .Range(.Cells(2, "H"), _ .Cells(LastRowSh1, "H")) For Each cell In ColHRange If cell < "vendor" Then Rows(cell.Row).ClearContents End If Next cell End If End With With Sheets("Sheet2") LastRowSh2 = _ .Cells(Rows.Count, "H").End(xlUp).Row If LastRowSh2 < 1 Then Set ColHRange = .Range(.Cells(2, "H"), _ .Cells(LastRowSh2, "H")) For Each cell In ColHRange If cell < "vendor" Then Rows(cell.Row).ClearContents End If Next cell End If End With LastRowSh3 = Sheets("Sheet3"). _ Cells(Rows.Count, "A").End(xlUp).Row R1300M100Count = 0 R1300M200Count = 0 R1500M100Count = 0 R1500M200Count = 0 R1700M100Count = 0 R1700M200Count = 0 With Sheets("Sheet3") For Sh3RowCount = 2 To LastRowSh3 OPeration = _ .Cells(Sh3RowCount, "L").Value ShippingOrder = _ .Cells(Sh3RowCount, "A").Value Model = _ .Cells(Sh3RowCount, "B").Value ITEM = Trim(.Cells(Sh3RowCount, "C")) If Left(ITEM, 2) = "13" Then If Model = 100 Then R1300M100Count = R1300M100Count + 1 R1300M100(R1300M100Count, OP) = _ OPeration R1300M100(R1300M100Count, SO) = _ ShippingOrder End If If Model = 200 Then R1300M200Count = R1300M200Count + 1 R1300M200(R1300M200Count, OP) = _ OPeration R1300M200(R1300M200Count, SO) = _ ShippingOrder End If End If If Left(ITEM, 2) = "15" Then If Model = 100 Then R1500M100Count = R1500M100Count + 1 R1500M100(R1500M100Count, OP) = _ OPeration R1500M100(R1500M100Count, SO) = _ ShippingOrder End If If Model = 200 Then R1500M200Count = R1500M200Count + 1 R1500M200(R1500M200Count, OP) = _ OPeration R1500M200(R1500M200Count, SO) = _ ShippingOrder End If End If If Left(ITEM, 2) = "17" Then If Model = 100 Then R1700M100Count = R1700M100Count + 1 R1700M100(R1700M100Count, OP) = _ OPeration R1700M100(R1700M100Count, SO) = _ ShippingOrder End If If Model = 200 Then R1700M200Count = R1700M200Count + 1 R1700M200(R1700M200Count, OP) = _ OPeration R1700M200(R1700M200Count, SO) = _ ShippingOrder End If End If Next Sh3RowCount End With Call SortData(R1300M100, R1300M100Count) Call SortData(R1300M200, R1300M200Count) Call SortData(R1500M100, R1500M100Count) Call SortData(R1500M200, R1500M200Count) Call SortData(R1700M100, R1700M100Count) Call SortData(R1700M200, R1700M200Count) Call InsertData(R1300M100, R1300M100Count, _ Ref1300, 100, "Sheet1") Call InsertData(R1300M200, R1300M200Count, _ Ref1300, 200, "Sheet2") Call InsertData(R1500M100, R1500M100Count, _ Ref1500, 100, "Sheet1") Call InsertData(R1500M200, R1500M200Count, _ Ref1500, 200, "Sheet2") Call InsertData(R1700M100, R1700M100Count, _ REf1700, 100, "Sheet1") Call InsertData(R1700M200, R1700M200Count, _ REf1700, 200, "Sheet2") End Sub Sub SortData(ByRef MyArray() As Variant, Count) For i = 0 To (Count - 1) For j = (i + 1) To Count If MyArray(j, OP) MyArray(i, OP) Then Temp = MyArray(i, OP) MyArray(i, OP) = MyArray(j, OP) MyArray(j, OP) = Temp Temp = MyArray(i, SO) MyArray(i, SO) = MyArray(j, SO) MyArray(j, SO) = Temp End If Next j Next i End Sub Sub InsertData(ByRef MyArray() As Variant, _ Count, Ref, Model, InsertSheet) With Sheets(InsertSheet) RowCount = 2 MyOffset = 0 Do While (Not IsEmpty(.Cells(RowCount, "B"). _ Offset(0, 2 * Ref))) And _ ((.Cells(RowCount, "H") < Model) Or _ (.Cells(RowCount, "I") = "vendor")) RowCount = RowCount + 1 Loop For LoopCount = 0 To (Count - 1) .Cells(RowCount, "I"). _ Offset(0, (2 * Ref) + MyOffset) = _ MyArray(LoopCount, SO) .Cells(RowCount, "B"). _ Offset(0, (2 * Ref) + MyOffset) = _ MyArray(LoopCount, OP) If MyOffset = 0 Then .Cells(RowCount, "H").Value = Model MyOffset = 1 Else RowCount = RowCount + 1 Do While (Not IsEmpty( _ .Cells(RowCount, "B"). _ Offset(0, 2 * Ref))) And _ ((.Cells(RowCount, "H") < Model) Or _ (.Cells(RowCount, "I") = "vendor")) RowCount = RowCount + 1 Loop MyOffset = 0 End If Next LoopCount End With End Sub "Joel" wrote: I will try to get to it tonight. From you descrion the problem has to be in this do loop Do While (.Cells(RowCount, "D"). _ Offset(0, Ref) < "") And _ ((.Cells(RowCount, "C") < Model) Or _ (.Cells(RowCount, "D") = "vendor")) I don't plan to change this loop, but will try to as some debug statement to help find the problem. Because two line of data get output it means the sub InsertDat is failing which is the last thing run in the macro. The Do statementt is the only place in this sub that will cause a failure.. If a mismatch is occuring it means a cell is empty or the code is comparing a number with a string. Try making this change and let me know what happens Do While (not isempty(.Cells(RowCount, "D"). _ Offset(0, Ref))) And _ ((.Cells(RowCount, "C") < Model) Or _ (.Cells(RowCount, "D") = "vendor")) "Cam" wrote: Joel, The original code has mismatch error cause the model column is a formula, I guess. The second time, it only filled in two rows out of all the raw data in sheet2. Can we start fresh? Another set requirement of 1760 was added. Also, the column changed. The letter represent column and because the model are split into two sheets, I hope the code is simplier. Here's the new data I would like to perform on the VBA. I hope this example is a lot more clearer. sheet1 LINE B C D E F G MODEL 1360-100 1560-100 1760-100 1002 100 1004 100 1005 100 1006 100 1010 100 sheet2 LINE B C D E F G MODEL 1360-100 1560-100 1760-100 1003 200 1007 200 1008 200 1009 200 sheet3 Order B Item D E F G H I J K Oper. M N 220720 1360-101 90 220721 1360-101 40 205659 1360-101 30 205660 1560-101 40 224256 1760-101 50 224253 1760-101 10 224251 1360-100 100 224252 1360-100 90 225157 1360-100 50 225158 1560-100 40 225159 1560-100 30 214058 1760-100 80 214063 1760-100 70 218799 1760-100 70 218800 1760-100 50 RESULTS: sheet1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FILL IN DATA USING KEY VALUE | Excel Worksheet Functions | |||
Filter data and fill only filtered data | Excel Discussion (Misc queries) | |||
fill data | Excel Discussion (Misc queries) | |||
pls help for fill data | Excel Programming | |||
I have a list of data, fill in the gaps. FILL function won't work | Excel Discussion (Misc queries) |