| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counter for inserting sequence of numbers in Column C
Thank you in advance for any help.
I am working with an excel spreadsheet that has the following data: ColumnA ColumnB ColumnC H 555555 01/12/2005 L 555555 D 555555 D 555555 D 555555 D 555555 D 555555 L 555555 D 555555 D 555555 L 555555 D 555555 D 555555 L 555555 D 555555 So far I have written the following macro: Range(Selection, Selection.End(xlDown)).Select Dim L For L = 1 To 250 Selection.Find(What:="L", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate On Error Resume Next Do Until ActiveCell = "D" ActiveCell.Offset(0, 2).Select ActiveCell = "1" ActiveCell.Offset(1, -2).Select Range(Selection, Selection.End(xlDown)).Select Loop Next L This macro produces the following which I will refer to as Example A ColumnA ColumnB ColumnC H 555555 01/12/2005 L 555555 1 D 555555 D 555555 D 555555 D 555555 D 555555 L 555555 1 D 555555 D 555555 L 555555 1 D 555555 D 555555 L 555555 1 D 555555 I want the end result to look like the example B below. I can't use Offsets because the number of Ds after each L changes. Example B ColumnA ColumnB ColumnC H 555555 01/12/2005 L 555555 1 D 555555 1 D 555555 1 D 555555 1 D 555555 1 D 555555 1 L 555555 2 D 555555 2 D 555555 2 L 555555 3 D 555555 3 D 555555 3 L 555555 4 D 555555 4 Does anyone know how I could alter my code to produce Example B instead of Example A? Thanks again for your help. I am using Excel 2000. Mike |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Counter for inserting sequence of numbers in Column C
Dim rng as Range
Dim rng1 as Range Dim rng2 as Range set rng1 = Nothing Range(Selection, Selection.End(xlDown)).Select Dim L For L = 1 To 250 Selection.Find(What:="L", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate if rng is nothing then _ set rng = ActiveCell On Error Resume Next Do Until ActiveCell = "D" ActiveCell.Offset(0, 2).Select ActiveCell = L ActiveCell.Offset(1, -2).Select Range(Selection, Selection.End(xlDown)).Select Loop Next L set rng = Range(rng, cells(rows.count,rng.column).End(xlup)) set rng = rng.offset(0,2) set rng 2 = rng set rng1 = rng(1) set rng = rng.specialCells(xlBlanks) rng.Formula = "=" & rng1.Address(0,0) rng2.formula = rng2.Value Code is untested and may contain typos. -- Regards, Tom Ogilvy "Mike" wrote in message ... Thank you in advance for any help. I am working with an excel spreadsheet that has the following data: ColumnA ColumnB ColumnC H 555555 01/12/2005 L 555555 D 555555 D 555555 D 555555 D 555555 D 555555 L 555555 D 555555 D 555555 L 555555 D 555555 D 555555 L 555555 D 555555 So far I have written the following macro: Range(Selection, Selection.End(xlDown)).Select Dim L For L = 1 To 250 Selection.Find(What:="L", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate On Error Resume Next Do Until ActiveCell = "D" ActiveCell.Offset(0, 2).Select ActiveCell = "1" ActiveCell.Offset(1, -2).Select Range(Selection, Selection.End(xlDown)).Select Loop Next L This macro produces the following which I will refer to as Example A ColumnA ColumnB ColumnC H 555555 01/12/2005 L 555555 1 D 555555 D 555555 D 555555 D 555555 D 555555 L 555555 1 D 555555 D 555555 L 555555 1 D 555555 D 555555 L 555555 1 D 555555 I want the end result to look like the example B below. I can't use Offsets because the number of Ds after each L changes. Example B ColumnA ColumnB ColumnC H 555555 01/12/2005 L 555555 1 D 555555 1 D 555555 1 D 555555 1 D 555555 1 D 555555 1 L 555555 2 D 555555 2 D 555555 2 L 555555 3 D 555555 3 D 555555 3 L 555555 4 D 555555 4 Does anyone know how I could alter my code to produce Example B instead of Example A? Thanks again for your help. I am using Excel 2000. Mike |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Counter for inserting sequence of numbers in Column C
Tom -
That code works perfectly. Thanks again for your help. Mike -----Original Message----- Dim rng as Range Dim rng1 as Range Dim rng2 as Range set rng1 = Nothing Range(Selection, Selection.End(xlDown)).Select Dim L For L = 1 To 250 Selection.Find(What:="L", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate if rng is nothing then _ set rng = ActiveCell On Error Resume Next Do Until ActiveCell = "D" ActiveCell.Offset(0, 2).Select ActiveCell = L ActiveCell.Offset(1, -2).Select Range(Selection, Selection.End(xlDown)).Select Loop Next L set rng = Range(rng, cells(rows.count,rng.column).End (xlup)) set rng = rng.offset(0,2) set rng 2 = rng set rng1 = rng(1) set rng = rng.specialCells(xlBlanks) rng.Formula = "=" & rng1.Address(0,0) rng2.formula = rng2.Value Code is untested and may contain typos. -- Regards, Tom Ogilvy "Mike" wrote in message ... Thank you in advance for any help. I am working with an excel spreadsheet that has the following data: ColumnA ColumnB ColumnC H 555555 01/12/2005 L 555555 D 555555 D 555555 D 555555 D 555555 D 555555 L 555555 D 555555 D 555555 L 555555 D 555555 D 555555 L 555555 D 555555 So far I have written the following macro: Range(Selection, Selection.End(xlDown)).Select Dim L For L = 1 To 250 Selection.Find(What:="L", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate On Error Resume Next Do Until ActiveCell = "D" ActiveCell.Offset(0, 2).Select ActiveCell = "1" ActiveCell.Offset(1, -2).Select Range(Selection, Selection.End(xlDown)).Select Loop Next L This macro produces the following which I will refer to as Example A ColumnA ColumnB ColumnC H 555555 01/12/2005 L 555555 1 D 555555 D 555555 D 555555 D 555555 D 555555 L 555555 1 D 555555 D 555555 L 555555 1 D 555555 D 555555 L 555555 1 D 555555 I want the end result to look like the example B below. I can't use Offsets because the number of Ds after each L changes. Example B ColumnA ColumnB ColumnC H 555555 01/12/2005 L 555555 1 D 555555 1 D 555555 1 D 555555 1 D 555555 1 D 555555 1 L 555555 2 D 555555 2 D 555555 2 L 555555 3 D 555555 3 D 555555 3 L 555555 4 D 555555 4 Does anyone know how I could alter my code to produce Example B instead of Example A? Thanks again for your help. I am using Excel 2000. Mike . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adding Counter to column for number of identical file numbers | Excel Discussion (Misc queries) | |||
| Macro help inserting records by date sequence. | Excel Discussion (Misc queries) | |||
| inserting a dash into a column of numbers | Excel Worksheet Functions | |||
| Inserting and Tracking Missing Sequence | Excel Discussion (Misc queries) | |||
| Repeating a sequence of numbers down a column | Excel Discussion (Misc queries) | |||
Hybrid Mode
