Solved

Add additional vba code to existing macro

Posted on 2014-04-10
2
310 Views
Last Modified: 2014-04-11
Hi Experts,

I have an existing macro that formats an excel spreadsheet into fixed width text file based on customer specs.

I need to add code to enter ZZ into dl_state column if driver lives in foreign country, except if it is Canada.   For Canada the province code gets entered into the dl_state column for all provinces except Northwest Territories and Yukon.    These get a ZZ.

What is best approach for this?  I've attached spreadsheet with data(dummy data)  and the macro.  
Thanks.
afftest.txt
DataTest.xls
0
Comment
Question by:rhadash
2 Comments
 
LVL 35

Accepted Solution

by:
Kimputer earned 500 total points
ID: 39992924
Find the loop where you applied the XX, replace it with this:

'Range("c1") = "Filler"


For i = 2 To lastRow

    If Len(Cells(i, "I").Text) < 2 Then
        Cells(i, "I").Value = "ZZ"
    End If
    If Sheets("CHI").Cells(i, "W").Value <> "CA" Then
        Cells(i, "I").Value = "ZZ"
    Else
        If Sheets("CHI").Cells(i, "U").Value = "NT" Or Sheets("CHI").Cells(i, "U").Value = "YT" Then
            Cells(i, "I").Value = "ZZ"
        Else
            Cells(i, "I").Value = Sheets("CHI").Cells(i, "U").Value
        End If
    End If

Next i

Open in new window

0
 

Author Closing Comment

by:rhadash
ID: 39994690
Thanks.  You have provided the basic structure required to make this work.  I've been able to adjust to meet requirements.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now