Hello All, I am trying to convert an array formula (see below) that works fine, but arrays won't work on workbooks that are shared. So I was trying to convert the array into VBA with a button, in hoping it will resolve my issue, but it needs some tweaking. Basically, I want it to index Col M on the Master Referral List 2021 workbook. Match names in Col A in Sheet A, with names in Col A in Master Referral List 2021 workbook, if they match, enter text that is in Col G on Master Referral List 2021 workbook, into Col K. This action needs to continue until it has matched all of Col A. All data starts on Row 2. Thanks in advance!
Original working array
{=INDEX('G:\Stuff\More Stuff\Tons of Stuff\[Master Referral List 2021.xlsx]Master Referrals'!$M$2:$M$300,MATCH(1,(A2='G:\Stuff\More Stuff\Tons of Stuff\[Master Referral List 2021.xlsx]Master Referrals'!$G$2:$G$300),0))}
VBA in Progress
Private Sub CommandButton3_Click()
Dim lastRow As Long
Application.ScreenUpdating = False
With Sheets("A")
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("K2:K" & lastRow)
.Formula = "=INDEX('G:\Stuff\More Stuff\Tons of Stuff\[Master Referral List 2021.xlsx]Master Referrals'!$M$2:$M$300,MATCH(1,(A2,SEARCH(""/"",A2)+1)),'G:\Stuff\More Stuff\Tons of Stuff\[Master Referral List 2021.xlsx]Master Referrals'!$G$2:$G$300),0))"
.Value = .Value
End With
End With
Application.ScreenUpdating = True
End Sub
Original working array
{=INDEX('G:\Stuff\More Stuff\Tons of Stuff\[Master Referral List 2021.xlsx]Master Referrals'!$M$2:$M$300,MATCH(1,(A2='G:\Stuff\More Stuff\Tons of Stuff\[Master Referral List 2021.xlsx]Master Referrals'!$G$2:$G$300),0))}
VBA in Progress
Private Sub CommandButton3_Click()
Dim lastRow As Long
Application.ScreenUpdating = False
With Sheets("A")
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("K2:K" & lastRow)
.Formula = "=INDEX('G:\Stuff\More Stuff\Tons of Stuff\[Master Referral List 2021.xlsx]Master Referrals'!$M$2:$M$300,MATCH(1,(A2,SEARCH(""/"",A2)+1)),'G:\Stuff\More Stuff\Tons of Stuff\[Master Referral List 2021.xlsx]Master Referrals'!$G$2:$G$300),0))"
.Value = .Value
End With
End With
Application.ScreenUpdating = True
End Sub