| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code Improvement
Hi
I have the ffg cade to enter data to a sheet. It works fine but how can i improve it as to shorten it....... Sub ActualAdd() ActiveCell.FormulaR1C1 = frmMain.txt2.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt3.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt4.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt5.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt6.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt7.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt8.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt9.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt10.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt11.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt12.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt13.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt14.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt15.Text Range("A3").Select Selection.CurrentRegion.Select Selection.Name = "CustomerInfo" Selection.HorizontalAlignment = xlLeft Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub Thanx |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Improvement
Zaahir,
here's one suggestion. Sub ActualAdd() Dim c As Range Dim i As Integer Set c = ActiveCell For i = 2 To 15 c.FormulaR1C1 = frmMain.Controls("txt" & i).Text Set c = c.Offset(0, 1) Next i With Range("A3").CurrentRegion .Name = "CustomerInfo" .HorizontalAlignment = xlLeft .Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End With End Sub -- Hope that helps. Vergel Adriano "Zaahir" wrote: Hi I have the ffg cade to enter data to a sheet. It works fine but how can i improve it as to shorten it....... Sub ActualAdd() ActiveCell.FormulaR1C1 = frmMain.txt2.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt3.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt4.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt5.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt6.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt7.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt8.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt9.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt10.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt11.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt12.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt13.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt14.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt15.Text Range("A3").Select Selection.CurrentRegion.Select Selection.Name = "CustomerInfo" Selection.HorizontalAlignment = xlLeft Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub Thanx |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code Improvement
wow...excellent.
thanx stax Vergel "Vergel Adriano" wrote: Zaahir, here's one suggestion. Sub ActualAdd() Dim c As Range Dim i As Integer Set c = ActiveCell For i = 2 To 15 c.FormulaR1C1 = frmMain.Controls("txt" & i).Text Set c = c.Offset(0, 1) Next i With Range("A3").CurrentRegion .Name = "CustomerInfo" .HorizontalAlignment = xlLeft .Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End With End Sub -- Hope that helps. Vergel Adriano "Zaahir" wrote: Hi I have the ffg cade to enter data to a sheet. It works fine but how can i improve it as to shorten it....... Sub ActualAdd() ActiveCell.FormulaR1C1 = frmMain.txt2.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt3.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt4.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt5.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt6.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt7.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt8.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt9.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt10.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt11.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt12.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt13.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt14.Text ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 = frmMain.txt15.Text Range("A3").Select Selection.CurrentRegion.Select Selection.Name = "CustomerInfo" Selection.HorizontalAlignment = xlLeft Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub Thanx |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Further improvement to this IP address sorting code. | Excel Discussion (Misc queries) | |||
| RowSource for Combobox Improvement | Excel Programming | |||
| Improvement for office | Excel Discussion (Misc queries) | |||
| HLOOKUP improvement? | Excel Worksheet Functions | |||
Hybrid Mode
