0
votes

Working within 2 worbook i.e (new vs old). I'm trying to use vlookup formula old vs new workbook to find out if the ID is missing.Below is the code use to run the macros.

Set OB1 = Application.Workbooks.Open(FTO_New_BRNO)

    name1 = ActiveWorkbook.name
    sheet_name1 = ActiveWorkbook.Sheets(1).name

    Set OB2 = Application.Workbooks.Open(FTO_Old_Brno)

   ' name2 = ActiveWorkbook.name
   ' sheet_name2 = ActiveWorkbook.Sheets(1).name



    OB2.Worksheets(1).Activate
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.NumberFormat = "General"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Compare With New Brno Tracker"
    OB2.Worksheets(1).Activate
    Range("C3").Select
     Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],[" & name1 & "]" & sheet_name1 & "!C1,1,0)"

The above vlookup formula gives me a below error

Runtime Error 1004 - "Run-time error '1004': Application-defined or object-defined error"
1
If the purpose is to find out if an ID is missing you expect your formula to return an error and then take action. However, by placing the VLOOKUP in the cell the error, once you insert the function correctly, will be produced in the worksheet where you can't take action. Try using VBA's Find method to look for the ID and then use the found range's Row property to retrieve the ID.Variatus
@GSerg Thanks for your help. I've made slight tweak in my code and posted my code in answer section.ROn
@Variatus Thanks for your help. I've made slight tweak in my code and posted my code in answer section.ROn

1 Answers

0
votes

Thanks for your help. I found out the solutions. There is slight tweak in macro code as shown in below updated code. Finally it worked.

Old code

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],[" & name1 & "]" & sheet_name1 & "!C1,1,0)"

New code

ActiveCell.FormulaR1C1 = "=VLOOKUP(C[-2],'[" & name1 & "]" & sheet_name1 & "'!C1,1,0)"