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
...