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, Jul 29 '16 at 02:45
If you follow [THIS](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) strictly then all these problems will go away :) — Siddharth Rout, Jul 29 '16 at 03: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, Jul 29 '16 at 08:18
varWST1Dockets is a variable for a worksheet name which is stored as string. I use var for strings WS for worksheets and then the rest is usually an abbreviation of the name. It helps me to follow what I'm doing when I have to go back and make adjustments. — Helen Rolls, Jul 31 '16 at 02:43
@Siddarth Rout I had a read through your references. I didn't know .select caused so many problems. I will pay more attention to eliminating it as much as possible. Yesterday I learnt that you can put the paste location in the same line as a copy, can't wait to try that out. — Helen Rolls, Jul 31 '16 at 02:44

score 1 · Accepted Answer · answered Jul 29 '16 at 04:02

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 '16 at 04:02

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 Jul 29 '16 at 04:30
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. – Helen Rolls Jul 29 '16 at 08:16
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? – Jul 29 '16 at 08:21
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. – Helen Rolls Jul 29 '16 at 08:22
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`. – Jul 29 '16 at 08:35
That print code is genius. Result: $B$11:$T$11. Which is correct. So I don't understand why the formatting isn't beling applied to this code: With rHeader.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = -4300032 .PatternTintAndShade = 0 End With – Helen Rolls Jul 31 '16 at 02:39
I can't tell you how much I appreciate the help. Learning from your examples is so much fun. I wish work was this much fun every day! – Helen Rolls Jul 31 '16 at 02:41
Lol..glad to hear that!! I just tested the code again. Everything worked as expected. Do you have any conditional formatting on that worksheet? I would create a new worksheet and add some test data; then test the code on it. – Jul 31 '16 at 02:47
Scratch what I said about the variable not applying the formatting. I've closed everything and reopened it, and everything seems to work now. I can't thank you enough, I have learnt so much and will be a better programmer for it. On to the next problem! – Helen Rolls Jul 31 '16 at 02:59
@HelenRolls You might be interested in my answer to [use getelement to get class with several values](http://stackoverflow.com/questions/38676526/use-getelement-to-get-class-with-several-values/38681973#38681973). I show a couple of different ways to dill down into a collection of HTML elements, it's children and their children elements. These same methods can be applied to drilling down into objects such as Worksheets, Ranges, and their properties. – Jul 31 '16 at 08:07
I'm afraid I got lost at span... :S – Helen Rolls Aug 02 '16 at 06:36
lol..it's easy to do. Basically, when you have elements nested inside other elements; I set a reference to one of the elements (e.g. Set e = ) and set a breakpoint. Then I'll run the code and find e in the Locals Window of the VBE. Next step is to, drill down into the properties of e until I find a reference to the next element or property that I want to test. While in break mode, I use the Immediate Window to test, whether the reference of property in question, will give me the results I want. – Aug 02 '16 at 06:52
Man, you completely lost me again LOL. Simple for you. I am afraid I learn best when I'm trying to solve a problem and some genius like yourself shows me a better way to do it as per above. What kind of problem does this solve? – Helen Rolls Aug 02 '16 at 22:37

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

1 Answers1