PDA

View Full Version : Transfer zip code to another column



wilder
04-28-2009, 11:43 AM
Hello,
I need your help with a script:
In my spreadsheet, in column E, starting at line 3 and going down, I have different postal code. Now what I want to do is that, if the 3 first digits from the postal code are followed by the same 3 last digits, in column F, it will write only the 3 first digits. If the 3 first are the same but the 3 last are not the same, it will write the entire postal code in column F.

EX:

"COL E" "COL F"
C2B 4F9 C2B
C2B 4F9 C2B
C9T 3R4 C9T 3R4
C9T 1J0 C9T 1JO

I have an error on my script:

Private Sub CommandButton3_Click()
'For the postal code
Sheets("TL Data ").Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Range("F3").Select


Set MaZone = Range("E3:" & Range("E65536").End(xlUp).Address)
For Each x In MaZone
x.Offset(0, 1) = Split(x, " ")(0)
Set c = MaZone.find(Split(x, " ")(0))
firstAddress = c.Address
Do
If x.Value <> c.Value Then x.Offset(0, 1) = x: Exit Do
Set c = MaZone.FindNext(c)
Loop While c.Address <> firstAddress
Next
End Sub

It sais: Object variable or with block variable not set (run time error 91)

And it has to to i think with that section of the code:

firstAddress = c.Address

Thanks for your help.

mdmackillop
04-28-2009, 11:48 AM
Can you post a sample to test the code?

wilder
04-28-2009, 11:55 AM
Yes.

mdmackillop
04-28-2009, 12:02 PM
Is this correct?

wilder
04-28-2009, 12:21 PM
I cant open the file because of the low speed Internet.
Could you give me the code only for now please?
I will open the spreadsheet later.
Thanks again

mdmackillop
04-28-2009, 12:43 PM
No Code
F3 =IF(E4=E3,LEFT(E4,3),"")
G3 =IF(E4=E3,"",E4)

wilder
04-28-2009, 03:02 PM
OK,
In an other circumstance, it would of been perfect but now it doesn’t.
Actually, if I could have all in the same column, it would be better. Maybe in VBA.

mdmackillop
04-28-2009, 03:09 PM
In an other circumstance, it would of been perfect but now it doesn’t.

Means nothing to me.


Actually, if I could have all in the same column, it would be better.

Just combine the two formulae into one.


Maybe in VBA.


Why?

wilder
04-28-2009, 03:17 PM
Because that file his an official file that i cant modified since the script that follows that, it transfer into notepad, and then transferred on the server.

Benzadeus
04-29-2009, 10:44 AM
In an other circumstance, it would of been perfect but now it doesn’t.

LOL?!

wilder
04-29-2009, 03:43 PM
forget it, I will find the solution myself.

If this is to make the world laugh.

Thank you anyway

mdmackillop
04-30-2009, 12:32 AM
If you need further asstance, please be specific. There is nothing in your responses which would guide me to your requirements.