Excel VBA Range Variable Throws Error 1004 - I'm in

Question

I need your help please. I am new to using ranges as variables, so there maybe something obvious I'm missing but I can't seem to find a solution after a lot of googling.

I am formatting four sheets of data (headings, pretty fill colour, nice borders). They are all pretty much the same, but they have a varying number of columns. To save repetitious code I've written one procedure to do the formatting and another to change the variables and call the formatting code.

sample of the calling code:

' Set Customer detail variables.

varGlobalID = Sheets(varWST1Dockets).Cells(2, 13).Value
varCustomerName = Sheets(varWST1Dockets).Cells(2, 14).Value

' Format Suspended

' Set Variables

    varReportHeading = "Suspended Dockets Investigation"

    Set rngDataHeadings = Range("B11", "T11")

    Range("B1048576").End(xlUp).Select
    Set rngDataTable = Range(Selection, "T11")

    Range("B1048576").End(xlUp).Select
    Set rngData = Range(Selection, "T12")

' Run Format Reports Procedure

    Sheets(varWSSuspended).Select
    Call FormatReports

sample of formatting code

' Format Data Headings

rngDataHeadings.Select
With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .Color = -4300032
    .PatternTintAndShade = 0
End With
With Selection.Font
    .ColorIndex = 2
    .TintAndShade = 0
    .Bold = True
End With
With Selection
    .HorizontalAlignment = xlLeft
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With

' Apply Borders

rngDataTable.Select

With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ColorIndex = 2
    .TintAndShade = 0
    .Weight = xlMedium
End With

The code seems to work on the first run of the variables but not the second. Do I need to unload them before resetting? Or am I doing something else stupidly obviously wrong?

Thanks in advance.

Step through it with F8 ... what line do you get the error on? — Rodger
– Rodger, Commented Jul 29, 2016 at 2:45
If you follow THIS strictly then all these problems will go away :) — Siddharth Rout
– Siddharth Rout, Commented Jul 29, 2016 at 3:50
Thanks Thomas and Siddharth. Using select seems to be the root of the problem. Although Thomas' solution below has expanded my horizons in more efficient ways of going about this problem. — Helen Rolls
– Helen Rolls, Commented Jul 29, 2016 at 8:18

user6432984 · Accepted Answer · 2016-07-29 04:02:00Z

1

Set rngDataHeadings = Range("B11", "T11") references B11:T11 of the ActiveSheet. Selecting another worksheet and try rngDataHeadings.Select will throw an exception Runtime Error '1004' Select method of Range class failed

It's best to avoid Select and Active. You should watch Selecting Cells (Range, Cells, Activecell, End, Offset)

If you have standard tables this will work.

Sub FormatTable(wsWorksheet As Worksheet, HeaderAddress As String)
    Dim rDataBody As Range
    Dim rHeader As Range

    With wsWorksheet
        Set rHeader = .Range(HeaderAddress, .Range(HeaderAddress).End(xlToRight))
        Set rDataBody = Range(HeaderAddress).CurrentRegion
        Set rDataBody = rDataBody.Offset(1).Resize(rDataBody.Rows.Count - 1)
    End With
    With rHeader.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = -4300032
        .PatternTintAndShade = 0
    End With
    With rHeader.Font
        .ColorIndex = 2
        .TintAndShade = 0
        .Bold = True
    End With
    With rHeader
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

    ' Apply Borders
    With rDataBody.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 2
        .TintAndShade = 0
        .Weight = xlMedium
    End With

End Sub

Call it like this

FormatTable Worksheets("Sheet1"), "B11"

answered Jul 29, 2016 at 4:02

user6432984

Sign up to request clarification or add additional context in comments.

13 Comments

Helen Rolls Over a year ago

Thanks Thomas. I think I understand what you're suggesting. I will give it a go. I am self taught so there is a lot I don't know :s.

Helen Rolls Over a year ago

Thanks Thomas. That is working really well. And I learnt a lot by the way you set it up. Particularly with selecting the ranges. I have one issue. Though, the rHeader does not appear to be selecting anything. rDataBody is working brilliantly though. Any ideas? That link was interesting although it covered much of what I already know. I have subscribed to them to come back and watch. I also have someone I'm teaching and this would be handy to send her too. Thanks.

user6432984 Over a year ago

Can you put this line of code Debug.Print rHeader.Address after the rHeader range is set? Then can you check the immediate window for the results and post it back to me in a comment?

Helen Rolls Over a year ago

Another question in the line: Set rHeader = .Range(HeaderAddress, .Range(HeaderAddress).End(xlToRight)) Why do you use .Range when you don't in the rDataBody. I can see it throws an error if you take it out, I'm curious to understand why it works differently in the two scenarios.

user6432984 Over a year ago

After a range variable is set it will always refer back to the worksheet of the cells that it references. Many objects have references to their parent and child objects. For example test this in the immediate window Range("A1").Worksheet.Parent.Name.

|

Collectives™ on Stack Overflow

Excel VBA Range Variable Throws Error 1004 - I'm in

1 Answer 1

13 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related