LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default 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

 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Streamlining sort A.R. Hunt Excel Discussion (Misc queries) 6 February 25th 08 05:10 PM
streamlining mttmwsn Excel Discussion (Misc queries) 9 October 29th 07 02:16 PM
Streamlining questionnaire analysis englishmustard Excel Discussion (Misc queries) 2 February 24th 06 12:22 PM
Streamlining a long IF=(AND formula ?? David.Allen297 Excel Discussion (Misc queries) 2 October 5th 05 12:16 PM
Streamlining Code Soundman Excel Discussion (Misc queries) 4 July 26th 05 02:42 PM


All times are GMT +1. The time now is 06:33 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"