I wanted to shorten the macro-made format code:

I used this hoping to shorten the amount of code needed to format the cells,
but it gives me a type mismatch. I am also hoping to use Arrays with text in
them for many other areas of my code. Is this possible?

Set format_range = Worksheets("Surface").Range("A5",
cell_range.Offset(cell_count1 - 1, 10))
format_borders = Array("xlEdgeLeft", "xlEdgeTop", "xlEdgeBottom",
"xlEdgeRight")

Dim c As Long

For c = 0 To 3
With format_range.Borders(format_borders(c))
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
Next c


What am I doing wrong? Thanks.

the following consnts are number, you made them strings". Remove the double
quotes.

format_borders = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight)


"baconcow" wrote:

> I wanted to shorten the macro-made format code:
>
> I used this hoping to shorten the amount of code needed to format the cells,
> but it gives me a type mismatch. I am also hoping to use Arrays with text in
> them for many other areas of my code. Is this possible?
>
> Set format_range = Worksheets("Surface").Range("A5",
> cell_range.Offset(cell_count1 - 1, 10))
> format_borders = Array("xlEdgeLeft", "xlEdgeTop", "xlEdgeBottom",
> "xlEdgeRight")
>
> Dim c As Long
>
> For c = 0 To 3
> With format_range.Borders(format_borders(c))
> .LineStyle = xlContinuous
> .ColorIndex = xlAutomatic
> .TintAndShade = 0
> .Weight = xlMedium
> End With
> Next c
>
>
> What am I doing wrong? Thanks.

Thanks, I didn't realized they were numbers. That makes the rest of what I
was doing work!

"Joel" wrote:

> the following consnts are number, you made them strings". Remove the double
> quotes.
>
> format_borders = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight)
>
>
> "baconcow" wrote:
>
> > I wanted to shorten the macro-made format code:
> >
> > I used this hoping to shorten the amount of code needed to format the cells,
> > but it gives me a type mismatch. I am also hoping to use Arrays with text in
> > them for many other areas of my code. Is this possible?
> >
> > Set format_range = Worksheets("Surface").Range("A5",
> > cell_range.Offset(cell_count1 - 1, 10))
> > format_borders = Array("xlEdgeLeft", "xlEdgeTop", "xlEdgeBottom",
> > "xlEdgeRight")
> >
> > Dim c As Long
> >
> > For c = 0 To 3
> > With format_range.Borders(format_borders(c))
> > .LineStyle = xlContinuous
> > .ColorIndex = xlAutomatic
> > .TintAndShade = 0
> > .Weight = xlMedium
> > End With
> > Next c
> >
> >
> > What am I doing wrong? Thanks.

Here is a little know secret.

right click on the VBA window and slect Object Browser. In the top of the
browser there is a search box. Insert xlEdgeLeft and press binoculars.. You
will see all the border constants. Click on each constant and you will see
there value at the bottom of the window Pane.

"baconcow" wrote:

> Thanks, I didn't realized they were numbers. That makes the rest of what I
> was doing work!
>
> "Joel" wrote:
>
> > the following consnts are number, you made them strings". Remove the double
> > quotes.
> >
> > format_borders = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight)
> >
> >
> > "baconcow" wrote:
> >
> > > I wanted to shorten the macro-made format code:
> > >
> > > I used this hoping to shorten the amount of code needed to format the cells,
> > > but it gives me a type mismatch. I am also hoping to use Arrays with text in
> > > them for many other areas of my code. Is this possible?
> > >
> > > Set format_range = Worksheets("Surface").Range("A5",
> > > cell_range.Offset(cell_count1 - 1, 10))
> > > format_borders = Array("xlEdgeLeft", "xlEdgeTop", "xlEdgeBottom",
> > > "xlEdgeRight")
> > >
> > > Dim c As Long
> > >
> > > For c = 0 To 3
> > > With format_range.Borders(format_borders(c))
> > > .LineStyle = xlContinuous
> > > .ColorIndex = xlAutomatic
> > > .TintAndShade = 0
> > > .Weight = xlMedium
> > > End With
> > > Next c
> > >
> > >
> > > What am I doing wrong? Thanks.

I've seen those numbers before but I did not know exactly what they
represented in terms of the code. So these are just constants that are built
into VBA? Interesting. Thanks again.

"Joel" wrote:

> Here is a little know secret.
>
> right click on the VBA window and slect Object Browser. In the top of the
> browser there is a search box. Insert xlEdgeLeft and press binoculars.. You
> will see all the border constants. Click on each constant and you will see
> there value at the bottom of the window Pane.
>
> "baconcow" wrote:
>
> > Thanks, I didn't realized they were numbers. That makes the rest of what I
> > was doing work!
> >
> > "Joel" wrote:
> >
> > > the following consnts are number, you made them strings". Remove the double
> > > quotes.
> > >
> > > format_borders = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight)
> > >
> > >
> > > "baconcow" wrote:
> > >
> > > > I wanted to shorten the macro-made format code:
> > > >
> > > > I used this hoping to shorten the amount of code needed to format the cells,
> > > > but it gives me a type mismatch. I am also hoping to use Arrays with text in
> > > > them for many other areas of my code. Is this possible?
> > > >
> > > > Set format_range = Worksheets("Surface").Range("A5",
> > > > cell_range.Offset(cell_count1 - 1, 10))
> > > > format_borders = Array("xlEdgeLeft", "xlEdgeTop", "xlEdgeBottom",
> > > > "xlEdgeRight")
> > > >
> > > > Dim c As Long
> > > >
> > > > For c = 0 To 3
> > > > With format_range.Borders(format_borders(c))
> > > > .LineStyle = xlContinuous
> > > > .ColorIndex = xlAutomatic
> > > > .TintAndShade = 0
> > > > .Weight = xlMedium
> > > > End With
> > > > Next c
> > > >
> > > >
> > > > What am I doing wrong? Thanks.