VBA Help please (rookie issue)

S

Scott Wagner

Making an other attempt to explain this, as I think I haven't done a good job
in previous posts. (my appologies)

I have a worksheet that containes product lists. Some are related in that
they are part of an assembly, but appear as seperate lines. In the case
where they are a component of a larger assembly column D is blank for that
item. Each assembly has a master line that includes an entry in column D and
also may have a marking in column A (but not always). In the cases where
column D is blank I need to have the value in column A copied from one row
above.

Also, column B contains the qty of each assembly for the master line, and
qty consists of for the component line. I need to have the mater line qty
over write each of the component line qty values. In the cases where column D
is blank I need to have the value in column B copied from one row above.

Need to do this with a macro. I have a bunch of code already in use that
gets the sheet to this point. The processes described above are the last
steps between me and this being done. Any help you can provide would be
greatly appreciated.

Below is an example

What I have now:
ColA ColB ColC ColD
1 Starter Open Type
3 XFMR Dry Type
AAA 4 Panelboard Type B
1 Box
1 Trim
BBB 2 Panelboard Type Q
1 Box
1 Trim
1 Ground Bar

What I need to end up with:
ColA ColB ColC ColD
1 Starter Open Type
3 XFMR Dry Type
AAA 4 Panelboard Type B
AAA 4 Box
AAA 4 Trim
BBB 2 Panelboard Type Q
BBB 2 Box
BBB 2 Trim
BBB 2 Ground Bar

Thanks in advance.
 
D

Don Guillett

try
Sub fillinblanks()
lr = Cells(Rows.Count, "g").End(xlUp).Row
For Each c In Range("g2:g" & lr)
If Len(c.Offset(1)) < 2 Then c.Offset(1) = c
Next
End Sub
 
S

Scott Wagner

This does fill in blanks, but doesn't distingish based on another column as
described.

I really need to be able to control where it populates based on that key
column.

Also, this doesn't address the qty item I described.

Any ideas?

Scott
 
D

Don Guillett

try this instead where it looks to col C to find the last row and uses col
D, as desired

Sub fillinblanksN()
lr = Cells(Rows.Count, "c").End(xlUp).Row
For Each c In Range("d2:d" & lr)
If Len(c) < 2 Then
c.Offset(0, -3) = c.Offset(-1, -3)
c.Offset(0, -2) = c.Offset(-1, -2)
End If
Next
End Sub
 
F

flummi

Try this:

Set r1 = Range("a2:b10")
rcnt = r1.Rows.Count
For i = 1 To rcnt
If r1.Cells(i, 1).Value = "" Then
If last_a = "" Then
Else
r1.Cells(i, 1).Value = last_a
r1.Cells(i, 2).Value = last_b
End If
Else
last_a = r1.Cells(i, 1).Value
last_b = r1.Cells(i, 2).Value
End If
Next i

Hans
 

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

Top