VBA Question - Applying values

[Scott Wagner]

I'm hoping to get some help with something that feels complex to me, that may
or may not be for the guru's here.

In my worksheet I have products that, in some cases, have components as well
as a master line item. There are two properties of the master line I need to
apply to the component lines.

Property 1: The master line item has a master quantity that all the
component quantities must be multiplied by.

Property 2: The master line item sometimes has a marking that also must
appear on all component lines.

The master lines and component lines can be distingished from each other
easily. The master lines have a line item number (not quantity) in column A.
It is sequenced line item 1, line item 2, line item 3, etc. The component
lines have nothing in column A.

Here is an example of what I have now:

ColA | ColB | ColC | ColD | ColE |
Line # | Qty | Description | Part # | Mark |
1 | 3 | MasterLine1 | ABC123 | H1 |
| 1 | Component1 | EFG123 | |
| 2 | Component2 | HIJ123 | |
2 | 2 | MasterLine2 | ABC123 | H2 |
| 1 | Component1 | EFG123 | |
| 2 | Component2 | HIJ123 | |


Here is what I want to end up with:
ColA | ColB | ColC | ColD | ColE |
Line # | Qty | Description | Part # | Mark |
1 | 3 | MasterLine1 | ABC123 | H1 |
| 3 | Component1 | EFG123 | H1 |
| 6 | Component2 | HIJ123 | H1 |
2 | 2 | MasterLine2 | ABC123 | H2 |
| 2 | Component1 | EFG123 | H2 |
| 4 | Component2 | HIJ123 | H2 |

Thanks in advance!

Scott

 

[Greg Wilson]

Be sure to run this on a copy of the original. Also, if it works, test it
rigorously before using on real data !!! I don't regard myself as an expert
and have only a vague picture of the worksheet and it sounds large and
important.

It is assumed that the worksheet name is "Parts List". Change to suit. Also
assumed is that the headers are in row 1. The code will therefore skip row 1.
If this is not correct then it will require adaption. Here's the suggested
code. Minimal testing:

Sub ChangeCompLines()
Dim r As Range, c As Range
Dim ws As Worksheet
Dim i As Long

Set ws = Sheets("Parts List") 'Change to suit
Set r = ws.Columns("A").SpecialCells(xlCellTypeConstants)
i = 1
For Each c In r.Cells
Do
i = i + 1
If c.Row = 1 Or Len(c(i, 2)) = 0 Then Exit Do
If Len(c(i, 1)) = 0 Then
c(i, 2) = c(i, 2) * c(1, 2).Value
c(i, 5) = c(1, 5)
End If
Loop Until Len(c(i, 1)) > 0
i = 1
Next
End Sub

Regards,
Greg

 

[Ken Johnson]

Hi Scott,
backup you data then try this macro...

Public Sub ScottsChanges()
Application.ScreenUpdating = False
Dim iMasterQuantity As Integer
Dim strMark As String
Dim iLastRow As Long
Dim iRowCounter As Long
iLastRow = Cells(Range("B:B").Rows.Count, 2) _
..End(xlUp).Row
For iRowCounter = 2 To iLastRow
If Cells(iRowCounter, 1) <> "" Then
Let iMasterQuantity = Cells(iRowCounter, 2).Value
Let strMark = Cells(iRowCounter, 5).Value
Else: Let Cells(iRowCounter, 2).Value = _
Cells(iRowCounter, 2).Value * iMasterQuantity
Let Cells(iRowCounter, 5) = strMark
End If
Next
End Sub

Ken Johnson

 

[Scott Wagner]

Not sure if my last post took... so I apologize if this is a duplicate.

Thanks for the quick response Ken.

I am getting an error on this line when I paste the code:

iLastRow = Cells(Range("B:B").Rows.Count, 2) _
...End(xlUp).Row

Any ideas?

 

[Glen Mettler]

I don't think you need a macro. You can do it with in-cell formulas and it
will be faster.
I would put the items on 2 different sheets. Master on one and Components
on another and use vlookup
If it MUST be on a single sheet, you could add a column for the Master and
Component codes and still do an in-cell formula to populate the cells based
on the Master and Component Codes (not particularly complicated, but
difficult to explain here.) I have an application that does something very
similar. I will share it if you are interested.

Glen

 

[Tom Ogilvy]

the extra period is put in by the email software. There should only be one
period

iLastRow = Cells(Range( _
"B:B").Rows.Count, 2).End(xlUp).Row

Sure you haven't asked for help on this situation before?

 

[Scott Wagner]

Thanks!