Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Question Multiply vs' replace
I got some help on this topic in an earlier post, but it is more complicated
that I had expected at first. Don Guillett was able to help, hoping to get that last piece of the puzzle. Here is a link to the previous post: http://www.microsoft.com/office/comm...27b&sloc=en-us In the previous post I needed to replace infomation including quantities, the complcation is that sometimes the components occur more than just quantity of 1. The master line should multiply with the componet line in these cases. Below is an example (trim line is the multiple example). What I have now: ColA ColB ColC ColD 1 Starter Open Type 3 XFMR Dry Type AAA 4 Panelboard Type B 1 Box 2 Trim BBB 2 Panelboard Type Q 1 Box 2 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 8 Trim BBB 2 Panelboard Type Q BBB 2 Box BBB 4 Trim BBB 2 Ground Bar The code Don provided me with is as follows, if that helps. Thanks in advance. 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Question Multiply vs' replace
Sub fillinblanksN()
qty = 1 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(0, -2) * qty else qty = c.offset(0,-2) End If Next End Sub -- Regards, Tom Ogilvy "Scott Wagner" wrote in message ... I got some help on this topic in an earlier post, but it is more complicated that I had expected at first. Don Guillett was able to help, hoping to get that last piece of the puzzle. Here is a link to the previous post: http://www.microsoft.com/office/comm...27b&sloc=en-us In the previous post I needed to replace infomation including quantities, the complcation is that sometimes the components occur more than just quantity of 1. The master line should multiply with the componet line in these cases. Below is an example (trim line is the multiple example). What I have now: ColA ColB ColC ColD 1 Starter Open Type 3 XFMR Dry Type AAA 4 Panelboard Type B 1 Box 2 Trim BBB 2 Panelboard Type Q 1 Box 2 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 8 Trim BBB 2 Panelboard Type Q BBB 2 Box BBB 4 Trim BBB 2 Ground Bar The code Don provided me with is as follows, if that helps. Thanks in advance. 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Question Multiply vs' replace
Tom, as always... YOU ARE A STUD!!!
Thanks so much! "Tom Ogilvy" wrote: Sub fillinblanksN() qty = 1 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(0, -2) * qty else qty = c.offset(0,-2) End If Next End Sub -- Regards, Tom Ogilvy "Scott Wagner" wrote in message ... I got some help on this topic in an earlier post, but it is more complicated that I had expected at first. Don Guillett was able to help, hoping to get that last piece of the puzzle. Here is a link to the previous post: http://www.microsoft.com/office/comm...27b&sloc=en-us In the previous post I needed to replace infomation including quantities, the complcation is that sometimes the components occur more than just quantity of 1. The master line should multiply with the componet line in these cases. Below is an example (trim line is the multiple example). What I have now: ColA ColB ColC ColD 1 Starter Open Type 3 XFMR Dry Type AAA 4 Panelboard Type B 1 Box 2 Trim BBB 2 Panelboard Type Q 1 Box 2 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 8 Trim BBB 2 Panelboard Type Q BBB 2 Box BBB 4 Trim BBB 2 Ground Bar The code Don provided me with is as follows, if that helps. Thanks in advance. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
< question for multiply cells. | Excel Worksheet Functions | |||
add value of 4 cells, multiply by 3 subtract 72 multiply by 80% | Excel Discussion (Misc queries) | |||
replace question | Excel Discussion (Misc queries) | |||
Another replace question | Excel Discussion (Misc queries) | |||
Question about "replace" | Excel Worksheet Functions |