0

I wrote a VBA code to compare data in two Excel workbooks. So I need to open two Excel files. As shown below, the line Set rng = wksh.Range(Cells(59, 1), Cells(66, 12)) at the bottom of the section triggers the run time error 1004. If I move the line up and before the section for opening the 2nd file, the error disappears. I am totally puzzled. Any suggestions are appreciated...

Note rng is defined as Range. Also, no issue with passing the Range references(wksh.Range) to function getStartLoc() and getEndLoc().

'Open first workbook
  Set wb = Workbooks.Open(sFile)   
  Set wksh = wb.Worksheets("sheet1")
...
  lr = 0
  lr = wksh.UsedRange.Rows.Count
  Set Start_Loc = getStartLoc(wksh.Range(Cells(1, 1), Cells(lr, 4)))
  Set End_Loc = getEndLoc(wksh.Range(Cells(1, 1), Cells(lr, 4)), Start_Loc)
'
'''''TEST set range-PASSED here
 'Set rng = wksh.Range(Cells(59, 1), Cells(66, 12)) 
'
...
'Open 2nd workbook
  Set wb2 = Workbooks.Open(sFile2)   *emphasized text*
  Set wksh2 = wb2.Worksheets("mysheet")
...
  lr2 = wksh2.UsedRange.Rows.Count
  Set Start_Loc2 = getStartLoc(wksh2.Range(Cells(1, 1), Cells(lr, 4)))
  Set End_Loc2 = getEndLoc(wksh2.Range(Cells(1, 1), Cells(lr, 4)), Start_Loc2)
'
'''''TEST set range-FAILED with run time error '1004': Method 'Range' of object '_worksheet' failed here
  Set rng = wksh.Range(Cells(59, 1), Cells(66, 12)) '''''TEST set range
...
  • 1
    Never mind. I just found the solution. With two worksheets open, I need to associate the cells with the worksheet. The error disappears when replacing the line Set rng = wksh.Range(Cells(59, 1), Cells(66, 12)) with Set rng = wksh.Range(wksh.Cells(59, 1), wksh.Cells(66, 12)) – user20160048 Oct 18 '22 at 21:41

0 Answers0