Macro to move down a list until a new item is found?
Posted by Claudette on May 21, 2001 8:08 AM
I have a list that looks like this
Item Name Info Value
A Weight 21
A Size 12
A Blah 13G
<empty row>
B Weight 14
B Size 24
B Blah 3
C Weight 123
C Size 87
<empty row>
D Weight 143
D Size 7
D Form Fiber
D Blah 90
I need to go through the entire list and add a row at the end of each item's listings. Then I need to change the first column value of that new row into the Item name. Finally, I have to go to the second column in that row and input hte formula =concatenate("L" + the part's name)
So at the end, it would look like this:
Item Name Info Value
A Weight 21
A Size 12
A Blah 13G
A LA
<empty row>
B Weight 14
B Size 24
B Blah 3
B LB
C Weight 123
C Size 87
C LC
<empty row>
D Weight 143
D Size 7
D Form Fiber
D Blah 90
D LD
Note that each part has a different number of items in its list, so I can't just tell the macro to go down a specific number of lines and insert a row. Also, the "INFO" categories are different for each part so I can't just search for a signal in the second column to tell the macro that it is the last item in the list. And finally, some parts have an empty row after them, and some don't!! So even an empty row isn't a signal!
Pleeease help! Help on all or part of this would be greatly appreciated.
Posted by mseyf on May 21, 2001 11:57 AM
Claudette-
I'm far from a VBA whiz, and the following code is a little crude, but it may get you started:
Sub TestMac()
Dim TestCell As String
Range("a2").Select 'enter range of beginning cell
TestCell = CStr(ActiveCell.Value)
Do Until TestCell = "end" 'enter end after the last item name
If TestCell <> CStr(ActiveCell.Value) Then
If IsEmpty(ActiveCell) Then
ActiveCell = ActiveCell.Offset(-1, 0)
ActiveCell.Offset(0, 1).Formula = "=(" & Chr(34) & "L" & Chr(34) & Chr(38) & ActiveCell.Address & ")"
ActiveCell.Offset(1, 0).EntireRow.Insert
ActiveCell.Offset(2, 0).Select
TestCell = CStr(ActiveCell.Value)
Else
Range(ActiveCell, ActiveCell.Offset(1, 0)).EntireRow.Insert
ActiveCell = ActiveCell.Offset(-1, 0)
ActiveCell.Offset(0, 1).Formula = "=(" & Chr(34) & "L" & Chr(34) & Chr(38) & ActiveCell.Address & ")"
ActiveCell.Offset(2, 0).Select
TestCell = CStr(ActiveCell.Value)
End If
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub
be sure to change the line 'Range("a2").Select' to reflect the beginning range of the data, and enter the word 'end' in the cell at the very end of the data (in the item name column)
Hopefully, someone can provide a more elegant answer.
HTH
Mark
Posted by Claudette on May 21, 2001 2:01 PM
Thanks Mark! Good beginners book?
That was awesome Mark, thanks.
Do you have any suggestions as to books that will teach you how to program vbs for excel? A good beginners book?
Thanks again, you saved me!
Claudette
Posted by Dave Hawley on May 21, 2001 11:30 PM
Hi Claudette
This code should do the trick.
Sub InsertRows()
'Written by OzGrid Business Applications
'www.ozgrid.com
Dim rRange As Range
Dim i As Integer
Columns(2).EntireColumn.Insert
Set rRange = Range("A3", Range("A65536").End(xlUp)).Offset(0, 1)
rRange = "=IF(LEFT(R[-1]C[-1],1)<>LEFT(RC[-1],1)" _
& ",""L""&LEFT(R[-1]C[-1],1),0)"
Set rRange = rRange.SpecialCells(xlCellTypeFormulas, xlTextValues)
For i = rRange.Areas.Count To 1 Step -1
For ii = rRange.Areas(i).Cells.Count To 1 Step -1
rRange.Areas(i).Rows(ii).EntireRow.Insert
Next ii
Next i
Set rRange = Range("A3", Range("A65536").End(xlUp)).SpecialCells(xlCellTypeBlanks)
rRange.FormulaR1C1 = "=R[-1]C"
rRange.Offset(0, 2).FormulaR1C1 = "=""L""&RC[-2]"
Columns(1) = Columns(1).Value
Columns(3) = Columns(3).Value
Columns(2).Delete
Set rRange = Nothing
End Sub
Dave
OzGrid Business Applications
Posted by Dave Hawley on May 22, 2001 12:06 AM
Re: Thanks Mark! Good beginners book?
Do you have any suggestions as to books that will teach you how to program vbs for excel? A good beginners book? Thanks again, you saved me!
Claudette
My company OzGrid Business Applications specializes in remote VBA for Excel training. There are some details on my Website under "Training". All courses are self paced.
Dave
OzGrid Business Applications
Posted by mseyf on May 22, 2001 5:36 AM
Re: Thanks Mark! Good beginners book?
Do you have any suggestions as to books that will teach you how to program vbs for excel? A good beginners book? Thanks again, you saved me!
I like John Walkenbach's books. 'Excel 2000 Programming for Dummies' is an excellent beginners guide, and 'Excel 2000 Power Programming with VBA' is a great next step.
Mark
