Solved

Add additional vba code to existing macro

Posted on 2014-04-10
2
338 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

679 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