0

I have a list of workbook names in Column "F", I'm getting "Object variable or with block variable not set error 91" error on the 3rd workbook which is exactly the same as 1st workbook.

Sub Required_Files()

vb = ActiveWorkbook.Name

For i = 2 To Range("G1").Value

fn = Range("A" & i).Value & " " & Range("D" & i).Value & " " & Range("E" & i).Value

Workbooks.Open Range("F" & i).Value

Range("A1").Select

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy

Selection.End(xlDown).Select

Selection.Offset(2, 0).Select

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
        
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete

Rows("1").Select

Selection.EntireRow.Delete

lc = Cells(1, Columns.Count).End(xlToLeft).Column
    
Cells(100, lc).Select

ActiveCell.Value = 1
    
Range("B1").Select
    
Columns("B:B").Select

    Selection.Find(What:="Grand Total", After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

a = ActiveCell.Address(0, 0)

Range("B1").Select

On Error GoTo er

Columns("B:B").Select

    Selection.Find(What:="Opt Total", After:=ActiveCell, LookIn:=xlFormulas _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate



b = ActiveCell.Address(0, 0)


Range("B100").Value = "=IfError(" & a & "-" & b & ",0)"

chk:

Range("B100").Select
Selection.Copy

Range(Selection, Selection.End(xlToRight)).Select

Selection.PasteSpecial xlPasteFormulas

Range("B100").Select
ActiveCell.Value = "New Total"


ActiveWorkbook.SaveAs Filename:= fn & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

ActiveWindow.Close (True)

Workbooks(vb).Activate

Next i

MsgBox "Files are ready"


er:

Range("B100").Value = "=" & a

GoTo chk

End Sub

While 3rd workbook is executed, this is where I'm getting the error


Selection.Find(What:="Opt Total", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
 MatchCase:=False, SearchFormat:=False).Activate

Codes are running exactly as required for the 1st and 2nd workbook which has both conditions but the 3rd workbook which is exactly the same as the 1st workbook shows an error

Farooq
  • 3
  • 2
  • 3
    That means that "Opt Total" was not found in `Selection`. It would be good to [avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba), and to [test if the Find succeeded](https://stackoverflow.com/questions/1589939/how-to-detect-whether-vba-excel-found-something). – BigBen Jul 29 '22 at 12:56
  • Right, but for such case, i have used "onerror go to er", also as I mentioned even the first workbook in the list doesn't have "Opt Total", but the code work as expected, 2nd workbook has both "Grand Total" and "Opt Total" again code work as expected, it's only the 3rd where it throws error – Farooq Jul 29 '22 at 13:03
  • 3
    You've got spaghetti code. The right way to do this is in the second link provided. – BigBen Jul 29 '22 at 13:04

0 Answers0