I don't know why my VBA code got Subscript Out of Range. Can anybody please look at my code.
Sub getPartnerName()
Dim i, j As Long
Dim Partner(1 To 2) As Variant
Dim partnercol As Long
Dim partnerletter As String
Dim RefNumObjpartner As Variant
Dim filename As Variant
Dim Partners As String
Dim PartnerIDCol As Long
Dim PartnerIDLetter As String
Dim PartnerID As String
Cells.Find(What:="Partner_Check", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Select
PartnerCheckCol = ActiveCell.Column
partnerletter = Left(ActiveCell.Address, 2)
Cells.Find(What:="partner_id", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Select
PartnerIDCol = ActiveCell.Column
PartnerIDLetter = Left(ActiveCell.Address, 2)
lastrow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
lastcol = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Column
For i = 2 To lastrow
PartnerID = Cells(i, PartnerIDCol).Value
filepath = Cells(i, PartnerCheckCol).Value
filename = Split(filepath, "\")
filename = filename(UBound(filename)) <<<<<----- this is where the error highlight
PartnerName = filenameconvert(filename)
Cells(i, PartnerCheckCol).Value = PartnerName
Next i
End Sub
Public Function filenameconvert(filename As Variant) As Variant
Dim i As Long
Dim PayoutReportFileNameCol, NametobeAssociatedCol As Long
Dim PayoutReportFileNameLetter, NametobeAssociatedLetter As String
Dim Dealfilename As Variant
Dim DealFileNames As Variant
Dim DealFileName_array As Variant
Sheets("Meta1").Select
Cells.Find(What:="Deal_Report_Filename", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Select
DealReportFileNameCol = ActiveCell.Column
DealReportFileNameLetter = Left(ActiveCell.Address, 2)
Cells.Find(What:="Name_to_be_Associated", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Select
NametobeAssoicatedCol = ActiveCell.Column
NametobeAssoicatedletter = Left(ActiveCell.Address, 2)
DealReportFileNameLetterlastrow = Columns(DealReportFileNameLetter & ":" & DealReportFileNameLetter).Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
DealReportFileNameLetterlastcol = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Column
For i = 2 To DealReportFileNameLetterlastrow
DealFileNames = Replace(Trim(Cells(i, DealReportFileNameCol).Value), " ", "")
DealFileName_array = Split(DealFileNames, ",")
DealNametobeAssociated = Cells(i, NametobeAssoicatedletter).Value
For Each Dealfilename In DealFileName_array
If InStr(filename, DealFileNames) <> 0 Then
filenameconvert = DealNametobeAssociated
End If
Next
Next i
End Function
Bookmarks