Worksheet Formula as VBA Code

[dbnfl]

Hello,

I'm trying to convert a work sheet formula to VBA.

I enter a new job in a user form, when I hit continue and info transfers to "Data" sheet I want code to run and update.

Sub Vessel_Details_WorksheetChange()

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


With Range("AL5:AL" & Range("AO" And "AQ" & Rows.Count).End(xlUp).Row)
.Formula = "=IFERROR(INDEX('Worksheet3'!B:B,MATCH('Worksheet'!"AO" And "AQ",'Worksheet2'!C:C&'Worksheet2'!E:E,0)),"""")"
.Value = .Value
End With
End Sub

 

[theBardd]

Try this

With Range("AL5:AL" & Cells(Rows.Count, "AL").End(xlUp).Row)
   .Formula = "=IFERROR(INDEX('Wharf Schedules'!C2,MATCH('Data'!RC41&RC43,'Wharf Schedules'!C:C&'Wharf Schedules'!E:E,0)),"""")"
   .Value = .Value
End With

 

[Fluff]

Maybe

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

 

[dbnfl]

Hello,

Didn't have any luck with code suggestions.

I wan this code to run on worksheet change. I have the code in worksheet1("Data") view code.

 

[theBardd]

In what way did they not work?

 

[Fluff]

What is your entire code?

 

[Fluff]

If neither of the codes worked, why did you Start a new thread, using one of them?

 

[dbnfl]

Hello Fluff,

I tried to post about adding in more code to same sub. I thought I posted about that and tried finding post but couldn't fi it. That's why the 2nd post.

I got your ode to work.

What I need to do now is add o code o more columns au fill.

Code I have is below with your help, and thank you for that

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

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

Range("AL5").FormulaArray = "=IFERROR(INDEX('Wharf Schedules'!B:B,MATCH('Data'!AR5&AT5,'Wharf Schedules'!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

I'm not sue how to add more code to it Eg.

Range("AP5").FormulaArray = "=IFERROR(INDEX('Wharf Schedules'!G:G,MATCH('Data'!AR5&AT5,'Wharf Schedules'!C:C&'Wharf Schedules'!E:E,0)),"""")"
With Range("AL5:AL" & Cells(Rows.Count, "B").End(xlUp).Row)

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

Range("AS5").FormulaArray = "=IFERROR(INDEX('Wharf Schedules'!D:D,MATCH('Data'!AR5&AR5,'Wharf Schedules'!C:C&'Wharf Schedules'!E:E,0)),"""")"
With Range("AL5:AL" & Cells(Rows.Count, "B").End(xlUp).Row)

Range("AU5").FormulaArray = "=IFERROR(INDEX('Wharf Schedules'!Q:Q,MATCH('Data'!AR5&AR5,'Wharf Schedules'!M:M&'Wharf Schedules'!O:O,0)),"""")"
With Range("AL5:AL" & Cells(Rows.Count, "B").End(xlUp).Row)

Range("AW5").FormulaArray = "=IFERROR(INDEX('Wharf Schedules'!R:R,MATCH('Data'!AR5&AR5,'Wharf Schedules'!M:M&'Wharf Schedules'!O:O,0)),"""")"
With Range("AL5:AL" & Cells(Rows.Count, "B").End(xlUp).Row)

Range("AV5").FormulaArray = "=IFERROR(INDEX('Wharf Schedules'!S:S,MATCH('Data'!AR5&AR5,'Wharf Schedules'!M:M&'Wharf Schedules'!O:O,0)),"""")"
With Range("AL5:AL" & Cells(Rows.Count, "B").End(xlUp).Row)

Range("AW5").FormulaArray = "=IFERROR(INDEX('Wharf Schedules'!T:T,MATCH('Data'!AR5&AR5,'Wharf Schedules'!M:M&'Wharf Schedules'!O:O,0)),"""")"
With Range("AL5:AL" & Cells(Rows.Count, "B").End(xlUp).Row)

Dale

 

[Fluff]

As you have started a new thread for this, you need to continue in that thread.
https://www.mrexcel.com/forum/excel...ding-more-code-existing-code.html#post5323814