Vista - a little problem with .Range(.Cells(2,1),.Cells(2,49)).

Hi

This work fine:
dim i: for i= 1 to 49: oExcelSheet.cells(2,i)= i: next
With oExcelSheet
.Range("A2:AW2").Font.Size= 8
.Range("A2:AW2").Font.Bold= false
.Range("A2:AW2").Font.Name= "Arial"
.Range("A2:AW2").Font.Color= RGB(0,0,0)
.Range(.Cells(2,1),.Cells(2,49)).select
End With
wScript.quit

BUT after I looped like this:
for i= 1 to 49: oExcelSheet.cells(2,i)= i: next

I then figured out that the line
oExcelSheet.Range("A2:AW2").Font.Size= 8
would make all the 49 values fontsize 8.
Now since it was not obvious that the last
value should be AW2 for column 49, I think
it would be better to use the
oExcelSheet.Range(.Cells(2,1),.Cells(2,49)).select
instead where one only have to specify the upper
left corner, and the lower right corner of the cells retangel.

The problem now is that I can't get a border around the
.Range(.Cells(2,1),.Cells(2,49)).select
I tried
.Range(.Cells(2,1),.Cells(2,49)).border= true
and a lot of other syntax but it doesn't make a border ?

Benny
www.fineraw.com

On Mar 10, 3:21*am, Benny Pedersen <b.peder...@xxxxxx> wrote:I used the Record Macro feature of Excel to provide the information
needed to construct this solution:

Sub Macro1(ULCell, LRCell)
'
' Macro1 Macro
' Macro recorded 3/10/2009 by Tom Lavedas
'
Const xlEdgeLeft = 7, xlEdgeRight = 10, xlContinuous =1 _
xlThin = 2, xlThick = 4, xlAutomatic = -4105
'
With oExcelSheet.Range(ULCell, LRCell)
For i = xlEdgeLeft To xlEdgeRight
With .Borders(i)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Next
End With
End Sub

It is not the exact macro, far from it, actually. It's just the result
of the information quickly derived from the macro that Excel
provided. It doesn't always work out as easy as this one, but usually
it is faster than trying to search through the arcane Excel (Word/
PowerPoint/Project/Access) document object model(s).

Tom Lavedas
***********
http://there.is.no.more/tglbatch/

Thanks Tom.
I will try this later today or tomorrow. I have never used an Excel
Macro, etc.,
so I hope I'm able to figure it out for VBS files.

Benny,
PS: Back later...
I maybe have to test this with interior.colorIndex all over the sheet
because
all the cells already have a border. So even if I had found the
correct syntax, I wouldn't be able to
see a border already there. hmm, maybe a thick border would do it...

On Mar 10, 3:07*pm, T Lavedas <tglba...@xxxxxx> wrote:

On Mar 10, 12:46*pm, Benny Pedersen <b.peder...@xxxxxx> wrote:I just noticed that this statement is missing a comma ...

Const xlEdgeLeft = 7, xlEdgeRight = 10, xlContinuous =1 _
xlThin = 2, xlThick = 4, xlAutomatic = -4105

It should read ...

Const xlEdgeLeft = 7, xlEdgeRight = 10, xlContinuous =1, _
xlThin = 2, xlThick = 4, xlAutomatic = -4105

Tom Lavedas
***********
http://there.is.no.more/tglbatch/

Thanks - After I saw your code, it now worked at once in first
experiment like this:

.Range(.Cells(5,2),.Cells(5,9)).borders.lineStyle= 1
.Range(.Cells(5,2),.Cells(5,9)).borders.weight= 3

The "i" in the border syntax (round brackets) in
".borders(i)" also worked... I then tried the Macro stuff.

Hmm - in other words, I would now see
the Const Values if I modified the macro (Alt+F8), and wrote this:

Dim i: i= 0
i= i +1: Range("A" & i).FormulaR1C1= "const xlNone= " & xlNone
i= i +1: Range("A" & i).FormulaR1C1= "const xlMedium= " & xlMedium

Then I just had to copy those cells over to the VBS file. Tom, I bet
you didn't told me that, because you already knew that
I would be able to figure that out meself

Benny,
PS: Yesterday, I shot an ugly fish. Here:
http://www.elementsvillage.com/forum...002#post446002


On Mar 10, 6:30*pm, T Lavedas <tglba...@xxxxxx> wrote:

Hey, the make macro possibility is very smart. Most (maybe all) the
syntax we used in VBS files written in Notepad, can be executed
in the macro instead The only bad thing about the Microsoft Visual
Basic Editor is that it transform msgBox to uppercase MsgBox, etc...,
and the macro is gone when the sheet is closed.

Sub Makro1()
Cells.Select
Selection.Clear
With Selection.Interior
.ColorIndex = 2: .Pattern = xlSolid
End With

Range("C3:F8").Select

Dim i
For i = xlEdgeLeft To xlEdgeRight
With Selection.Borders(i)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Next
With Selection.Borders
For i = xlDiagonalDown To xlDiagonalUp + 2
Selection.Borders(i + (i > 6) * -4).LineStyle = xlNone
MsgBox "value " & i + (i > 6) * -4, 4096, "info"
Next
End With

Range("A1").Select
End Sub

On Mar 11, 7:22*pm, Benny Pedersen <b.peder...@xxxxxx> wrote: