value for a specific zip code

[wilder]

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]

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]

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]

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]

Hi Wilder,
Use the green VBA button to format your code as shown, rather than Code tags.
Regards
MD