Solved

Add additional vba code to existing macro

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Defining contents of Combo box 4 68
Make sub to format and total ranges 9 64
Access 2016 - Use a Button to Select a specific Record on a form 12 74
IT Desktop Support 11 76
I wanted to share this with fellow Experts, who might not know. How often have you wanted to learn something, only to be set back by either restrictions imposed on "trial" or "evaluation" software?  How often have you had to rebuild a home networ…
As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

831 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