Condensing Format Code
 

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.

Condensing Format Code
 

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.

Condensing Format Code
 

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.

Condensing Format Code
 

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.

Condensing Format Code
 

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.