VBA Code Casestudy:Automated V-lookup

In our office we have a huge database of sub-publisher group,we need to enter in excel worksheet and their corresponding publisher name. Here is a VBA code who matches sub-publisher from active workbook to Master data file of sub publisher's and give their corresponding publisher's name  in respective column
VBA code using v-lookup is as  mentioned below:



Function automatedVlookup()
    Dim pathname, vlookupformula, bookname, sheetname As String
    Dim displaycolumn As Integer
    Dim wb  As Workbook
    Dim mywb As String
 
    Dim myrng, displayrange, lookuprange  As Range
    Application.ScreenUpdating = False
    Dim cntrow, tempcount As Long
 
 
    On Error Resume Next
 
    'Selecting columnno. of Publisher name
 
 
     mywb = CStr(ActiveWorkbook.Name)
    'counting total no. of rows
     cntrow = ActiveWorkbook.Sheets(1).UsedRange.Rows.Count
 
    'selecting column of publisher subgroup
    Set myrng = Application.InputBox("Select column", "Sub publisher group", Type:=8)
    displaycolumn = CInt(Application.InputBox("Select column no. you want to display", "for Publisher group", Type:=2))
 
   
        pathname = frmfinal.temppathname.Value & "\pub group_new.xls"
     
        'openning publisher master file
     
        Set wb = Workbooks.Open(pathname)
       
        If Not wb Is Nothing Then
         
            Set lookuprange = wb.Sheets(1).UsedRange
            bookname = "pub group_new.xls"
            sheetname = wb.Sheets(1).Name
     
        tempcount = 1
 
            Do
                    Set myrng = myrng.Offset(1, 0)
                    Set displayrange = myrng
         
                    'setting lookup formula
                    vlookupformula = "=if(iserror(vlookup(" & CStr(myrng.Address) & ",'[" & bookname & "]" & sheetname & "'!" & CStr(lookuprange.Address) & ",2,0))," & Chr(34) & Chr(34) & "," & "vlookup(" & CStr(myrng.Address) & ",'[" & bookname & "]" & sheetname & "'!" & CStr(lookuprange.Address) & ",2,0))"
             
             
                    'writing vlookup formula
         
                    displayrange.Offset(0, displaycolumn).Formula = vlookupformula
             
                    tempcount = tempcount + 1
            Loop Until tempcount = cntrow
        Else
     
            MsgBox "Publisher file Not found"
        End If
        wb.Close savechanges:=False
        Set wb = Nothing
        Unload frmfinal
        Application.ScreenUpdating = True
 

End Function