Range.(Cells()) question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jmartinez.pincheira@gmail.com - 21 Mar 2006 02:50 GMT

Hi everyone, this is my first post. I would appreciate it if you tell
me how to do this in a more "elegant" way. I imagine that exist a more
sinthetic form to do this without specifying the range every time.

r is set previously in the code

With xlsheet
.Range(.Cells(r, 1), .Cells(r, 4)).MergeCells = True
.Range(.Cells(r, 1), .Cells(r, 4)).HorizontalAlignment = xlCenter
.Range(.Cells(r, 1), .Cells(r, 4)).VerticalAlignment = xlCenter
.Range(.Cells(r, 1), .Cells(r, 4)).Interior.ColorIndex = 53
.Range(.Cells(r, 1), .Cells(r, 4)).Interior.Pattern = xlSolid
.Range(.Cells(r, 1), .Cells(r, 4)).Font.Size = 8
.Range(.Cells(r, 1), .Cells(r, 4)).Font.ColorIndex = 2
.Range(.Cells(r, 1), .Cells(r, 4)).Value = "label1"

.Range(.Cells(r, 5), .Cells(r, 6)).MergeCells = True
.Range(.Cells(r, 5), .Cells(r, 6)).HorizontalAlignment = xlCenter
.Range(.Cells(r, 5), .Cells(r, 6)).VerticalAlignment = xlCenter
.Range(.Cells(r, 5), .Cells(r, 6)).Interior.ColorIndex = 53
.Range(.Cells(r, 5), .Cells(r, 6)).Interior.Pattern = xlSolid
.Range(.Cells(r, 5), .Cells(r, 6)).Font.Size = 8
.Range(.Cells(r, 5), .Cells(r, 6)).Font.ColorIndex = 2
.Range(.Cells(r, 5), .Cells(r, 6)).Value = "label2"

.Range(.Cells(r, 7), .Cells(r, 8)).MergeCells = True
.Range(.Cells(r, 7), .Cells(r, 8)).HorizontalAlignment = xlCenter
.Range(.Cells(r, 7), .Cells(r, 8)).VerticalAlignment = xlCenter
.Range(.Cells(r, 7), .Cells(r, 8)).Interior.ColorIndex = 53
.Range(.Cells(r, 7), .Cells(r, 8)).Interior.Pattern = xlSolid
.Range(.Cells(r, 7), .Cells(r, 8)).Font.Size = 8
.Range(.Cells(r, 7), .Cells(r, 8)).Font.ColorIndex = 2
.Range(.Cells(r, 7), .Cells(r, 8)).Value = "label3"

End With

Thanks

Javier Martinez

Reply to this Message

Gary''s Student - 21 Mar 2006 03:16 GMT

Expand the with:

With xlsheet.Range(.Cells(r, 1), .Cells(r, 4))
.MergeCells = True
End With

etc.

Signature

Gary's Student

> Hi everyone, this is my first post. I would appreciate it if you tell
> me how to do this in a more "elegant" way. I imagine that exist a more
[quoted text clipped - 35 lines]
>
> Javier Martinez

Reply to this Message

Tom Ogilvy - 21 Mar 2006 03:50 GMT

Dim v as Variant, v1 as Variant, j as Long
Dim i as Long
v = Array(1,5,7)
v1 = Array(3,2,2)
j = 0
for i = lbound(v) to ubound(v)
j = j + 1
With xlsheet
with .Cells(r, v(i)).Resize(1,v1(i))
.MergeCells = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Interior.ColorIndex = 53
.Interior.Pattern = xlSolid
.Font.Size = 8
.Font.ColorIndex = 2
.Value = "label" & j
End With
End With
Next

Signature

Regards,
Tom Ogilvy

> Hi everyone, this is my first post. I would appreciate it if you tell
> me how to do this in a more "elegant" way. I imagine that exist a more
[quoted text clipped - 35 lines]
>
> Javier Martinez

Reply to this Message

jmartinez.pincheira@gmail.com - 21 Mar 2006 05:30 GMT

that was fast!!

Thank you very much, it works perfect. I used the solution of Tom,
modified to put different labels from an array

Javier Martinez

Reply to this Message