Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range.(Cells()) question
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range.(Cells()) question
Expand the with:
With xlsheet.Range(.Cells(r, 1), .Cells(r, 4)) ..MergeCells = True End With etc. -- Gary's Student " wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range.(Cells()) question
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 -- Regards, Tom Ogilvy wrote in message oups.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range.(Cells()) question
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Question about a range of cells | Excel Discussion (Misc queries) | |||
how to compute a range of cells based on another range of cells? | Excel Worksheet Functions | |||
how to compute a range of cells based on another range of cells? | Excel Worksheet Functions | |||
Range Question / error 1004: method Range of object Worksheet has failed | Excel Programming | |||
Range.Formula and Range question using Excel Automation | Excel Programming |