ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Code Improvement (https://www.excelbanter.com/excel-programming/388532-code-improvement.html)

Zaahir
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

Vergel Adriano
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

Zaahir
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


All times are GMT +1. The time now is 12:04 PM.

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