Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Add additional vba code to existing macro

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

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…
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
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…

840 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