From firing pan to the fire

Hi,
I have been thrown into the fire to correct this code. Being a novice programmer at best, please be gentle. On the up side since the code has worked for the past 12 months and no changes have been made it should be an easy fix. So here is my problem.
I have an VB macro in Excel which reformats a txt report (created from our accounting system) that is giving the following error when ran:
"Method Range' of object'_Global' failed”. Debugger is highlighting the line range(strRange).Select with curser at beginning of line. I've found if I manually delete the last two rows in the unformatted spreadsheet, which is junk data, it runs without error. However since the code is programmed to delete last two lines this erases two lines of good data.

Here is the complete sub routine of question.

Private Sub FormatTotalLines()
Dim x As Long
Dim lngRow As Long
Dim strRange As String
Dim sJobCode As String

For x = 1 To MyTotalRows.Count
lngRow = MyTotalRows.Item(x).TotalRow
strRange = "A" & CStr(lngRow) & ":" & "AA" & CStr(lngRow)
range(strRange).Select

TotalLineCells
MyTotalRows.Item(x).ColTotals

sJobCode = range("C" & CStr(lngRow - 1)).Value
range("B" & CStr(lngRow)).Value = "Sub-Total for " & sJobCode
range("B" & CStr(lngRow)).Font.Bold = True
Next
End Sub

What does "Method Range' of object'_Global' failed" mean and any suggestions to resolve would be greatly appreciated.
Thanks in advance

Well, it's freaking on you trying to set the range to strRange. Use the debug window to see what the value of strRange is.

The way you do this is by running the code and waiting for it to give you the error. Then there should be a button on the toolbar that looks like a square with an exclamation mark in it. Click that and look for strRange. Knowing what that is will help.

Where do the values in the last two cells (the ones causing the problems) come from? I'm thinking the last two cells are just holding information.

When you get the XX Method of _Globals failed it means you have an unqualified reference to an Excel Object, method or property. This errors has made me crazy more than once because often, the code works the first time, but not the second.

(Details: Microsoft KB Article#'s 189618 or 178510)

You need to fully qualify the references to the Range object

Something like this

dim oExcel as Object
dim oBook as Object
dim oSheet as Object
dim strRange as String

'Open Excel
Set oExcel = CreateObject("Excel.Application")
'Open a new Workbook
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.ActiveSheet

strRange = "A1:AA:2"
oSheet.range(strRange).Select

' another way to reference it, using cells that also need to be qualified
oSheet.Range(oSheet.Cells(1,1),oSheet.Cells(2,27)).select

Good Luck

Well, it's freaking on you trying to set the range to strRange. Use the debug window to see what the value of strRange is.

The way you do this is by running the code and waiting for it to give you the error. Then there should be a button on the toolbar that looks like a square with an exclamation mark in it. Click that and look for strRange. Knowing what that is will help.

Where do the values in the last two cells (the ones causing the problems) come from? I'm thinking the last two cells are just holding information.


strRange = A65537:AA65537 at time of error. This more than covers the entire extent of my data which ends at cell P270.

The values in the last two rows (as well as all the data) are coming from our Accounting system. We export a txt file out of our Accounting System than open it in Excell and run this VB script to format it.