View Full Version : value for a specific zip code
wilder
03-03-2009, 02:20 PM
Hello,
I need your help on a vba macro.
In the column F and J, I have zip code at 3 digits and some more with 6 digits.
Now what I want the macro to do is that if it has a 3 digit zip code, it will put the number 5 to the cell before, and if its a 6 digits zip code, it will put the number 8 to the cell before.
Thanks for your help.
CreganTur
03-03-2009, 02:27 PM
You can use the Len() function to determine the length, or number of characters, in a string. If you want to do this via VBA, you could use something like:
If Len(Sheet1.Range("F2").Value) = 3 Then
'place a 5 in a cell
ElseIf Len(Sheet1.Range("F2").Value) = 6 Then
'place a 8 in a cell
End If
The above is pseudo-code to give you a general idea of where to start.
HTH:thumb
wilder
03-04-2009, 10:44 AM
Thanks, here his the final answer:
'key regions
For Each cel In Range("F2:F" & Range("F65536").End(xlUp).Row)
cel.Offset(0, -1) = IIf(Len(cel) = 3, 5, 8)
Next
For Each cel In Range("J2:J" & Range("J65536").End(xlUp).Row)
cel.Offset(0, -1) = IIf(Len(cel) = 3, 5, 8)
Next
Thanks again.
CreganTur
03-04-2009, 10:55 AM
You're very welcome.
Please be sure to mark your thread as solved. Click on Thread Tools at the top of the screen and select "Mark As Solved"
Thanks:thumb
mdmackillop
03-04-2009, 02:31 PM
Hi Wilder,
Use the green VBA button to format your code as shown, rather than Code tags.
Regards
MD
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.