Re: a little problem with .Range(.Cells(2,1),.Cells(2,49)).

Tech-Archive recommends: Fix windows errors by optimizing your registry


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 *** 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...@xxxxxxxxxx> wrote:
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/forums/showthread.php?p=446002#post446002

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



On Mar 10, 12:46 pm, Benny Pedersen <b.peder...@xxxxxxxxxx> wrote:

:) 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 ***
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...@xxxxxxx> wrote:

On Mar 10, 3:21 am, Benny Pedersen <b.peder...@xxxxxxxxxx> wrote:

Hi

This work fine:
  dim i: for i= 1 to 49: oExcel***.cells(2,i)= i: next
  With oExcel***
  .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: oExcel***.cells(2,i)= i: next

I then figured out that the line
  oExcel***.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
  oExcel***.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 ?

Bennywww.fineraw.com

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 oExcel***.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/-Hidequotedtext -

- Show quoted text -

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/-Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

.

Quantcast