Adding more code to existing Code

[dbnfl]

Hello,

I have the below code and I want to add more to theworksheet change code.

Current Code I have

Private Sub Worksheet_SelectionChange(ByVal Target AsRange)

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


Range("AL5").FormulaArray ="=IFERROR(INDEX('Wharf Schedules'!B:B,MATCH('Data'!AO5&AQ5,'WharfSchedules'!C:C&'Wharf Schedules'!E:E,0)),"""")"
With Range("AL5:AL" & Cells(Rows.Count,"B").End(xlUp).Row)
.FillDown
.Value = .Value

End With

End Sub

Want to add more code like below to auto fill more columns.I’m not sure how to add it to what I already have.

Range("AM5").FormulaArray ="=IFERROR(INDEX('Wharf Schedules'!G:G,MATCH('Data'!AO5&AQ5,'WharfSchedules'!C:C&'Wharf Schedules'!E:E,0)),"""")"
With Range("AL5:AL" & Cells(Rows.Count,"B").End(xlUp).Row)
.FillDown
.Value = .Value

Dale

 

[Fluff]

You need to change the range in the with statement to match the range for the formula like

Range("AM5").FormulaArray = "=IFERROR(INDEX('Wharf Schedules'!G:G,MATCH('Data'!AO5&AQ5,'WharfSchedules'!C:C&'Wharf Schedules'!E:E,0)),"""")"
With Range("AM5:AM" & Cells(Rows.Count, "B").End(xlUp).Row)
   .FillDown
   .Value = .Value
End With

 

[dbnfl]

Hello Fluff,

Code workedperfect. Thank you so much.

Is thereanyway that if there is no match it doesn’t add anything to cell?

I have onemore I’m trying to add in, but I have no clue on how to do it.
Last FreeDay column Sheets(1) “Data” Column B:B = (Date Value)

I Need toget a date value based on multiple criteria and sum
1^st- Match Sheets(1) “Data” Column AZ (Header:Shipping Line) with Sheets(4) “Lists” Column O (Header: Shipping Line)
Once matchedmove one cell to right Sheets(4) “Lists” Column P and depending on cell value Iwant to sum
Sheets(1) “Data”Column AU (If Value of Sheets(4) Column P = First Availablility)
Or
Sheets(1) “Data”Column AX (If Value of Sheet(4) Column P = Wharf Date of Discharge)
With Valueof Sheets(4) “Lists” Column Q, R, S, T, U, V, W, X, Y, Z, AA, or AB.
Eg. Sheets(4)“Lists” Column and Container type.
Q = 20GP
R = 20HC
S = 20RF
T = 20OT
U = 20FR
V = 20TK
W = 40GP
X = 40HC
Y = 40RF
Z = 40OT
AA = 40FR
AB = 40TK

I need toreference container type from Sheets(1) “Data” Column S of that row.

I am hopingyou can help me with this.

Dale.

 

[dbnfl]

Code I have working. Want to add into this code.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'my formula on worksheet =INDEX('Wharf Schedules'!B:B,MATCH(Data!AO59&Data!AQ59,'Wharf Schedules'!C:C&'Wharf Schedules'!E:E,0))

Range("AO5").FormulaArray = "=IFERROR(INDEX('Wharf Schedules'!B:B,MATCH('Data'!AR5&AT5,'Wharf Schedules'!C:C&'Wharf Schedules'!E:E,0)),"""")"
With Range("AO5:AO" & Cells(Rows.Count, "B").End(xlUp).Row)
.FillDown
.Value = .Value
End With
Range("AP5").FormulaArray = "=IFERROR(INDEX('Wharf Schedules'!G:G,MATCH('Data'!AR5&AT5,'Wharf Schedules'!C:C&'Wharf Schedules'!E:E,0)),"""")"
With Range("AP5:AP" & Cells(Rows.Count, "B").End(xlUp).Row)
.FillDown
.Value = .Value
End With
Range("AQ5").FormulaArray = "=IFERROR(INDEX('Wharf Schedules'!I:I,MATCH('Data'!AR5&AT5,'Wharf Schedules'!C:C&'Wharf Schedules'!E:E,0)),"""")"
With Range("AQ5:AQ" & Cells(Rows.Count, "B").End(xlUp).Row)
.FillDown
.Value = .Value
End With
Range("AS5").FormulaArray = "=IFERROR(INDEX('Wharf Schedules'!D:D,MATCH('Data'!AR5&AT5,'Wharf Schedules'!M:M&'Wharf Schedules'!O:O,0)),"""")"
With Range("AS5:AS" & Cells(Rows.Count, "B").End(xlUp).Row)
.FillDown
.Value = .Value
End With
Range("AU5").FormulaArray = "=IFERROR(INDEX('Wharf Schedules'!Q:Q,MATCH('Data'!AR5&AT5,'Wharf Schedules'!M:M&'Wharf Schedules'!O:O,0)),"""")"
With Range("AU5:AU" & Cells(Rows.Count, "B").End(xlUp).Row)
.FillDown
.Value = .Value
End With
Range("AV5").FormulaArray = "=IFERROR(INDEX('Wharf Schedules'!R:R,MATCH('Data'!AR5&AT5,'Wharf Schedules'!M:M&'Wharf Schedules'!O:O,0)),"""")"
With Range("AV5:AV" & Cells(Rows.Count, "B").End(xlUp).Row)
.FillDown
.Value = .Value
End With
Range("AW5").FormulaArray = "=IFERROR(INDEX('Wharf Schedules'!S:S,MATCH('Data'!AR5&AT5,'Wharf Schedules'!M:M&'Wharf Schedules'!O:O,0)),"""")"
With Range("AW5:AW" & Cells(Rows.Count, "B").End(xlUp).Row)
.FillDown
.Value = .Value
End With


End Sub

 

[Fluff]

If there is no match the formula will result in an error which is covered by the IfError & so the cell will remain blank.

For your other question, you will need to start a new thread.