Excel: If statement

[florens]

Hi,

I have an application with customers and ever day I generate a report, copy-paste it to Excel and edit certain data in it. This takes me too long, and I want to have some things automated by a macro.
The problem with that is that I don't know how to do something, here's some background info:
I have the zip-code of every customer, since I live in the netherlands, most zipcodes are build up like this: 1000AA. For another report I create, I need to get the first 4 numbers in one field, and the two letters in another. So I have made this macro:
Range("G2:G200&quot.Select
Selection.TextToColumns Destination:=Range("G2:G200&quot, DataType:=xlFixedWidth, _
OtherChar:="4", FieldInfo:=Array(Array(0, 1), Array(4, 1))

It works like a charm, the only problem is that I have foreign customers too. For example a belgium(our neighbours) zipcode is build up of only 4 numbers and the germans have 5 numbers. For these customers my boss wants me to copy the zipcode in front of the city they live in. There's my problem, I don't know how to do that. I have written an IF statement, which looks at the country field and if it is dutch, it splits and if it is not, it doesn't do anything, but this is not enough, and it only worked for one field. Not for the entire column. I don't know how to go to the next row to redo the action (I hope you understand this sentence, I don't know how to make it clear).
It needs to be a macro, because the sheet is deleted and remade again everyday and I want to have it automated as much as possible.

Thanks

 

[SkipVought]

florens,

With a properly designed system (organization of data and process, both manual and automated) it should not be necessary to cut 'n' paste.

I can help with the slice of the problem that you have highlighted, the construction of the postal code, but it seem that the problem is much deeper that that.

If we were just addressing the postal code in a procedure (macro), then the core of the solution would be a Case statement on Country something like this...

Set rngZIP = Range("G2:G200")
For Each c in Range(CountryRange)
  sCountry = c.Value
  Select Case sCountry
    Case "Netherlands"
      sSuffix = ???
      sZIP = Right(Cells(c.Row, rngZIP.Column).Value,4) & sSuffix
    Case "Germany"
      sZIP = 
    Case "Belgium"
      sZIP = 
...
Next
End Select

But I believe that this addresses only a small part of your challenge. Hope it helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[florens]

Skip, thanks for the reply.
I know that the problem is much deeper, and I have told my boss that, but it is very expensive to replace the application with a working one ( ), so that will take some time. And untill that time, I need to stick to copy-paste

I've tried your code, and I need to fill in some things, but I don't know what.
The first 4 numbers of the dutch zipcode need to stay in the G column and the last 2 letters need to go to the H column. The city is in the I column, and if the country (J column) is not 730 (code for netherlands) the zipcode of column G has to be pasted in front of the city in column I.

I think that I have not explained it correctly in my first post, sorry for that. Do you know how to change your code to have it do this?
Thanks,

Florens

 

[SkipVought]

I would suggest that you insert a new sheet.

Set wsNew = Worksheets("New")
Set wsOld = Worksheets("Old")
Set rngZIP = wsOld.Range("G2:G200")
Set rngCity = wsOld.Range(cells(1,"I"), cells
(1,"I").end(xlDown))
Set rngCountry = wsOld.Range(cells(1,"J"), cells
(1,"J").end(xlDown))
For Each c in rngCountry 
  sCountry = c.Value
  wsNew.Cells(c.row, "I").Value = wsOld.Cells
  wsNew.Cells(c.row, "J").Value = wsOld.Cells(c.Row, "J").Value
  Select Case sCountry
    Case "730"
      wsNew.Cells(c.row, "G").Value = Left(Cells(c.Row, rngZIP.Column).Value,4)     
      wsNew.Cells(c.row, "H").Value = Right(Cells(c.Row, rngZIP.Column).Value,2)
Case "830"
      wsNew.Cells(c.row, "G").Value = ???
      wsNew.Cells(c.row, "H").Value = ???
    Case "930"
      wsNew.Cells(c.row, "G").Value = ???
      wsNew.Cells(c.row, "H").Value = ???
...
Next
End Select

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[florens]

Skip, thanks for the quick reply.
Why do you suggest a new sheet?
And I have runned your code (ofcourse, after I entered the right values at the question marks and compiled it without errors). But Excel crashes at the eight line:
wsNew.Cells(c.row, "I&quot.Value = wsOld.Cells

My computers activity light goes on (doesn't flikker) and when I go into Task manager it says Excel not responding. I've run the macro step by step so I'm sure it's at that line. Isn't it possible to keep it on the same sheet?

Florens

 

[SkipVought]

Sorry, it was a typo...

  wsNew.Cells(c.row, "I").Value = wsOld.Cells(c.Row, "I").Value
  wsNew.Cells(c.row, "J").Value = wsOld.Cells(c.Row, "J").Value

It's not a good idea to perturb source data. Source data ought to remain as source data.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[florens]

You're right, Source data should stay source data. I hadn't thought of that.

It all works fine now, the only thing that remains is that for foreign countries the zip should be pasted infront of the city. I've tried to do it like this, but I get an error:
wsNew.Cells(c.Row, "I&quot.Value = rngZip + rngCity
also tried this:
wsNew.Cells(c.Row, "I&quot.Value = wsOld.Cells(c.Row, "G&quot.value + wsOld.Cells(c.Row, "I&quot

but this didn't work as well. I'm sorry I keep asking to spell things out for me, but I have no expierence with VB in Excel.

Florens

 

[SkipVought]

This should work

With wsOld
   wsNew.Cells(c.Row, "I").Value = .Cells(c.Row, "G").Value & .Cells(c.Row, "I").Value
End With

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[florens]

Thank you very much. It works perfectly!
Florens

 

[DaleWatson123321]

Florens,

Because you've already awarded STARS to show your appreciation for other help received, I'm sure it's just an oversight that caused you not to award a STAR to Skip.

Given that Skip has provided a significant amount of help, I'm sure you'll appreciate receiving this "reminder".

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca