Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Question - Applying values
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Question - Applying values
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 "Scott Wagner" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Question - Applying values
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Question - Applying values
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Question - Applying values
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 "Scott Wagner" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Question - Applying values
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? -- Regards, Tom Ogilvy "Scott Wagner" wrote in message ... 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Question - Applying values
Thanks!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multiple values question | Excel Discussion (Misc queries) | |||
Combo Box initial values question | Excel Discussion (Misc queries) | |||
Sum Values Question | Excel Worksheet Functions | |||
Question about range values with Set = | Excel Programming | |||
Another Question Concerning Replacing Formulae With Values ! | Excel Programming |