Need a button to do all the work

[bengy17]

Hi,

So what I need is a button so that users can copy there information in and then press this button to generate it in another sheet, then export that sheet to a .xlsx. I am currently using Office 2019 so I am missing some functions.

So I need the following to happen:



Column Ref On FinishedSheet

Source Sheet



A

K



B

A



C

B



D

C



E

L



F

M



G

D



H

E



I

G



J

F



K

R



L

S



M

N/A



O

STREET FROM D



P

TOWN/CITY FROM D



Q

COUNTY FROM D



R

E



S

HOUSE NAME FROM H



T

STREET FROM H



U

TOWN/CITY FROM H



V

COUNTY FROM H



W

I




Now the only issues with this are splitting column D and Column H on the Source sheet to send to the finished sheet. I have made two extra sheets with all cities and county's in that can be used as a lookup to filter column D. Column H has a delimiter so should be more straight forward.

I have detailed what needs to match where, i've been stuck on this a week and have determined that as the delimiter can be different on column D I need to do a lookup against the cities and counties. See attached sheet.

HELP ME VBA-KENOBI you're my only hope.

[Paul_Hossler]

You try this

Because the data is not consistant, I don't know if I caught all the cases

Didn't use the City / county lists




Option Explicit


'Final col
' Source col
'A K 11
'B A 1
'C B 2
'D C 3
'E L 12
'F M 13
'G D 4
'H E 5
'I G 7
'J F 6
'K R 18
'L S 19
'M N / A
'O STREET FROM D 4
'P TOWN/CITY FROM D 4
'Q COUNTY FROM D 4
'R E 5
'S HOUSE NAME FROM H 8
'T STREET FROM H 8
'U TOWN/CITY FROM H 8
'V COUNTY FROM H 8
'W I 9




Sub MoveData()
Dim rowFrom As Long, rowTo As Long, rowLast As Long
Dim i As Long
Dim wsFrom As Worksheet, wsTo As Worksheet
Dim House As String, Street As String, City As String, County As String

Set wsFrom = Worksheets("Source")
Set wsTo = Worksheets("FinishedSheet")

rowLast = wsFrom.Cells(wsFrom.Rows.Count, 1).End(xlUp).Row
rowTo = 2

For rowFrom = 2 To rowLast
With wsFrom.Rows(rowFrom)
wsTo.Rows(rowTo).Cells(1).Value = .Cells(11)
wsTo.Rows(rowTo).Cells(2).Value = .Cells(1)
wsTo.Rows(rowTo).Cells(3).Value = .Cells(2)
wsTo.Rows(rowTo).Cells(4).Value = .Cells(3)
wsTo.Rows(rowTo).Cells(5).Value = .Cells(12)
wsTo.Rows(rowTo).Cells(6).Value = .Cells(13)
wsTo.Rows(rowTo).Cells(7).Value = .Cells(4)
wsTo.Rows(rowTo).Cells(8).Value = .Cells(5)
wsTo.Rows(rowTo).Cells(9).Value = .Cells(7)
wsTo.Rows(rowTo).Cells(10).Value = .Cells(6)
wsTo.Rows(rowTo).Cells(11).Value = .Cells(18)
wsTo.Rows(rowTo).Cells(12).Value = .Cells(19)
'skip col M
'skip col N
Call SplitAddr_1(.Cells(4).Value, Street, City, County)


wsTo.Rows(rowTo).Cells(15).Value = Street
wsTo.Rows(rowTo).Cells(16).Value = City
wsTo.Rows(rowTo).Cells(17).Value = County

wsTo.Rows(rowTo).Cells(18).Value = .Cells(5)


Call SplitAddr_2(.Cells(8).Value, House, Street, City, County)
wsTo.Rows(rowTo).Cells(19).Value = House
wsTo.Rows(rowTo).Cells(20).Value = Street
wsTo.Rows(rowTo).Cells(21).Value = City
wsTo.Rows(rowTo).Cells(22).Value = County


wsTo.Rows(rowTo).Cells(23).Value = .Cells(9)


End With

rowTo = rowTo + 1

Next rowFrom

MsgBox "Done"

End Sub


Private Sub SplitAddr_1(sIn As String, Street As String, City As String, County As String)
Dim i As Long
Dim v As Variant

sIn = Replace(sIn, ",", " ")
sIn = Replace(sIn, " ", " ")
sIn = Trim(sIn)

Street = vbNullString
City = vbNullString
County = vbNullString

v = Split(sIn, " ")

County = v(UBound(v))
City = v(UBound(v) - 1)


For i = LBound(v) To UBound(v) - 2
Street = Street & " " & v(i)
Next i
Street = Trim(Street)


End Sub


Private Sub SplitAddr_2(sIn As String, House As String, Street As String, City As String, County As String)
Dim i As Long
Dim v As Variant

sIn = Replace(sIn, ", ", ",")
sIn = Replace(sIn, ",,", ",")
sIn = Replace(sIn, " ", " ")
sIn = Trim(sIn)

House = vbNullString
Street = vbNullString
City = vbNullString
County = vbNullString

v = Split(sIn, ",")

If UBound(v) = 3 Then
House = v(LBound(v))
Street = v(LBound(v) + 1)
City = v(LBound(v) + 2)
County = v(LBound(v) + 3)


Else
House = v(LBound(v))
Street = v(LBound(v) + 1)
City = vbNullString
County = v(LBound(v) + 2)
End If






End Sub

[bengy17]

Hi Paul,

Unfortunately this doesn't work as it cuts off part of the street names, and puts them on the incorrect column. So it reads 121 Test | Way |

Also, I have just tried with some fresh data and it is erroring. Getting a Subscript error on Street = v(LBound(v) + 1)

Any ideas to stop both those issues?

[Paul_Hossler]

Collection Address


1 AUDI CLOSE WATFORD HERTFORDSHIRE


2 BMW AVENUE HUNTINGDON NORTHAMPTON


3 MAXINE WAY BOURNEMOUTH DORSET


4 RADIAL DRIVE REDRUTH CORNWALL


5 SAW ROAD YORK YORKSHIRE




Provide a spreadsheet with more complete and realistic addresses and I'll take a look

[yogeshpaji]

Nice