Filling in missing data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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:
 
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!
 
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:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top