VBA Code - Fine tuning code to extract data

[dbnfl]

Hello,


I have the below code which is on Sheet “Data” View Code andwhen it runs it takes approx. 2mins to run through 560 Rows.
E.g. ‘Calculating (4 Threads): 27%’
When it runs its like it changes all cells in that column toone of the options then updates.
Its working with what I’m trying to achieve but need to finetune it.
At present on “Data” sheet I’m getting values of 12:00:00 AMwhen on “Wharf Schedule” sheet cell is blank to look up value(s). If the cellis blank on “Wharf Schedule” sheet I want cell on “Data” sheet to remain blankas well.
The “Wharf Schedule” sheet I update every morning and afternoon.As my “Data” sheet updates from “Wharf Schedule” sheet I want to be able to pasteover data on “Wharf Schedule” sheet and columns on “Data” sheet automatically update.If there is no match and cells on “Data” sheet already have values in them fromprevious match, I want them to remain. Is there any way that I can get it toonly update if there is a match?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'my formula onworksheet =INDEX('Wharf Schedules'!B:B,MATCH(Data!AO59&Data!AQ59,'WharfSchedules'!C:C&'Wharf Schedules'!E:E,0))

[FONT=Calibri][SIZE=3][COLOR=#000000] Range("AO5").FormulaArray ="=IFERROR(INDEX('Wharf Schedules'!B:B,MATCH('Data'!AR5&AT5,'WharfSchedules'!C:C&'Wharf Schedules'!E:E,0)),"""")"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] WithRange("AO5:AO" & Cells(Rows.Count, "B").End(xlUp).Row)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].FillDown[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].Value = .Value[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]Range("AP5").FormulaArray ="=IFERROR(INDEX('Wharf Schedules'!G:G,MATCH('Data'!AR5&AT5,'WharfSchedules'!C:C&'Wharf Schedules'!E:E,0)),"""")"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] WithRange("AP5:AP" & Cells(Rows.Count, "B").End(xlUp).Row)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].FillDown[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].Value = .Value[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]Range("AQ5").FormulaArray ="=IFERROR(INDEX('Wharf Schedules'!I:I,MATCH('Data'!AR5&AT5,'WharfSchedules'!C:C&'Wharf Schedules'!E:E,0)),"""")"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] With Range("AQ5:AQ"& Cells(Rows.Count, "B").End(xlUp).Row)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].FillDown[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].Value = .Value[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]Range("AS5").FormulaArray ="=IFERROR(INDEX('Wharf Schedules'!D:D,MATCH('Data'!AR5&AT5,'WharfSchedules'!M:M&'Wharf Schedules'!O:O,0)),"""")"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] WithRange("AS5:AS" & Cells(Rows.Count, "B").End(xlUp).Row)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].FillDown[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].Value = .Value[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]Range("AU5").FormulaArray ="=IFERROR(INDEX('Wharf Schedules'!Q:Q,MATCH('Data'!AR5&AT5,'WharfSchedules'!M:M&'Wharf Schedules'!O:O,0)),"""")"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] WithRange("AU5:AU" & Cells(Rows.Count, "B").End(xlUp).Row)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].FillDown[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].Value = .Value[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]Range("AV5").FormulaArray ="=IFERROR(INDEX('Wharf Schedules'!R:R,MATCH('Data'!AR5&AT5,'WharfSchedules'!M:M&'Wharf Schedules'!O:O,0)),"""")"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] WithRange("AV5:AV" & Cells(Rows.Count, "B").End(xlUp).Row)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].FillDown[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].Value = .Value[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]Range("AW5").FormulaArray = "=IFERROR(INDEX('WharfSchedules'!S:S,MATCH('Data'!AR5&AT5,'Wharf Schedules'!M:M&'WharfSchedules'!O:O,0)),"""")"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] WithRange("AW5:AW" & Cells(Rows.Count, "B").End(xlUp).Row)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].FillDown[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000].Value = .Value[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]'Range("BB5").FormulaArray ="=IFERROR(INDEX('Wharf Schedules'!S:S,MATCH('Data'!AR5&AT5,'WharfSchedules'!M:M&'Wharf Schedules'!O:O,0)),"""")"[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] 'WithRange("BB5:BW" & Cells(Rows.Count, "B").End(xlUp).Row)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'.FillDown[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'.Value = .Value[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]'End With[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=3][COLOR=#000000] End Sub[/COLOR][/SIZE][/FONT]

Any help would be greatly appreciated.

Dale.