ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Counter for inserting sequence of numbers in Column C (https://www.excelbanter.com/excel-programming/320622-counter-inserting-sequence-numbers-column-c.html)

mike
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

Tom Ogilvy
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

mike
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


.


All times are GMT +1. The time now is 05:23 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com