Method 'Range' of object '_Global' failed

[notme23]

I have a macro I am writing that I cannot seem to fix. I know this has been asked and I have read through some of the responses but just cant get it to work. i'm open for suggestions.

Dim c
For Each c In Range("B2:ZZ2").Cells
        If c = "" Then
           c.Select
            Exit For
        End If
    Next

' count back 3, select column and insert column
ActiveCell.Offset(0, -3).Select
Columns("B:ActiveCell").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

 

[Rick Rothstein]

I have a macro I am writing that I cannot seem to fix. I know this has been asked and I have read through some of the responses but just cant get it to work. i'm open for suggestions.

Dim c
For Each c In Range("B2:ZZ2").Cells
If c = "" Then
c.Select
Exit For
End If
Next

' count back 3, select column and insert column
ActiveCell.Offset(0, -3).Select
Columns("B:ActiveCell").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

It would have helped if you told us the line of code that was highlighted by VB when it raised the error. I'm guessing it was the one I show in red above. If the blank cell found in the loop is in Columns B or C, then when you attempt to offset -3 columns, you would be asking Excel to select a cell that is before Column A which, of course, can't happen, so I am guessing that is causing your error.

 

[notme23]

Sorry I thought the title might answer that question. Excel is failing on the Range line:

For Each c In Range("B2:ZZ2").Cells

 

[Rick Rothstein]

Sorry I thought the title might answer that question. Excel is failing on the Range line:

For Each c In Range("B2:ZZ2").Cells

If you are using XL2007 or above, then you appear to have opened the file in Compatibility Mode. This will make Column IV the last column available so your use of Column ZZ in the Range call in your For Each line of code is failing because there is no Column ZZ. If you are using XL2003, then you are not in Compatibility Mode, but you have the same column limitations and, hence, are failing for the same reason.

 

[RoryA]

Also note that this won't work:

Columns("B:ActiveCell").Select

It's not clear which cell(s) you are actually hoping to select there?

 

[notme23]

I will fix the "Columns("B:ActiveCell").Select" but I am still having problems with the "For Each c In Range("B2:ZZ2").Cells" line. If I try to run this with multiple workbooks open it still fails the range. I guess I don't know how to define it properly. The loop runs fine in other macros.

I am using O365 so I definitely have a ZZ column.

 

[RoryA]

The version of Office won't make any difference if the active sheet is in an xls format workbook.

Which module is the code in?

 

[notme23]

OK so I think I figured it out. at least part. I had the macro in a test file but was trying to apply it to another workbook. I had the workbook open and active but had the module on the other spreadsheet. I have a macro workbook I make as an Add-in and run all my macros out of. Anyway I can explicitly call the workbook in the range so this wouldn't matter?

 

[RoryA]

Yes, like this:

For Each c In Workbooks("workbook name.xlsx").Sheets("sheet name").Range("B2:ZZ2").Cells