Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Macro Question
Hello, I have created a movie database of movies I own, column A is the movie
number, while column b is the movie title. I have set up a macro to first sort the data by movie title, and then re-number (fill) the A column with the appropriate number. Problem is, I'm not very good with visual basic, and I think if I use an 'if' statement somewhere (i'm good with php hence the logic) it will only fill in column A (the number) if column B (the movie) is present, or has a title enered. Here is the VB code for the Macro, I have it set right now to stop at 316, because this is where page 7 would break, but I'm sure there is a way to do it so I won't have to keep updating this macro, I just need one of you pros to tell me how. My current vb code: Sub alphnumbers() ' ' alphnumbers Macro ' Macro recorded 5/24/2005 by Jason ' ' Keyboard Shortcut: Ctrl+s ' Columns("B:B").Select Range("A1:E65526").Sort Key1:=Range("B2"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("A2:A4").Select Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _ Step:=1, Trend:=False Selection.AutoFill Destination:=Range("A2:A316"), Type:=xlFillDefault Range("A2:A316").Select ActiveWindow.SmallScroll Down:=-264 Range("A2").Select End Sub As you can probably see, I did this via the record function, as I am new to this, but if there is a more efficient way please let me know. Right now I only have about 300 movies, so I am getting 15 lines with 301-315 listed without titles. 8( |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Macro Question
Bob, that was exactly what I was looking for, thank you so much! 8)
"Bob Phillips" wrote: Is this what you mean? Sub alphnumbers() ' ' alphnumbers Macro ' Macro recorded 5/24/2005 by Jason ' ' Keyboard Shortcut: Ctrl+s ' Dim iLastRow As Long iLastRow = Cells(Rows.Count, "B").End(xlUp).Row Range("A1:E" & iLastRow).Sort Key1:=Range("B2"), _ Order1:=xlAscending, _ Header:=xlYes, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("A2").Value = 1 iLastRow = Cells(Rows.Count, "B").End(xlUp).Row With Range("A2:A" & iLastRow) .DataSeries Rowcol:=xlColumns, _ Type:=xlLinear, _ Date:=xlDay, _ Step:=1, _ Trend:=False End With End Sub -- HTH Bob Phillips "Jason R" <Jason wrote in message ... Hello, I have created a movie database of movies I own, column A is the movie number, while column b is the movie title. I have set up a macro to first sort the data by movie title, and then re-number (fill) the A column with the appropriate number. Problem is, I'm not very good with visual basic, and I think if I use an 'if' statement somewhere (i'm good with php hence the logic) it will only fill in column A (the number) if column B (the movie) is present, or has a title enered. Here is the VB code for the Macro, I have it set right now to stop at 316, because this is where page 7 would break, but I'm sure there is a way to do it so I won't have to keep updating this macro, I just need one of you pros to tell me how. My current vb code: Sub alphnumbers() ' ' alphnumbers Macro ' Macro recorded 5/24/2005 by Jason ' ' Keyboard Shortcut: Ctrl+s ' Columns("B:B").Select Range("A1:E65526").Sort Key1:=Range("B2"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("A2:A4").Select Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _ Step:=1, Trend:=False Selection.AutoFill Destination:=Range("A2:A316"), Type:=xlFillDefault Range("A2:A316").Select ActiveWindow.SmallScroll Down:=-264 Range("A2").Select End Sub As you can probably see, I did this via the record function, as I am new to this, but if there is a more efficient way please let me know. Right now I only have about 300 movies, so I am getting 15 lines with 301-315 listed without titles. 8( |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Macro Question
Great, I goy confused by the Autofill in your code, so I just got rid of it
:-) Bob "Jason R" <Jason wrote in message ... Bob, that was exactly what I was looking for, thank you so much! 8) "Bob Phillips" wrote: Is this what you mean? Sub alphnumbers() ' ' alphnumbers Macro ' Macro recorded 5/24/2005 by Jason ' ' Keyboard Shortcut: Ctrl+s ' Dim iLastRow As Long iLastRow = Cells(Rows.Count, "B").End(xlUp).Row Range("A1:E" & iLastRow).Sort Key1:=Range("B2"), _ Order1:=xlAscending, _ Header:=xlYes, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("A2").Value = 1 iLastRow = Cells(Rows.Count, "B").End(xlUp).Row With Range("A2:A" & iLastRow) .DataSeries Rowcol:=xlColumns, _ Type:=xlLinear, _ Date:=xlDay, _ Step:=1, _ Trend:=False End With End Sub -- HTH Bob Phillips "Jason R" <Jason wrote in message ... Hello, I have created a movie database of movies I own, column A is the movie number, while column b is the movie title. I have set up a macro to first sort the data by movie title, and then re-number (fill) the A column with the appropriate number. Problem is, I'm not very good with visual basic, and I think if I use an 'if' statement somewhere (i'm good with php hence the logic) it will only fill in column A (the number) if column B (the movie) is present, or has a title enered. Here is the VB code for the Macro, I have it set right now to stop at 316, because this is where page 7 would break, but I'm sure there is a way to do it so I won't have to keep updating this macro, I just need one of you pros to tell me how. My current vb code: Sub alphnumbers() ' ' alphnumbers Macro ' Macro recorded 5/24/2005 by Jason ' ' Keyboard Shortcut: Ctrl+s ' Columns("B:B").Select Range("A1:E65526").Sort Key1:=Range("B2"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("A2:A4").Select Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _ Step:=1, Trend:=False Selection.AutoFill Destination:=Range("A2:A316"), Type:=xlFillDefault Range("A2:A316").Select ActiveWindow.SmallScroll Down:=-264 Range("A2").Select End Sub As you can probably see, I did this via the record function, as I am new to this, but if there is a more efficient way please let me know. Right now I only have about 300 movies, so I am getting 15 lines with 301-315 listed without titles. 8( |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple Macro Question
If I knew how to do it without the fill code in there I would have, but your
code is flawless for what I am doing, thanks again! "Bob Phillips" wrote: Great, I goy confused by the Autofill in your code, so I just got rid of it :-) Bob "Jason R" <Jason wrote in message ... Bob, that was exactly what I was looking for, thank you so much! 8) "Bob Phillips" wrote: Is this what you mean? Sub alphnumbers() ' ' alphnumbers Macro ' Macro recorded 5/24/2005 by Jason ' ' Keyboard Shortcut: Ctrl+s ' Dim iLastRow As Long iLastRow = Cells(Rows.Count, "B").End(xlUp).Row Range("A1:E" & iLastRow).Sort Key1:=Range("B2"), _ Order1:=xlAscending, _ Header:=xlYes, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("A2").Value = 1 iLastRow = Cells(Rows.Count, "B").End(xlUp).Row With Range("A2:A" & iLastRow) .DataSeries Rowcol:=xlColumns, _ Type:=xlLinear, _ Date:=xlDay, _ Step:=1, _ Trend:=False End With End Sub -- HTH Bob Phillips "Jason R" <Jason wrote in message ... Hello, I have created a movie database of movies I own, column A is the movie number, while column b is the movie title. I have set up a macro to first sort the data by movie title, and then re-number (fill) the A column with the appropriate number. Problem is, I'm not very good with visual basic, and I think if I use an 'if' statement somewhere (i'm good with php hence the logic) it will only fill in column A (the number) if column B (the movie) is present, or has a title enered. Here is the VB code for the Macro, I have it set right now to stop at 316, because this is where page 7 would break, but I'm sure there is a way to do it so I won't have to keep updating this macro, I just need one of you pros to tell me how. My current vb code: Sub alphnumbers() ' ' alphnumbers Macro ' Macro recorded 5/24/2005 by Jason ' ' Keyboard Shortcut: Ctrl+s ' Columns("B:B").Select Range("A1:E65526").Sort Key1:=Range("B2"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("A2:A4").Select Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _ Step:=1, Trend:=False Selection.AutoFill Destination:=Range("A2:A316"), Type:=xlFillDefault Range("A2:A316").Select ActiveWindow.SmallScroll Down:=-264 Range("A2").Select End Sub As you can probably see, I did this via the record function, as I am new to this, but if there is a more efficient way please let me know. Right now I only have about 300 movies, so I am getting 15 lines with 301-315 listed without titles. 8( |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple Macro Question | Excel Discussion (Misc queries) | |||
simple macro question | Excel Worksheet Functions | |||
question about syntax in a simple macro... | Excel Discussion (Misc queries) | |||
Macro question - I'm sure it's simple... | Excel Discussion (Misc queries) | |||
Simple question - Named Range in Macro | Excel Programming |