Run Time Error Method Rangeof Object Global Failed

You have code that imports a text file each day. You expect the text file to end with a total row. After importing the text, you want to convert all the detail rows to italics.

The following code works fine for months:

Sub SetReportInItalics()

TotalRow = Range("A65536").End(xlUp).Row FinalRow = TotalRow - 1

Range("A1:A" & FinalRow).Font.Italic = True End Sub

Then, one day the client calls with the error message shown in Figure 23.9.

Figure 23.9

The Runtime Error 1004 can be caused by a myriad of things.

Figure 23.9

The Runtime Error 1004 can be caused by a myriad of things.

Upon examination of the code, you discover that something bizarre went wrong when the text file was FTP'ed to the client that day. The text file ended up as an empty file. Because the worksheet was empty, TotalRow was determined to be Row 1. When we assumed the last detail row was TotalRow - 1, this set our code up to attempt to format Row 0, which clearly does not exist.

After this episode, you will find yourself writing code that preemptively looks for this situation:

Sub SetReportInItalics()

TotalRow = Range("A65536").End(xlUp).Row FinalRow = TotalRow - 1 If FinalRow > 0 Then

Range("A1:A" & FinalRow).Font.Italic = True

Else

MsgBox "It appears the file is empty today. Check the FTP process" End If End Sub

0 0

Post a comment