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

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

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

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