| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Streamlining Code
Hello Members,
I am relatively new with vba and excel and would appreciate how I could go about cleaning or rather streamlining this akward piece of code that follows below. What I would like to accomplish is the following: Loop through just the cells that contain data (i.e. row 1, col 1, row 2, col 2,etc.). In other words, my data set is set like this: ICSnumber ATnumber 4 1 2 65 222 3 53 4 75 5 81 6 If the number in row 1 col1 etc. satisfies the condition in the macro, then, that number is replaced (i.e. 4 1 becomes 1,1 2 65 becomes 2,2 etc.). The macro I have developed works however I like a cleaner piece of code. Would greatly appreciate some code example if not suggestions. Thanks. Sub ICS_to_AT21_first_country_row2() ' ' ' Macro recorded 14/10/98 by Camel ' ' change ICS codes to AT codes ' ' first country in Row 2 ' 'Worksheets.Select Range("a2").Select 'ActiveCell.FormulaR1C1 = "AT code" 'ActiveCell.Offset(1, 0).Activate Do Until IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(0, 1)) If ActiveCell.Offset(0, 0) = 1 Then ActiveCell.Offset(0, 0) = 178 ElseIf ActiveCell.Offset(0, 0) = 2 Then ActiveCell.Offset(0, 0) = 65 ElseIf ActiveCell.Offset(0, 0) = 3 Then ActiveCell.Offset(0, 0) = 119 ElseIf ActiveCell.Offset(0, 0) = 4 Then ActiveCell.Offset(0, 0) = 1 ElseIf ActiveCell.Offset(0, 0) = 7 Then ActiveCell.Offset(0, 0) = 18 ElseIf ActiveCell.Offset(0, 0) = 8 Then ActiveCell.Offset(0, 0) = 135 ElseIf ActiveCell.Offset(0, 0) = 9 Then ActiveCell.Offset(0, 0) = 50 ElseIf ActiveCell.Offset(0, 0) = 10 Then ActiveCell.Offset(0, 0) = 112 ElseIf ActiveCell.Offset(0, 0) = 11 Then ActiveCell.Offset(0, 0) = 101 ElseIf ActiveCell.Offset(0, 0) = 12 Then ActiveCell.Offset(0, 0) = 136 'Else: ActiveCell.Offset(0, 0) = "" End If ActiveCell.Offset(1, 0).Activate Loop End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Streamlining Code
Sub ICS_to_AT21_first_country_row2()
' ' ' Macro recorded 14/10/98 by Camel ' ' change ICS codes to AT codes ' ' first country in Row 2 ' Dim v1 as Variant, v2 as Variant Dim i as Long 'Worksheets.Select Range("a2").Select 'ActiveCell.FormulaR1C1 = "AT code" 'ActiveCell.Offset(1, 0).Activate v1 = Array(1,2,3,4,7,8,9,10,11,12) v2 = Array(178,65,119,1,18,135,50,112,101,136) Do Until IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(0, 1)) for i = lbound(v1) to ubound(v1) if activeCell.value = v1(i) then activecell.Value = v2(i) exit for end if Next ActiveCell.Offset(1, 0).Activate Loop End Sub -- Regards, Tom Ogilvy "Camel" wrote: Hello Members, I am relatively new with vba and excel and would appreciate how I could go about cleaning or rather streamlining this akward piece of code that follows below. What I would like to accomplish is the following: Loop through just the cells that contain data (i.e. row 1, col 1, row 2, col 2,etc.). In other words, my data set is set like this: ICSnumber ATnumber 4 1 2 65 222 3 53 4 75 5 81 6 If the number in row 1 col1 etc. satisfies the condition in the macro, then, that number is replaced (i.e. 4 1 becomes 1,1 2 65 becomes 2,2 etc.). The macro I have developed works however I like a cleaner piece of code. Would greatly appreciate some code example if not suggestions. Thanks. Sub ICS_to_AT21_first_country_row2() ' ' ' Macro recorded 14/10/98 by Camel ' ' change ICS codes to AT codes ' ' first country in Row 2 ' 'Worksheets.Select Range("a2").Select 'ActiveCell.FormulaR1C1 = "AT code" 'ActiveCell.Offset(1, 0).Activate Do Until IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(0, 1)) If ActiveCell.Offset(0, 0) = 1 Then ActiveCell.Offset(0, 0) = 178 ElseIf ActiveCell.Offset(0, 0) = 2 Then ActiveCell.Offset(0, 0) = 65 ElseIf ActiveCell.Offset(0, 0) = 3 Then ActiveCell.Offset(0, 0) = 119 ElseIf ActiveCell.Offset(0, 0) = 4 Then ActiveCell.Offset(0, 0) = 1 ElseIf ActiveCell.Offset(0, 0) = 7 Then ActiveCell.Offset(0, 0) = 18 ElseIf ActiveCell.Offset(0, 0) = 8 Then ActiveCell.Offset(0, 0) = 135 ElseIf ActiveCell.Offset(0, 0) = 9 Then ActiveCell.Offset(0, 0) = 50 ElseIf ActiveCell.Offset(0, 0) = 10 Then ActiveCell.Offset(0, 0) = 112 ElseIf ActiveCell.Offset(0, 0) = 11 Then ActiveCell.Offset(0, 0) = 101 ElseIf ActiveCell.Offset(0, 0) = 12 Then ActiveCell.Offset(0, 0) = 136 'Else: ActiveCell.Offset(0, 0) = "" End If ActiveCell.Offset(1, 0).Activate Loop End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Streamlining Code
One way
Sub ICS_to_AT21_first_country_row2() ' Macro recorded 14/10/98 by Camel ' ' change ICS codes to AT codes ' ' first country in Row 2 ' Dim i As Long With Range("A2") Do Until IsEmpty(.Offset(i, 0)) And IsEmpty(.Offset(i, 1)) Select Case .Offset(i, 0) Case 1: .Offset(i, 0) = 178 Case 2: .Offset(i, 0) = 65 Case 3: .Offset(i, 0) = 119 Case 4: .Offset(i, 0) = 1 Case 7: .Offset(i, 0) = 18 Case 8: .Offset(i, 0) = 135 Case 9: .Offset(i, 0) = 50 Case 10: .Offset(i, 0) = 112 Case 11: .Offset(i, 0) = 101 Case 12: .Offset(i, 0) = 136 End Select i = i + 1 Loop End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Camel" wrote in message oups.com... Hello Members, I am relatively new with vba and excel and would appreciate how I could go about cleaning or rather streamlining this akward piece of code that follows below. What I would like to accomplish is the following: Loop through just the cells that contain data (i.e. row 1, col 1, row 2, col 2,etc.). In other words, my data set is set like this: ICSnumber ATnumber 4 1 2 65 222 3 53 4 75 5 81 6 If the number in row 1 col1 etc. satisfies the condition in the macro, then, that number is replaced (i.e. 4 1 becomes 1,1 2 65 becomes 2,2 etc.). The macro I have developed works however I like a cleaner piece of code. Would greatly appreciate some code example if not suggestions. Thanks. Sub ICS_to_AT21_first_country_row2() ' ' ' Macro recorded 14/10/98 by Camel ' ' change ICS codes to AT codes ' ' first country in Row 2 ' 'Worksheets.Select Range("a2").Select 'ActiveCell.FormulaR1C1 = "AT code" 'ActiveCell.Offset(1, 0).Activate Do Until IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(0, 1)) If ActiveCell.Offset(0, 0) = 1 Then ActiveCell.Offset(0, 0) = 178 ElseIf ActiveCell.Offset(0, 0) = 2 Then ActiveCell.Offset(0, 0) = 65 ElseIf ActiveCell.Offset(0, 0) = 3 Then ActiveCell.Offset(0, 0) = 119 ElseIf ActiveCell.Offset(0, 0) = 4 Then ActiveCell.Offset(0, 0) = 1 ElseIf ActiveCell.Offset(0, 0) = 7 Then ActiveCell.Offset(0, 0) = 18 ElseIf ActiveCell.Offset(0, 0) = 8 Then ActiveCell.Offset(0, 0) = 135 ElseIf ActiveCell.Offset(0, 0) = 9 Then ActiveCell.Offset(0, 0) = 50 ElseIf ActiveCell.Offset(0, 0) = 10 Then ActiveCell.Offset(0, 0) = 112 ElseIf ActiveCell.Offset(0, 0) = 11 Then ActiveCell.Offset(0, 0) = 101 ElseIf ActiveCell.Offset(0, 0) = 12 Then ActiveCell.Offset(0, 0) = 136 'Else: ActiveCell.Offset(0, 0) = "" End If ActiveCell.Offset(1, 0).Activate Loop End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Streamlining Code
Thank you Tom,
I have now gone about creating code to populate the multidimensional arrays from a text file. One thing however, when I build an else statement (in other words jump over a cell that doesn't have any data, it cancels out all other cells set by the condition). Do Until IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(0, 1)) for i = lbound(v1) to ubound(v1) if activeCell.value = v1(i) then activecell.Value = v2(i) --- Else: -- activecell.value="" exit for end if Next How can I fix this? Thanks alot once again. This is really a helpful group Tom Ogilvy wrote: Sub ICS_to_AT21_first_country_row2() ' ' ' Macro recorded 14/10/98 by Camel ' ' change ICS codes to AT codes ' ' first country in Row 2 ' Dim v1 as Variant, v2 as Variant Dim i as Long 'Worksheets.Select Range("a2").Select 'ActiveCell.FormulaR1C1 = "AT code" 'ActiveCell.Offset(1, 0).Activate v1 = Array(1,2,3,4,7,8,9,10,11,12) v2 = Array(178,65,119,1,18,135,50,112,101,136) Do Until IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(0, 1)) for i = lbound(v1) to ubound(v1) if activeCell.value = v1(i) then activecell.Value = v2(i) exit for end if Next ActiveCell.Offset(1, 0).Activate Loop End Sub -- Regards, Tom Ogilvy "Camel" wrote: Hello Members, I am relatively new with vba and excel and would appreciate how I could go about cleaning or rather streamlining this akward piece of code that follows below. What I would like to accomplish is the following: Loop through just the cells that contain data (i.e. row 1, col 1, row 2, col 2,etc.). In other words, my data set is set like this: ICSnumber ATnumber 4 1 2 65 222 3 53 4 75 5 81 6 If the number in row 1 col1 etc. satisfies the condition in the macro, then, that number is replaced (i.e. 4 1 becomes 1,1 2 65 becomes 2,2 etc.). The macro I have developed works however I like a cleaner piece of code. Would greatly appreciate some code example if not suggestions. Thanks. Sub ICS_to_AT21_first_country_row2() ' ' ' Macro recorded 14/10/98 by Camel ' ' change ICS codes to AT codes ' ' first country in Row 2 ' 'Worksheets.Select Range("a2").Select 'ActiveCell.FormulaR1C1 = "AT code" 'ActiveCell.Offset(1, 0).Activate Do Until IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(0, 1)) If ActiveCell.Offset(0, 0) = 1 Then ActiveCell.Offset(0, 0) = 178 ElseIf ActiveCell.Offset(0, 0) = 2 Then ActiveCell.Offset(0, 0) = 65 ElseIf ActiveCell.Offset(0, 0) = 3 Then ActiveCell.Offset(0, 0) = 119 ElseIf ActiveCell.Offset(0, 0) = 4 Then ActiveCell.Offset(0, 0) = 1 ElseIf ActiveCell.Offset(0, 0) = 7 Then ActiveCell.Offset(0, 0) = 18 ElseIf ActiveCell.Offset(0, 0) = 8 Then ActiveCell.Offset(0, 0) = 135 ElseIf ActiveCell.Offset(0, 0) = 9 Then ActiveCell.Offset(0, 0) = 50 ElseIf ActiveCell.Offset(0, 0) = 10 Then ActiveCell.Offset(0, 0) = 112 ElseIf ActiveCell.Offset(0, 0) = 11 Then ActiveCell.Offset(0, 0) = 101 ElseIf ActiveCell.Offset(0, 0) = 12 Then ActiveCell.Offset(0, 0) = 136 'Else: ActiveCell.Offset(0, 0) = "" End If ActiveCell.Offset(1, 0).Activate Loop End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Streamlining sort | Excel Discussion (Misc queries) | |||
| streamlining | Excel Discussion (Misc queries) | |||
| Streamlining questionnaire analysis | Excel Discussion (Misc queries) | |||
| Streamlining a long IF=(AND formula ?? | Excel Discussion (Misc queries) | |||
| Streamlining Code | Excel Discussion (Misc queries) | |||
Linear Mode
