|
Filling in missing data
I will try to make this as clear as I can. It is quite a challenge and I
have no idea how to do it. - Data is received from client, so I have to control over the content - I need to quote on part numbers that may be supplied by multiple vendors - IDEALLY the data is as follows: Description Part Number Vendor Qty Price Widgets 1111 ABC 500 .25 Widgets 2222 XYZ 500 .25 Widgets 3333 MMM 500 .25 However, often the data is received like this: Description Part Number Vendor Qty Price Widgets 1111 ABC 500 .25 2222 XYZ 500 .25 3333 MMM 500 .25 Code Required: If there is a part number with no description to the left of it, copy the description above so that it looks like scenario 1 above. Thanks! -- Thanks! Dee |
Filling in missing data
Hi dee,
This macro does the job: Sub Fillmiss() Range("A1").Select partnums = Range("B:B").Find(What:="*", _ After:=Range("B1"), _ LookAt:=xlPart, _ LookIn:=xlValues, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row Range("A1:A" & partnums).Select Selection.ColumnDifferences(ActiveCell).Select lastRow = 1 firstrow = ActiveCell.Row Do While ActiveCell.Row < partnums firstrow = ActiveCell.Row cellVal = ActiveCell.Value On Error GoTo vege Range("A" & lastRow + 1 & ":A" & partnums).Select Selection.ColumnDifferences(ActiveCell).Select lastRow = ActiveCell.Row - 1 If ActiveCell = "" Then Selection.ColumnDifferences(ActiveCell).Select lastRow = ActiveCell.Row - 1 Range("A" & firstrow & ":A" & lastRow).Value = cellVal End If Loop Exit Sub vege: Range("A" & lastRow & ":A" & partnums).Value = cellVal End Sub I hope I didn't make many errors in it! Regards, Stefi €ždee€ť ezt Ă*rta: I will try to make this as clear as I can. It is quite a challenge and I have no idea how to do it. - Data is received from client, so I have to control over the content - I need to quote on part numbers that may be supplied by multiple vendors - IDEALLY the data is as follows: Description Part Number Vendor Qty Price Widgets 1111 ABC 500 .25 Widgets 2222 XYZ 500 .25 Widgets 3333 MMM 500 .25 However, often the data is received like this: Description Part Number Vendor Qty Price Widgets 1111 ABC 500 .25 2222 XYZ 500 .25 3333 MMM 500 .25 Code Required: If there is a part number with no description to the left of it, copy the description above so that it looks like scenario 1 above. Thanks! -- Thanks! Dee |
Filling in missing data
Hi again,
The code worked beautifully - thank you. I am wondering if it can be adjusted to work maybe column by column. Sometimes the column that needs to be filled in isn't column A. Any assistance is so appreciated! -- Thanks! Dee "Stefi" wrote: Hi dee, This macro does the job: Sub Fillmiss() Range("A1").Select partnums = Range("B:B").Find(What:="*", _ After:=Range("B1"), _ LookAt:=xlPart, _ LookIn:=xlValues, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row Range("A1:A" & partnums).Select Selection.ColumnDifferences(ActiveCell).Select lastRow = 1 firstrow = ActiveCell.Row Do While ActiveCell.Row < partnums firstrow = ActiveCell.Row cellVal = ActiveCell.Value On Error GoTo vege Range("A" & lastRow + 1 & ":A" & partnums).Select Selection.ColumnDifferences(ActiveCell).Select lastRow = ActiveCell.Row - 1 If ActiveCell = "" Then Selection.ColumnDifferences(ActiveCell).Select lastRow = ActiveCell.Row - 1 Range("A" & firstrow & ":A" & lastRow).Value = cellVal End If Loop Exit Sub vege: Range("A" & lastRow & ":A" & partnums).Value = cellVal End Sub I hope I didn't make many errors in it! Regards, Stefi €ždee€ť ezt Ă*rta: I will try to make this as clear as I can. It is quite a challenge and I have no idea how to do it. - Data is received from client, so I have to control over the content - I need to quote on part numbers that may be supplied by multiple vendors - IDEALLY the data is as follows: Description Part Number Vendor Qty Price Widgets 1111 ABC 500 .25 Widgets 2222 XYZ 500 .25 Widgets 3333 MMM 500 .25 However, often the data is received like this: Description Part Number Vendor Qty Price Widgets 1111 ABC 500 .25 2222 XYZ 500 .25 3333 MMM 500 .25 Code Required: If there is a part number with no description to the left of it, copy the description above so that it looks like scenario 1 above. Thanks! -- Thanks! Dee |
Filling in missing data
Hi Dee,
Try this: Sub Fillmiss(ColToBeFilled) Range(ColToBeFilled & "1").Select partnums = Range("B:B").Find(What:="*", _ After:=Range("B1"), _ LookAt:=xlPart, _ LookIn:=xlValues, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row Range(ColToBeFilled & "1:" & ColToBeFilled & partnums).Select Selection.ColumnDifferences(ActiveCell).Select lastRow = 1 firstrow = ActiveCell.Row Do While ActiveCell.Row < partnums firstrow = ActiveCell.Row cellVal = ActiveCell.Value On Error GoTo vege Range(ColToBeFilled & lastRow + 1 & ":" & ColToBeFilled & partnums).Select Selection.ColumnDifferences(ActiveCell).Select lastRow = ActiveCell.Row - 1 If ActiveCell = "" Then Selection.ColumnDifferences(ActiveCell).Select lastRow = ActiveCell.Row - 1 Range(ColToBeFilled & firstrow & ":" & ColToBeFilled & lastRow).Value = cellVal End If Loop Exit Sub vege: Range(ColToBeFilled & lastRow & ":" & ColToBeFilled & partnums).Value = cellVal End Sub Call it this way: Call Fillmiss("A") Call Fillmiss("C"), etc. Regards, Stefi €ždee€ť ezt Ă*rta: Hi again, The code worked beautifully - thank you. I am wondering if it can be adjusted to work maybe column by column. Sometimes the column that needs to be filled in isn't column A. Any assistance is so appreciated! -- Thanks! Dee "Stefi" wrote: Hi dee, This macro does the job: Sub Fillmiss() Range("A1").Select partnums = Range("B:B").Find(What:="*", _ After:=Range("B1"), _ LookAt:=xlPart, _ LookIn:=xlValues, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row Range("A1:A" & partnums).Select Selection.ColumnDifferences(ActiveCell).Select lastRow = 1 firstrow = ActiveCell.Row Do While ActiveCell.Row < partnums firstrow = ActiveCell.Row cellVal = ActiveCell.Value On Error GoTo vege Range("A" & lastRow + 1 & ":A" & partnums).Select Selection.ColumnDifferences(ActiveCell).Select lastRow = ActiveCell.Row - 1 If ActiveCell = "" Then Selection.ColumnDifferences(ActiveCell).Select lastRow = ActiveCell.Row - 1 Range("A" & firstrow & ":A" & lastRow).Value = cellVal End If Loop Exit Sub vege: Range("A" & lastRow & ":A" & partnums).Value = cellVal End Sub I hope I didn't make many errors in it! Regards, Stefi €ždee€ť ezt Ă*rta: I will try to make this as clear as I can. It is quite a challenge and I have no idea how to do it. - Data is received from client, so I have to control over the content - I need to quote on part numbers that may be supplied by multiple vendors - IDEALLY the data is as follows: Description Part Number Vendor Qty Price Widgets 1111 ABC 500 .25 Widgets 2222 XYZ 500 .25 Widgets 3333 MMM 500 .25 However, often the data is received like this: Description Part Number Vendor Qty Price Widgets 1111 ABC 500 .25 2222 XYZ 500 .25 3333 MMM 500 .25 Code Required: If there is a part number with no description to the left of it, copy the description above so that it looks like scenario 1 above. Thanks! -- Thanks! Dee |
| All times are GMT +1. The time now is 04:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com