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