| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Filling in Data | Excel Discussion (Misc queries) | |||
| Filling in data when... | Excel Worksheet Functions | |||
| Filling in missing data | Excel Discussion (Misc queries) | |||
| excel novice question on filling in missing fields/cells | Excel Discussion (Misc queries) | |||
| Filling in missing dates | Excel Worksheet Functions | |||
Linear Mode
