Using Offset on inactive sheet

[crookesa]

I am trying to run the following code but it only works when Sheet5 is active. Am I right in thinking that the Offset() property tries to select a range.
Is it possible use Offset() with the sheet being active?

    For Each named_range In ActiveWorkbook.Names
        If Left(named_range.Name, 6) <> "_xlfn." Then
            If named_range.RefersToRange.Parent.Name = ActiveSheet.Name Then
                With Sheet5.range(named_range.Name).Borders(xlInsideHorizontal)
                    .LineStyle = xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlHairline
                End With
                With Sheet5.range(named_range.Name).range(Cells(1, 1), Cells(Sheet5.range(named_range.Name).Rows.Count, 1)).Offset(0, 32).Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlMedium
                End With
            End If
        End If
    Next named_range

What I am trying to do is format the xlEdgeLeft border of the range of cells in the column after the range as sometimes I hide the last few columns of the range depending on the length of the month. This will always give me the border I need at the end of the range.

 

[JLGWhiz]

why not just;

Sheet5.named_range.Resize(, 32).BorderAround xlThin 'or whatever weight you want

 

[crookesa]

why not just;

Sheet5.named_range.Resize(, 32).BorderAround xlThin 'or whatever weight you want

thanks for the reply but I don’t think that’ll work as the 32nd column of the range is the one I hide at times and when’s it’s hidden the right border of the cell hides also. My intention is to create a left border on the 33rd cell(s) to make it appear the 32nd column border is visible.

 

[JLGWhiz]

OK, misinterpreted what you were doing. Regarding the Offset question, the way you are using the 'With Sheet5' statement should allow the statement to execute on Sheet5, whether it is the active sheet or not. But the problem you are having could be related to the named_range variable and the statement

If named_range.RefersToRange.Parent.Name = ActiveSheet.Name Then

because there is no way to know what the active sheet is unless you have specifically activated a sheet before beginning the loop.However, if you want to put borders around all your named ranges, then instead of specifying Sheet5, Just substiture 'ActiveSheet' for Sheet5 and it should work OK. Otherwise you would probably need another loop to test each sheet as the parent for the named range and then use a variable for that sheet to execute the code on.

 

[crookesa]

OK, misinterpreted what you were doing. Regarding the Offset question, the way you are using the 'With Sheet5' statement should allow the statement to execute on Sheet5, whether it is the active sheet or not. But the problem you are having could be related to the named_range variable and the statement

If named_range.RefersToRange.Parent.Name = ActiveSheet.Name Then

because there is no way to know what the active sheet is unless you have specifically activated a sheet before beginning the loop.However, if you want to put borders around all your named ranges, then instead of specifying Sheet5, Just substiture 'ActiveSheet' for Sheet5 and it should work OK. Otherwise you would probably need another loop to test each sheet as the parent for the named range and then use a variable for that sheet to execute the code on.

I had updated my code before you replied and now have it like below but am getting a run-time error '1004', Application-defined or object-defined error with the offset line of code highlighted.

    For Each named_range In ActiveWorkbook.Names
        If Left(named_range.Name, 6) <> "_xlfn." Then
            If named_range.RefersToRange.Parent.Name = Sheet5.Name Then
                With Sheet5.range(named_range.Name).Borders(xlInsideHorizontal)
                    .LineStyle = xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlHairline
                End With
                With Sheet5.range(named_range.Name).range(Cells(1, 1), Cells(Sheet5.range(named_range.Name).Rows.Count, 1)).Offset(0, 32).Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlMedium
                End With
            End If
        End If
    Next named_range

I did use Sheet5.Activate at the start of my sub but wanted to try and not have to activate a sheet. If it's not possible so be it but just trying to learn if there is a way.

 

[JLGWhiz]

If Left(named_range.Name, 6) <> "_xlfn." Then

This statement needs some work. The last six characters of a file name will never equal what you have here. File extenstions do not begin with Underscore and end with period. Also xlfn is a fictitious extension code.

The error you are getting could be related to your use of 'Sheet5' in the procedure. As used, that is the code name for a worksheet and may not represent Sheets("Sheet5") nor Sheets(5) and may not even exist in that workbook. Look in the vb editor in the Projects pane to see if it shows 'Sheet5 (Sheet5)'. If it does, then you are OK, if not you could have a problem. Tghe sheet code name, tab name and index can have the same number, but be three different sheets because of the way excel handles them. So it is important to be sure you are using the correct reference in code.

If you know the name you want to compare to, then it might be better to just use that sheet name like "Data" instead of Sheet5.Name.

The statement where you are getting the error does not appear to be a valid statement. It looks like two statements might have been inadvertantly combined, but I am not sure about how to unscramble them.

 

[crookesa]

If Left(named_range.Name, 6) <> "_xlfn." Then

This statement needs some work. The last six characters of a file name will never equal what you have here. File extenstions do not begin with Underscore and end with period. Also xlfn is a fictitious extension code.

This is used in conjunction with the for loop on the previous line, it's checking the name of the current named range in the for loop. If you use some formulas (SUMIFS, IFERROR and COUNTIFS) in Excel these named ranges are automatically generated and cause me errors so want to skip them. More info on this thread here.

The error you are getting could be related to your use of 'Sheet5' in the procedure. As used, that is the code name for a worksheet and may not represent Sheets("Sheet5") nor Sheets(5) and may not even exist in that workbook. Look in the vb editor in the Projects pane to see if it shows 'Sheet5 (Sheet5)'. If it does, then you are OK, if not you could have a problem. Tghe sheet code name, tab name and index can have the same number, but be three different sheets because of the way excel handles them. So it is important to be sure you are using the correct reference in code.

If you know the name you want to compare to, then it might be better to just use that sheet name like "Data" instead of Sheet5.Name.

The statement where you are getting the error does not appear to be a valid statement. It looks like two statements might have been inadvertantly combined, but I am not sure about how to unscramble them.

Sheet5 is the correct codename in my workbook so I think it's being referred to correctly. The previous with statement using the same object doesn't work out, it's only this one with the offset causing me the problems.

With Sheet5.range(named_range.Name).range(Cells(1, 1), Cells(Sheet5.range(named_range.Name).Rows.Count, 1)).Offset(0, 32).Borders(xlEdgeLeft)

 

[JLGWhiz]

Try

With Sheet5.range(named_range.Name).Sheet5.range(Sheet5.Cells(1, 1), Sheet5.Cells(Sheet5.range(named_range.Name).Rows.Count, 1)).Offset(0, 32).Borders(xlEdgeLeft)

 

[crookesa]

Try

With Sheet5.range(named_range.Name).Sheet5.range(Sheet5.Cells(1, 1), Sheet5.Cells(Sheet5.range(named_range.Name).Rows.Count, 1)).Offset(0, 32).Borders(xlEdgeLeft)

Sorry @JLGWhiz only getting back to you now was off for a few days. I've tried that and getting a different run time error now. It's run-time error 438 Object doesn't support this property or method.

Looks like the Sheet5.Activate will have to work for the time being.