Thread: Selecting range with variable

Hi everyone, this is my first post. Thanks in advance for any help. I used to do extensive Lotus macro programming years ago, and some with older Excel, but am relatively new to VBA used with Excel 2007, so I'm flying by the seat of my pants somewhat with this.

I'm trying to write a macro in Excel 2007 that includes selecting a horizontal range of cells and then putting a border along the bottom of all cells in the range. But for some reason, everything I try gives me an error, usually #1004 "Application-defined or object-defined error." I've tried several different ways of selecting the range, including methods from Microsoft's own support website, but nothing seems to work and I can't for the life of me figure out what I'm doing wrong.

The columns are always the same, but the row being selected changes and so I need to use a variable for the row index. I'll put a number of attempts I've done, below, and what the results were.

In the attempt below, LineRow is a named cell in the spreadsheet that keeps track of which row is to get the border along the bottom. I tried just using LineRow as the row index (returned an error) so I tried using a local variable that takes LineRow's value (also returned an error). Either way, it bombs as soon as it gets to the Application.GoTo statement with a 1004 error.

z = LineRow
    For i = 1 To 9
    Application.Goto Cells(z, i)
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ThemeColor = 3
        .TintAndShade = -0.499984740745262
        .Weight = xlThick
    End With
    Next i

In the next attempt (below), LineRange is a spreadsheet cell that contains a concatenated range (e.g. A1:I16). This works when I set the PrintArea at the end of the macro, but with the code below, all that happens is that the cell named LineRange gets a border at the bottom. At least it doesn't bomb but the effect is definitely not what I'm looking for.

    Range("LineRange").Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ThemeColor = 3
        .TintAndShade = -0.499984740745262
        .Weight = xlThick
    End With

In the last attempt I'll post (below), I finally took the Select statement right from the MS support site and put in my own cell numbers - LineRow being the row number, with hard-coded column numbers. As with the others, as soon as it gets to the Select statement, it bombs with a 1004 error.

ActiveSheet.Range(Cells(LineRow, 1), Cells(LineRow, 9)).Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ThemeColor = 3
        .TintAndShade = -0.499984740745262
        .Weight = xlThick
    End With

I would greatly appreciate any help with this to understand what will finally work, as well as hopefully to understand why the above attempts do not. Thanks in advance!

Hi, and welcome aboard,

I too cut my teeth on the old Lotus \ macros, (or was it the / ) which preceded the dialect of VBA called Lotus Script that they eventually used.

Since you say your columns are always the same, then one way using your LineRange variable and assuming columns B:F

Range("B" & Range("LineRow") & ":F" & Range("LineRow")).Borders(xlEdgeBottom).Weight = xlThin

HTH

LineRow is a named cell in the spreadsheet that keeps track of which row is to get the border along the bottom

If you want to refer to a cell, either as A1 or with its defined range name, you need to use

z = Range("LineRow")
' or also
z = [Linerow]

This will return the value of the cell.

LineRange is a spreadsheet cell that contains a concatenated range (e.g. A1:I16). This works when I set the PrintArea at the end of the macro, but with the code below, all that happens is that the cell named LineRange gets a border at the bottom.

A cell can not contain a concatenated range. What exactly does the cell contain? Perhaps the text "A1:I16"? If so, you could use

Range(Range("linerange").Value).Select

not pretty, and there are most probably better ways for achieving that.

Your third code suffers from the same problem as the first.

ActiveSheet.Range(Cells([LineRow], 1), Cells([LineRow], 9)).Select

cheers

the square brackets are an alternative to addressing a range with

Range(<the range>)
' alternative
[<the range>]

Whatever works within the round brackets of the Range() also works within []

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save