VBA Help

[ExceLoki]

Going to start off by stating, VBA is nto my strong suit. What I'm trying to do is have a script that will edit the values in the "ZIP" column of this table i'm working in. My data range can be anywhere from 200-6000 lines. I would like it to edit the first cell and then go to the next until there are no more.
The formula I came up with to get what I am looking for is below:

=IFS(LEN(create_users_20221122_1134323[@Zip])=5,create_users_20221122_1134323[@Zip],RIGHT(create_users_20221122_1134323[@Zip],4)="0000",LEFT(create_users_20221122_1134323[@Zip],5),LEN(create_users_20221122_1134323[@Zip])=10,TEXTJOIN("-",TRUE,LEFT(create_users_20221122_1134323[@Zip],5),RIGHT(create_users_20221122_1134323[@Zip],4)))

My input is in column A and desired results in column B
-------------------

fix zip codes.xlsm
	A	B
1	Zip	formula
2	30506	30506
3	0	#N/A
4	04571	04571
5	29926 2272	29926-2272
6	22201 1701	22201-1701
7	19072 0000	19072
8	22027 0000	22027
9	32163 0000	32163
10	21045 2529	21045-2529
11	33414 6245	33414-6245
12	11768	11768
13	10025	10025
Sheet1

Cell Formulas
Range	Formula
B2:B13	B2	=IFS(LEN(Table3[@Zip])=5,Table3[@Zip],RIGHT(Table3[@Zip],4)="0000",LEFT(Table3[@Zip],5),LEN(Table3[@Zip])=10,TEXTJOIN("-",TRUE,LEFT(Table3[@Zip],5),RIGHT(Table3[@Zip],4)))

 

[lrobbo314]

How about this?

Sub LOKI()
Dim ws As Worksheet:        Set ws = ActiveSheet
Dim r As Range:             Set r = ws.Range("Table3")
Dim AR() As Variant:        AR = r.Value2
Dim tmp As String
Dim sp() As String

For i = 1 To UBound(AR)
    tmp = AR(i, 1)
    If InStr(tmp, " ") > 0 Then
        sp = Split(tmp, " ")
        If sp(1) = "0000" Then
            AR(i, 1) = sp(0)
        Else
            AR(i, 1) = Replace(tmp, " ", "-")
        End If
    ElseIf Len(tmp) = 1 Then
        AR(i, 1) = "#N/A"
    End If
Next i

r.Value2 = AR
End Sub

 

[ExceLoki]

Thanks for the quick reply. It worked great, except in row 4 it returned 4571 instead of 04571. Is there a way to fix that?
-----------------

fix zip codes.xlsm
	A
1	Zip
2	30506
3	#N/A
4	4571
5	29926-2272
6	22201-1701
7	19072
8	22027
9	32163
10	21045-2529
11	33414-6245
12	11768
13	10025
Sheet1

 

[lrobbo314]

Try this one.

Sub LOKI2()
Dim ws As Worksheet:        Set ws = ActiveSheet
Dim r As Range:             Set r = ws.Range("Table3")
Dim AR() As Variant:        AR = r.Value2
Dim tmp As String
Dim sp() As String

For i = 1 To UBound(AR)
    tmp = AR(i, 1)
    Select Case Len(tmp)
        Case 1
            AR(i, 1) = "#N/A"
        Case Is > 5
            sp = Split(tmp, " ")
            If sp(1) = "0000" Then
                AR(i, 1) = sp(0)
            Else
                AR(i, 1) = Replace(tmp, " ", "-")
            End If
        Case Else
            AR(i, 1) = "'" & CStr(tmp)
    End Select
Next i

r.Value2 = AR
End Sub

 

[ExceLoki]

getting an error

 

[ExceLoki]

and here is a more accurate version of the data
-----------------

fix zip codes.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Zip
2													60525
3													60657
4													29483
5													60047
6													28604
7													60471
8													87004
9													61615
10													60613
11													85225
12													34266
13													20816
14													30506
15													54634
16													60516
17													72653
18													25425
19													60098
20													60482
21													85266
22													20910
23													46321
24													91977
25													47203
26													33947
27													60033
28													60457
29													60452
30													60451
31													60056
32													21230
33													60473
34													60615
35													60477
36													60515
37													60463
38													21108
39													08051
40													55811
41													89081
42													46374
43													33487
44													60126
45													61353
46													37923
47													60402
48													61821
49													33761
50													78624
51													60657
52													60409
53													60453
54													60164
55													60189
56													33434
57													89144
58													34238
59													34231
60													63011
61													60640
62													60137
63													60201
64													49504
65													53188
66													92637
67													60188
68													60403
69													60441
70													29414
71													04571
72													75013
73													60613
74													60448
75													33763
76													60008
77													60103
78													60193
79													60610
80													85250
81													60093
82													60619
83													60477
84													50266
85													60163
86													60035
87													60525
88													97330
89													60611
90													33626
91													60441
92													83704
93													97201
94													60098
95													60098
96													60045
97													92108
98													60477
99													60014
100													60304 1356
101													80526 1557
102													60657 0000
103													60610 7898
104													60005 1849
105													60657 4222
106													60614 2510
107													60525 2352
108													60302 2025
109													60657 2024
110													29926 2272
111													76712 0000
112													60612 1142
113													22201 1701
114													19072 0000
115													22027 0000
116													60657 2980
117													60093 1721
118													62656 3127
119													60025 4022
120													77024 4502
121													60047 7694
122													85718 0000
123													53217 0000
124													60010 0000
125													60124 3801
126													60069 3315
127													32163 0000
128													60558 1051
129													85020 4731
130													20853 2536
131													21045 2529
132													60010 0000
133													60177 2738
134													60707 3942
135													60563 2678
136													60201 2404
137													60044 2036
138													98004 1336
139													33414 6245
140													60302 0000
141													60643 4807
142													60640 5301
143													60646 2836
144													60521
145													60706 2186
146													60022 2027
147													0
148													46534
149													60148
150													60035
New Sheet

 

[ExceLoki]

i thought it'd be easier to change from original post to the table i need it in, but again... " VBA is nto my strong suit"

 

[lrobbo314]

This code worked when I tested it. The only change I made was on the second line where I am assigning the 'r' range variable to the specific 'Zip' column in your table.

Sub LOKI2()
Dim ws As Worksheet:        Set ws = ActiveSheet
Dim r As Range:             Set r = ws.Range("Table3[Zip]")
Dim AR() As Variant:        AR = r.Value2
Dim tmp As String
Dim sp() As String


For i = 1 To UBound(AR)
    tmp = AR(i, 1)
    Select Case Len(tmp)
        Case 1
            AR(i, 1) = "#N/A"
        Case Is > 5
            sp = Split(tmp, " ")
            If sp(1) = "0000" Then
                AR(i, 1) = sp(0)
            Else
                AR(i, 1) = Replace(tmp, " ", "-")
            End If
        Case Else
            AR(i, 1) = "'" & CStr(tmp)
    End Select
Next i

r.Value2 = AR
End Sub

 

[ExceLoki]

i'm still getting the debug

 

[lrobbo314]

I don't know why you would be getting that error. I copied the data from your post and it ran fine.

What is the value of i when you get the error?

Another thing to possibly try... I did the same thing in Power Query. Not sure if your very familiar with that.

PQ
	A
1	Zip
2	60525
3	60657
4	29483
5	60047
6	28604
7	60471
8	87004
9	61615
10	60613
11	85225
12	34266
13	20816
14	30506
15	54634
16	60516
17	72653
18	25425
19	60098
20	60482
21	85266
22	20910
23	46321
24	91977
25	47203
26	33947
27	60033
28	60457
29	60452
30	60451
31	60056
32	21230
33	60473
34	60615
35	60477
36	60515
37	60463
38	21108
39	8051
40	55811
41	89081
42	46374
43	33487
44	60126
45	61353
46	37923
47	60402
48	61821
49	33761
50	78624
51	60657
52	60409
53	60453
54	60164
55	60189
56	33434
57	89144
58	34238
59	34231
60	63011
61	60640
62	60137
63	60201
64	49504
65	53188
66	92637
67	60188
68	60403
69	60441
70	29414
71	4571
72	75013
73	60613
74	60448
75	33763
76	60008
77	60103
78	60193
79	60610
80	85250
81	60093
82	60619
83	60477
84	50266
85	60163
86	60035
87	60525
88	97330
89	60611
90	33626
91	60441
92	83704
93	97201
94	60098
95	60098
96	60045
97	92108
98	60477
99	60014
100	60304-1356
101	80526-1557
102	60657
103	60610-7898
104	60005-1849
105	60657-4222
106	60614-2510
107	60525-2352
108	60302-2025
109	60657-2024
110	29926-2272
111	76712
112	60612-1142
113	22201-1701
114	19072
115	22027
116	60657-2980
117	60093-1721
118	62656-3127
119	60025-4022
120	77024-4502
121	60047-7694
122	85718
123	53217
124	60010
125	60124-3801
126	60069-3315
127	32163
128	60558-1051
129	85020-4731
130	20853-2536
131	21045-2529
132	60010
133	60177-2738
134	60707-3942
135	60563-2678
136	60201-2404
137	60044-2036
138	98004-1336
139	33414-6245
140	60302
141	60643-4807
142	60640-5301
143	60646-2836
144	60521
145	60706-2186
146	60022-2027
147	#N/A
148	46534
149	60148
150	60035
Table3

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    ROC = Table.SelectColumns(Source,{"Zip"}),
    Custom1 = Table.TransformColumns(ROC,{{"Zip", each 
        let 
            SP = try Text.Split(_," ") otherwise {_},
            X = if List.Count(SP) = 2 then List.Transform(SP, each if _ ="0000" then null else _) else {Text.From(_)} & {null},
            C = Text.Combine(X,"-")
        in 
            C
    }}),
    Custom2 = Table.TransformColumns(Custom1,{{"Zip", each if _ = "0" then "#N/A" else _}})
in
    Custom2