VBA Question - Applying values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Scott Wagner - 17 Mar 2006 23:23 GMT

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

Reply to this Message

Greg Wilson - 18 Mar 2006 06:43 GMT

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

> 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.
[quoted text clipped - 38 lines]
>
> Scott

Reply to this Message

Ken Johnson - 18 Mar 2006 06:57 GMT

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

Reply to this Message

Scott Wagner - 18 Mar 2006 15:43 GMT

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?

Reply to this Message

Tom Ogilvy - 18 Mar 2006 17:11 GMT

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?

Signature

Regards,
Tom Ogilvy

> Not sure if my last post took... so I apologize if this is a duplicate.
>
[quoted text clipped - 6 lines]
>
> Any ideas?

Reply to this Message

Scott Wagner - 20 Mar 2006 15:59 GMT

Thanks!

Reply to this Message

Glen Mettler - 18 Mar 2006 15:55 GMT

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

> I'm hoping to get some help with something that feels complex to me, that
> may
[quoted text clipped - 42 lines]
>
> Scott

Reply to this Message