Range.(Cells()) question

[jmartinez.pincheira]

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

 

[Gary''s Student]

Expand the with:

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

etc.

 

[Tom Ogilvy]

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

 

[jmartinez.pincheira]

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