Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Repeating cells
How do I repeat the stuff in column A when there's stuff in Col B?
My spreadsheet looks like this: A B 000771605-0 1976 0000000 000771607-2 1976 0000000 0284831 000800014-5 2009 0000000 0681378 0681864 0682617 000804444-2 1981 0000000 0284831 000804444-2 1983 0000000 0284831 000804448-6 1980 0000000 000804448-6 1982 0000000 0681804 000812407-4 2006 0000000 0681865 0682683 0682694 0682695 0682697 0684836 000812415-1 2003 0000000 0681865 0682683 0682694 0682695 0682697 0684836 ====================================== Here's what I need: A B 000771605-0 1976 0000000 000771607-2 1976 0000000 000771607-2 1976 0284831 000800014-5 2009 0000000 000800014-5 2009 0681378 000800014-5 2009 0681864 000800014-5 2009 0682617 000804444-2 1981 0000000 000804444-2 1981 0284831 000804444-2 1983 0000000 000804444-2 1983 0284831 000804448-6 1980 0000000 000804448-6 1982 0000000 000804448-6 1982 0681804 000812407-4 2006 0000000 000812407-4 2006 0681865 000812407-4 2006 0682683 000812407-4 2006 0682694 000812407-4 2006 0682695 000812407-4 2006 0682697 000812407-4 2006 0684836 000812415-1 2003 0000000 000812415-1 2003 0681865 000812415-1 2003 0682683 000812415-1 2003 0682694 000812415-1 2003 0682695 000812415-1 2003 0682697 000812415-1 2003 0684836 Thank you in advance, GaryWC |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Repeating cells
Hello Gary,
is this what you are looking for Sub adapt() Dim rng As Range Dim lLastRow As Integer Dim lLastCol As Integer Dim i As Integer 'Now we get the dimensions of the data Set rng = Worksheets("sheet2").Range("a1").SpecialCells(xlCe llTypeLastCell) lLastRow = rng.Row lLastCol = rng.Column 'Now for the loop For i = 2 To lLastRow 'find cell wit less than 7 positions in col a and copy to col b If Len(Cells(i, "a").Value) < 7 Then Range("A" & i).Select Application.CutCopyMode = False Selection.Copy Range("b" & i).Select ActiveSheet.Paste End If 'find cell wit less than 7 positions in col a 'when content col a = col b 'copy content col a one row less to actual row col a If Len(Cells(i, "a").Value) < 7 And Range("A" & i).Value = Range("b" & i).Value Then Range("A" & i - 1).Select Application.CutCopyMode = False Selection.Copy Range("a" & i).Select ActiveSheet.Paste End If Next i 'adapt format cells in col b Columns("B:B").Select Selection.NumberFormat = "0000000" End Sub "gary" schreef in bericht ... How do I repeat the stuff in column A when there's stuff in Col B? My spreadsheet looks like this: A B 000771605-0 1976 0000000 000771607-2 1976 0000000 0284831 000800014-5 2009 0000000 0681378 0681864 0682617 000804444-2 1981 0000000 0284831 000804444-2 1983 0000000 0284831 000804448-6 1980 0000000 000804448-6 1982 0000000 0681804 000812407-4 2006 0000000 0681865 0682683 0682694 0682695 0682697 0684836 000812415-1 2003 0000000 0681865 0682683 0682694 0682695 0682697 0684836 ====================================== Here's what I need: A B 000771605-0 1976 0000000 000771607-2 1976 0000000 000771607-2 1976 0284831 000800014-5 2009 0000000 000800014-5 2009 0681378 000800014-5 2009 0681864 000800014-5 2009 0682617 000804444-2 1981 0000000 000804444-2 1981 0284831 000804444-2 1983 0000000 000804444-2 1983 0284831 000804448-6 1980 0000000 000804448-6 1982 0000000 000804448-6 1982 0681804 000812407-4 2006 0000000 000812407-4 2006 0681865 000812407-4 2006 0682683 000812407-4 2006 0682694 000812407-4 2006 0682695 000812407-4 2006 0682697 000812407-4 2006 0684836 000812415-1 2003 0000000 000812415-1 2003 0681865 000812415-1 2003 0682683 000812415-1 2003 0682694 000812415-1 2003 0682695 000812415-1 2003 0682697 000812415-1 2003 0684836 Thank you in advance, GaryWC |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
increment cells in repeating formulas by something other than 1 | Excel Worksheet Functions | |||
Repeating cells at bottom | New Users to Excel | |||
repeating cells | Excel Discussion (Misc queries) | |||
Delete Repeating text in cells | Excel Discussion (Misc queries) | |||
Repeating Characters in Cells with info. | Excel Discussion (Misc queries) |