Macro Trouble with Numbering Sequence

  • Thread starter Thread starter Jim S
  • Start date Start date
J

Jim S

I used the following Macro in Excel 2003:

Sub FillDownWithDecimals()
Dim X As Long, W As String, F As String, S As String
S = Selection(1).Text
W = Int(S) 'Left(S, InStr(S & ".", ".") - 1)
F = Mid(S, InStr(S & ".0", ".") + 1)
For X = 0 To Selection.Count - 1
With Selection(1).Offset(X)
..NumberFormat = "@"
..HorizontalAlignment = xlRight
..Value = W & "." & F
End With
F = CStr(Val(F + 1))
Next
End Sub

I get a debug error on this line:

W = Int(S) 'Left(S, InStr(S & ".", ".") - 1)

Can't execute code in break mode
 
Hi Dave,

I recieved this as a Macro from your team as I was trying to fill numbers
down a column A associated with the items I had in column B. I tried to run
this Macro and got the error. Maybe I don't know how to put the Macro in or I
don't know what I need to do to get the Macro to work. As I don't know the
difference between the string and interger.
 
First, I don't have a team.

Second,
Integers are whole numbers: ..., -3, -2, -1, 0, 1, 2, 3, ....
Strings are text: "This is Text", "This is more Text", "A"

Third, I'm not sure what you're trying to do but maybe:

W = CStr(Int(S))

But you haven't shared (in this thread at least) what's in the top cell of the
selection and what you want to do with the rest of the cells in that selection.
 
column A
Header
2.1
2.2
2.3
2.4
2.5
etc.

I would like to use Column A and start with a decimal number such as:
A1 = 2.1
A2 = 2.2
select A1:A2 and drag which will go upto 2.9 or 2.109 or as far as i would
like and also be able to add then 3.0, 3.1, etc.
I have used this:
in A1 type formula and copy that down...
="2." & ROW()

I works as long as I start in column A and Row 1 but going to any other row
it starts with that row number instead of starting with 1.0 or 2.0 or what
ever number I choose.

I would like it to be able to auto file as well if that is possible.

btw, i can get it to do the following:
A1= 2.1
A2= 2.2
A3= 2.3
A4= 2.4
A5= 2.5
A6= 2.6
A7= 2.7
A8= 2.8
A9= 2.9
A10= 2.10
A11= 2.11
A12= 2.12
etc, to what ever range i want before changing to
A199= 3.0 or higher.

Here's an example:

Column A Column B
Row1 2.1.5.1 3com-tsmux
Row2 2.1.5.2 acap
Row3 2.1.5.3 afbackup
Row4 2.1.5.4 afpovertcp
Row5 2.1.5.5 afs3-bos
Row6 2.1.5.6 afs3-callback
Row7 2.1.5.7 afs3-errors
Row 8 2.1.5.8 afs3-fileserver
Row9 2.1.5.9 afs3-kaserver
Row10 2.1.5.10 afs3-prserver
Row11 2.1.5.11 afs3-rmtsys


The macro solution was one that should have worked by simply put the
starting number in a cell, then select from that
cell to the last cell you want to put your serial number in, and finally run
the macro.

The macro was modified to let me change the numbering within a
selection...

Sub FillDownWithDecimals()
Dim X As Long, W As String, F As String, S As String
S = Selection(1).Text
W = Int(S) 'Left(S, InStr(S & ".", ".") - 1)
F = Mid(S, InStr(S & ".0", ".") + 1)
For X = 0 To Selection.Count - 1
With Selection(1).Offset(X)
If .Text <> "" Then
S = .Text
W = Int(S) 'Left(S, InStr(S & ".", ".") - 1)
F = Mid(S, InStr(S & ".0", ".") + 1)
End If
..NumberFormat = "@"
..HorizontalAlignment = xlRight
..Value = W & "." & F
End With
F = CStr(Val(F + 1))
Next
End Sub

To see what I mean, go to a blank column and in any cell in that column, put
2.0; then, in the same column, put 3.0 in a cell some number of rows below
the 2.0 cell; now, make a selection starting with the 2.0 cell and going
past the 3.0 cell... run the macro and the 2.# series will stop when it
reaches the 3.0 cell and from then on, the number will be 3.# until the end
of the selection. You can encompass as many new numbering starting points
(the 2.0 and 3.0 values, although you don't have to start with those number)
as you want and the code will adapt to them.

The problem is that it doesn't work for me.
Please help me to understand what is wrong with the Macro or the execution
of it.
 
I don't understand when you'd go from 2.xxx to 3.0.

Do you allow 2.9, 2.99, 2.999, or even 2.999999999999

And if I were using this, I'd want a nicer format so I could sort by this field.

I'd use
2.001
2.002
....
2.999
3.000
....

(depending on the number of digits you allow.)

Jim said:
column A
Header
2.1
2.2
2.3
2.4
2.5
etc.

I would like to use Column A and start with a decimal number such as:
A1 = 2.1
A2 = 2.2
select A1:A2 and drag which will go upto 2.9 or 2.109 or as far as i would
like and also be able to add then 3.0, 3.1, etc.
I have used this:
in A1 type formula and copy that down...
="2." & ROW()

I works as long as I start in column A and Row 1 but going to any other row
it starts with that row number instead of starting with 1.0 or 2.0 or what
ever number I choose.

I would like it to be able to auto file as well if that is possible.

btw, i can get it to do the following:
A1= 2.1
A2= 2.2
A3= 2.3
A4= 2.4
A5= 2.5
A6= 2.6
A7= 2.7
A8= 2.8
A9= 2.9
A10= 2.10
A11= 2.11
A12= 2.12
etc, to what ever range i want before changing to
A199= 3.0 or higher.

Here's an example:

Column A Column B
Row1 2.1.5.1 3com-tsmux
Row2 2.1.5.2 acap
Row3 2.1.5.3 afbackup
Row4 2.1.5.4 afpovertcp
Row5 2.1.5.5 afs3-bos
Row6 2.1.5.6 afs3-callback
Row7 2.1.5.7 afs3-errors
Row 8 2.1.5.8 afs3-fileserver
Row9 2.1.5.9 afs3-kaserver
Row10 2.1.5.10 afs3-prserver
Row11 2.1.5.11 afs3-rmtsys

The macro solution was one that should have worked by simply put the
starting number in a cell, then select from that
cell to the last cell you want to put your serial number in, and finally run
the macro.

The macro was modified to let me change the numbering within a
selection...

Sub FillDownWithDecimals()
Dim X As Long, W As String, F As String, S As String
S = Selection(1).Text
W = Int(S) 'Left(S, InStr(S & ".", ".") - 1)
F = Mid(S, InStr(S & ".0", ".") + 1)
For X = 0 To Selection.Count - 1
With Selection(1).Offset(X)
If .Text <> "" Then
S = .Text
W = Int(S) 'Left(S, InStr(S & ".", ".") - 1)
F = Mid(S, InStr(S & ".0", ".") + 1)
End If
.NumberFormat = "@"
.HorizontalAlignment = xlRight
.Value = W & "." & F
End With
F = CStr(Val(F + 1))
Next
End Sub

To see what I mean, go to a blank column and in any cell in that column, put
2.0; then, in the same column, put 3.0 in a cell some number of rows below
the 2.0 cell; now, make a selection starting with the 2.0 cell and going
past the 3.0 cell... run the macro and the 2.# series will stop when it
reaches the 3.0 cell and from then on, the number will be 3.# until the end
of the selection. You can encompass as many new numbering starting points
(the 2.0 and 3.0 values, although you don't have to start with those number)
as you want and the code will adapt to them.

The problem is that it doesn't work for me.
Please help me to understand what is wrong with the Macro or the execution
of it.
Click to expand...
 
I would like to have the choice of starting at 3.0 at any place on the spread
sheet in the same column when I decide to change the numbering.

I have several columns with data in it and I need to be able to insert
different numbering schemes for the various O/S environments that I am
working with. As an example:

Col A Col B Col C Col D Col E
Col F
Num AIX Num Linux Num
HPUX
Row1 2.1.5.1 3com-tsmux 2.1.1.1 3com-tsmux 2.1.3.1 3com-tsmux

Row2 2.1.5.2 acap 2.1.1.2 acap 2.1.3.2
acap
Row3 2.1.5.3 afbackup 2.1.1.3 afbackup 2.1.3.3

Row4 2.1.5.4 afpovertcp 2.1.1.4 afpovertcp
Row5 2.1.5.5 afs3-bos 2.1.1.5 afs3-bos
Row6 2.1.5.6 afs3-callback 2.1.1.6 afs3-callback
Row7 2.1.5.7 afs3-errors 2.1.1.7 afs3-errors
Row 8 2.1.5.8 afs3-fileserver 2.1.1.8 afs3-fileserver
Row9 2.1.5.9 afs3-kaserver 2.1.1.9 afs3-kaserver
Row10 2.1.5.10 afs3-prserver 2.1.1.10 afs3-prserver
Row11 2.1.5.11 afs3-rmtsys 2.1.1.11 afs3-rmtsys
Row12 blank>>>>>>>>>>>>>>>>>>>>
Row13 3.1.5.1 net.ipv4.tcp_max_syn_backlog = 4096
Row14 blank>>>>>>>>>>>>>>>>>>>>>>
Row15 4.1.5.1 authpriv.* /var/log/secure
etc.................

Does this make more sense?
 
It doesn't make more sense to me.

I don't see how the original question fits with these changes. I'm not sure if
you're looking for x.x.x.x and how those should be incremented.

Maybe you can just use excel's builtin autofill capability.

Type the first entry in the top cell.
Type the second entry in the second cell.
Select both cells
rightclick on the autofill button and drag down as far as you want.

When you release the mouse, you'll see a list of options. Maybe fill series is
what you want.



Jim said:
I would like to have the choice of starting at 3.0 at any place on the spread
sheet in the same column when I decide to change the numbering.

I have several columns with data in it and I need to be able to insert
different numbering schemes for the various O/S environments that I am
working with. As an example:

Col A Col B Col C Col D Col E
Col F
Num AIX Num Linux Num
HPUX
Row1 2.1.5.1 3com-tsmux 2.1.1.1 3com-tsmux 2.1.3.1 3com-tsmux

Row2 2.1.5.2 acap 2.1.1.2 acap 2.1.3.2
acap
Row3 2.1.5.3 afbackup 2.1.1.3 afbackup 2.1.3.3

Row4 2.1.5.4 afpovertcp 2.1.1.4 afpovertcp
Row5 2.1.5.5 afs3-bos 2.1.1.5 afs3-bos
Row6 2.1.5.6 afs3-callback 2.1.1.6 afs3-callback
Row7 2.1.5.7 afs3-errors 2.1.1.7 afs3-errors
Row 8 2.1.5.8 afs3-fileserver 2.1.1.8 afs3-fileserver
Row9 2.1.5.9 afs3-kaserver 2.1.1.9 afs3-kaserver
Row10 2.1.5.10 afs3-prserver 2.1.1.10 afs3-prserver
Row11 2.1.5.11 afs3-rmtsys 2.1.1.11 afs3-rmtsys
Row12 blank>>>>>>>>>>>>>>>>>>>>
Row13 3.1.5.1 net.ipv4.tcp_max_syn_backlog = 4096
Row14 blank>>>>>>>>>>>>>>>>>>>>>>
Row15 4.1.5.1 authpriv.* /var/log/secure
etc.................

Does this make more sense?
Click to expand...
 
Auto fill works fine except that it references the number on the row and
doesn't fit the need for what I am trying to do with the data on the right
for numbering at different rows starting over again with 2.1 or 3.1 as it
references the row number

2.13 instead of 2.1

and 3.21 instead of 3.1

etc.
 
Back
Top