Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Trouble with Numbering Sequence
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Trouble with Numbering Sequence
W is a string.
You're trying to put an integer (Int()) in that variable. Jim S wrote: 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 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Trouble with Numbering Sequence
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. "Dave Peterson" wrote: W is a string. You're trying to put an integer (Int()) in that variable. Jim S wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Trouble with Numbering Sequence
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. Jim S wrote: 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. "Dave Peterson" wrote: W is a string. You're trying to put an integer (Int()) in that variable. Jim S wrote: 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 -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Trouble with Numbering Sequence
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. "Dave Peterson" wrote: 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. Jim S wrote: 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. "Dave Peterson" wrote: W is a string. You're trying to put an integer (Int()) in that variable. Jim S wrote: 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 -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Trouble with Numbering Sequence
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 S wrote: 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. "Dave Peterson" wrote: 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. Jim S wrote: 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. "Dave Peterson" wrote: W is a string. You're trying to put an integer (Int()) in that variable. Jim S wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Trouble with Numbering Sequence
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? "Dave Peterson" wrote: 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 S wrote: 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. "Dave Peterson" wrote: 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. Jim S wrote: 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. "Dave Peterson" wrote: W is a string. You're trying to put an integer (Int()) in that variable. Jim S wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Trouble with Numbering Sequence
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 S wrote: 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? "Dave Peterson" wrote: 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 S wrote: 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. "Dave Peterson" wrote: 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. Jim S wrote: 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. "Dave Peterson" wrote: W is a string. You're trying to put an integer (Int()) in that variable. Jim S wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Trouble with Numbering Sequence
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. "Dave Peterson" wrote: 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 S wrote: 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? "Dave Peterson" wrote: 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 S wrote: 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. "Dave Peterson" wrote: 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. Jim S wrote: 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. "Dave Peterson" wrote: W is a string. You're trying to put an integer (Int()) in that variable. Jim S wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Numbering Sequence | Excel Discussion (Misc queries) | |||
re-numbering or sequence priorities formula | Excel Discussion (Misc queries) | |||
Macro to create sequence of numbers | Excel Programming | |||
How to link diffrent documents with a sequence of page numbering? | Excel Discussion (Misc queries) | |||
sequence of page numbering from diffrent documents in to a footer | Excel Discussion (Misc queries) |